How do I connect to SQLite database using Anaconda(Python)?

When I updated the Windows 10 OS to a new Build of OS, the installation spawned some warnings related to Anaconda. After taking care of the problem the installation completed. I believe the Anaconda I had was replaced by a new install.

The following programs appeared in my control panel:

AnacondaPython_2

This added Anaconda to All Programs as shown:

Anaconda3(x64).png

You can launch the Anaconda console and the console gets displayed:

AnacondaPrompt.png

At the prompt you launch Python as shown:

AnacondaPython.png

The version of Python is 3.6.4.

The Python 3.70b2 was removed from the computer.

Accessing SQLite (My persisted file on the computer) is easy using a cursor:

Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn=sqlite3.connect("C:/Users/Owner/Desktop/Blog2017/MSSS2017/SQLite3_DBS/UsersOwnerApr25.sqlite")
>>> c=conn.cursor()
>>> for row in c.execute('Select * from friends'):
...    print(row)
...
('John', 'Chiavetta', 35)
>>>

Are there ODBC drivers for SQLite databases?

Yes. There are ODBC drivers.

Please go to this site  to download your ODBC driver for SQLite.

The current version are as described in my previous post. Download the one appropriate for you use:

LiteODBC_00

I downloaded the one shown here,

LiteODBC_0

You double click the executable show above to begin the installation.

Follow the wizard. Here are some screens shown for your guidance.

LiteODBC_1

LiteODBC_2

I chose the default folder.

LiteODBC_3

LiteODBC_4

As you may know Windows OS has two ODBC Data Source (x32) and (x64). You can verify that you find the driver in the (x64) version as shown here:

LiteODBC_5

Now you start using SQLite.

In the Install directory (in my case: C:\Program Files\SQLite ODBC Driver for Win64), you can find the ODBC Drivers for two versions,

SQLite 2.8.17 

 SQLite 3.22.0 .

How do I know which version of SQLite engine I am using?

I have quite a few SQLite versions on my computer and how do I know which one I am using? Well, here is a list of all SQLite I have on my recent laptop. They are all SQLite3.

When you launch SQLite you get to know the version.

OK, but how do I find it?

Another way is to use the command select sqlite_version();

On the other hand, in SQL Server you would query, Select @@Version

UWP: How do you connect to SQLite from a Universal Windows App?

SQLite is the right relational daatabase with zero configuration for mobile apps. There is a recipe in Microsoft documentation on using a SQLite database in a UWP app. This should be quite straight forward given a recipe but there are problems. Probably because of a plethora of versions of just about everything.

This post is not about a successful follow up of the recipe to its successful end, but steps in the way of achieving, trying to document as much as possible, firstly to find where things did not happen as expected, secondly to make sure it works, at least for me.

The computer is Windows 10 Version 1803, build 17133.73

SQLite for Universal Windows Platform added via Extensions and Update

The Solution for the app, UWPDataTest:

Universal Windows project-UWPDataTest

DataAccessLibrary -DataAccesLibrary

UWPDataTestPost_0

Microsoft.NETCore.UniversalWindowsPlatform used in the DataAccessLibrary(version 6.0.8)

UWPDataTestPost_2

Installed Microsoft.NETCore.UniversalWindowsPlatform

UWPDataTestPost_2a

DataAccessLibrary referenced in UWPDataTest

UWPDataTestPost_3

Using DataAccessLibrary in MainPage.xaml.cs

UWPDataTestPost_4

Using DataAccessLibrary in app.xaml.cs

UWPDataTestPost_5

DataAccess class made static.

UWPDataTestPost_6

Cannot add using Microsoft.Data.SQLite to class library. Data is not an option. If you add it and try to build you get build errors.

UWPDataTestPost_7

Build errors: DataAccessLibrary.dll could not be found. The DataAccessLibrary build has errors.

UWPDataTestPost_8.png

Error persists after upgrading the SQLite version from within VS Studio 2017.

UWPDataTestPost_9.png

Microsoft Visual Studio 2017 Community edition used.

UWPDataTestPost_10.png

I am not sure where the sqliteSample.db is in the computer. It does not seem to be present anywhere, although search is not guaranteed to work always.

UWPDataTestPost_11.png

The problem at present seems to be related to the absence of SQLite on the computer. Perhaps there is an independent source for the database.

Another possible source of error could be the Insiders Preview build may not support the SQLite added.

In VS2015 I did not face any problem, https://hodentekmsss.blogspot.com/2017/03/querying-sqlite-database-using-rsqlite.html

How do you install SQLite ODBC?

Good question.

Probably the SQLite ODBC driver will make way for more extensive use of this zeo-configuration database. Now ODBC drivers are available to developers as posted earlier.

Please go to this site  to download your ODBC driver.

The current version are as described in my previous post. Download the one appropriate for you use:

LiteODBC_00

I downloaded the one shonw here,

LiteODBC_0

You double click the executable show above to begin the installation.

Follow the wizard. Here are some screens shown for your guidance.

LiteODBC_1

LiteODBC_2

I chose the default folder.

LiteODBC_3

LiteODBC_4
As you may know Windows OS has two ODBC Data Source (x32) and (x64). You can verify that you find the driver in the (x64) version as shown here:

LiteODBC_5

Now you start using SQLite.

What is SQLite?

It is a serverless SQL Database that does not require a separate server process. You do not need a configuration (Zero configuration) and it does not need any administration. The whole database is stored in a single cross-platorm disk (or on your mobile device). It is very popular and widely used with mobile applications as the database is stored in the device and can be used without network connection. No external dependencies.

SQLite as opposed to client/server relational database engines is serverless and it's use is indicated where efficency, reliability, independence and simplicity are required and it does not look for a shared repository. SQLite trasactions are fully ACID-compliant. SQLite supports most query languages that are SQL92 compliant and it is written in ANSI-C which provides simple APIs.
SQLite is available on Linux, Mac OS-x, Android, iOS and Windows 32, WinCE and WinRT platforms.

SQLite download from here:
http://sqlite.org/download.html
Read more here:
http://sqlite.org/