SQLAlchemy Tutorial 1 – Connect Python to MySQL, PostgreSQL, SQLite and MS SQL

In this tutorial, you will learn how to connect Python code to various SQL database servers including: SQLite, PostgreSQL, MS SQL, MySQL.

1. Connect Python to SQLite Database
2. Connect Python to MySQL Database
3. Connect Python to PostgreSQL Database

1. Connect Python to SQLite Database

Let’s connect Python to a database in memory. Actually, we’ll create an in-memory database. Then we load this database using data imported from a CSV file.

# Import the neccessary modules
from sqlalchemy import create_engine as ce
import pandas as pd

# Create the db engine for file database
file_db = ce('sqlite:///datasets/tutorial.db')

# Create the db engine for in-memory database
memory_db = ce('sqlite:///:memory:')

# Import some data from a CSV file
data = pd.read_excel("datasets/Telescope_data.xlsx")

# Load the imported CSV into your database
data.to_sql('telescope_table', file_db)

I recommend you execute each part of the code separately.

Once this code executes, the database would be available in the datasets directory with the name tutorial.db. To view this SQLite database you will need a tool like DB Browser for SQL Lite  or DBeaver. You can download them from the links below

• Download DB Browser
• Download DBeaver

Please watch the video for  a detailed procedure.

2. Connect Python to My SQL Database

You need to have MySQL Installed. You may also need MySQL Workbench.

Let’s now connect to Python to MySQL Database. We would also use the same imports as with SQLite. Follow the steps below:

Step 1 – Create an empty database in MySQL. You can either use the command line or the Workbench GUI (See the video for details). The command-line syntax is given below:

create database datasciencedb;

Step 2 – Use the code below to connect to MySQL and load the empty database with data.

Note: You’ll need to have mysqlclient installed using pip or conda.

# Connect to MySQL
mysql_engine = ce("mysql://root:rootuser@localhost:3306/datasciencedb")

# Import some data from a CSV file
data = pd.read_excel("datasets/Telescope_data.xlsx")

# Load the imported CSV into your database
data.to_sql('telescope_table', mysql_engine)

You can not check the database to see that the table is created

3. Connect Python to PostgreSQL and MS SQL

For this, you need to have PostgreSQL installed in your system.

Note: if you encounter missing modules error while running these commands, then install them! Also note that the database should exist. So you need to create empty database in PostgreSQL in advance.

In case of PostgreSQL, you need to use the code below:

# Connect to PosgreSQL
pg_engine = ce("postgresql://postgres:password@localhost:5432/postgres")

For MS SQL, you’ll use the code:

# Connect using pyodbc
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')

# Connect using pymssql
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')