What is SQLAlchemy?

 SQLAlchemy a toolkit for Python programming language. You can build full blown database applications with it and use the full range of the SQL Language. It is also a Object Relational Mapper(ORM).

SQLAlchemy is what you need (libraries and other tools) for building data-centric apps using Python and using other libraries the possibilities are limitless including AI and Machine Learning.

SQLAlchemy and connecting to data:

The connectors are elements that connect your application to data but in the case of SQLAlchemy they are called 'database dialects'. There are database dialects for each kind of database. These are the most common kinds:

SQLite: A lightweight, disk-based database that doesn’t require a separate server process.

https://hodentekmsss.blogspot.com/2016/09/sqlite-using-visual-studio-community.html

PostgreSQL: A powerful, open-source object-relational database system.

https://hodentekmsss.blogspot.com/search?q=postgresql

MySQL: A widely used open-source relational database management system.

https://hodentekmsss.blogspot.com/2024/11/install-latest-mysql-and-improve.html

Oracle: A proprietary database management system by Oracle Corporation.

https://hodentekmsss.blogspot.com/2013/11/a-collection-of-sql-server-related.html

Microsoft SQL Server: A relational database management system by Microsoft.

https://hodentekmsss.blogspot.com/2024/11/sql-server-2025-ready-to-go.html

MariaDB: A community-developed fork of MySQL.

https://hodentekhelp.blogspot.com/2015/11/how-do-you-access-mariadb-in-xampp.html

These connectors make it easy to connect SQLAlchemy to various database systems, ensuring compatibility and flexibility in developing data-centric applications.

If you are looking for working with databases, stay in and learn from over 15 years of blogging on databases here.

You may also have a look at my database related books:

You may

Connecting to SQLite using Power BI

Power BI is the Microsoft product for business intelligence. SQLite is well known for its use in mobile platforms and embedded apps.

There is no native way of connecting to SQLite using Power BI, but you could use the ODBC driver which was described in an earlier post.

Read here:

ODBC Drivers for SQLite:

Read here:

Creating ODBC DSN for SQLite3:

https://hodentekmsss.blogspot.com/2018/04/creating-odbc-dsn-for-connecting-to.html

Connecting to SQLite3 using Power BI is easy. Here are the steps.

Launch Power BI and open the Get Data menu as shown.

In the list in the Other clic on ODBC.

Highlight the DSN, ODBCSQLite and Click OK. 

Change it to Windows (this may not be necessary) and click Connect.

The From ODBC window is displayed. Click on Advanced Options to display the pane where you can write a SQL Statement.

It goes through from processing and since there is no table by that name, it returns an error.

Now change the SQL Statement to the following:

Select * from Users

After some processing it get connected.

Now you can find the table details in Navigator as shown.

Power BI does not show the tables and write a query you need to know the objects you created in SQLite database.

Querying a persisted SQLite using Python 3.7

Python uses the sqlite3 module written by Gerhard Häring. It provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249.

I launch the Python Shell in my All Programs.

PythonSQLite_00

Python 3.7.0b2 (v3.7.0b2:b0ef5c979b, Feb 28 2018, 01:32:48) [MSC v.1912 32 bit (Intel)] on win32

Type "copyright", "credits" or "license()" for more information.

Connecting to an existing SQLite database. UsersOwnerApr25.sqlite has a table called 'friends' with just one row of data.

 First you need to import sqlite3 and then establish a connection by providing the path to the database (note the direction of slashes).

---------------------

>>> import sqlite3

>>> conn=sqlite3.connect("C:/Users/Owner/Desktop/Blog2017/MSSS2017/SQLite3_DBS/UsersOwnerApr25.sqlite")

--------------------

After connecting create a cursor and the subsequently use the Execute() method of the cursor.

>>> c=conn.cursor()

>>> conn.commit()

-----------------------------------------------

For example, for inserting data into 'friends' table use the following statement. I already had a table with the name 'friends'

----------------------------------

>>> c.execute("Insert Into friends values('Jay', 'Kris',45)")

--This is something I need to figure out---

I try to print the results but I get the same response after the print() statement.

--------------------------------------

>>> y= c.execute('Select * from friends')

>>> print (y)

-----------------------------------------

Now I modify the print() statement and I succeed in printing the first row using the fetchone()method.

---------------------------------------

>>> print(c.fetchone())

('John', 'Chiavetta', 35)

-----------------------------

I print all the rows using the fetchall().

-----------------------------

>>> for row in c.execute('Select * from friends'):

print(row)

('John', 'Chiavetta', 35)

('Jay', 'Kris', 45)

>>>

-------

I have not figured out why I get this:

The IDLE interface is very useful as mentioned here. You get a lot of help.

Problem connecting to SQLite from a UWP app is still not solved

SQLite is the right relational database 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