In this tutorial, you will learn how to connect Python code to various SQL database servers including: SQLite, PostgreSQL, MS SQL, MySQL.
- Connect Python to SQLite Database
- Connect Python to MySQL Database
- 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
Please watch the video for a detailed procedure.
2. Connect Python to My SQL Database
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:[email protected]: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:[email protected]:5432/postgres")
For MS SQL, you’ll use the code:
# Connect using pyodbc engine = create_engine('mssql+pyodbc://scott:[email protected]') # Connect using pymssql engine = create_engine('mssql+pymssql://scott:[email protected]:port/dbname')