How to Connect Python to a SQL Database

1. The three lines

Before the explanation: pandas can already read a CSV. What would connecting straight to a database give you that exporting a CSV first does not?

import sqlite3
import pandas as pd

con = sqlite3.connect('Chinook_Sqlite.sqlite')

df = pd.read_sql("""
    SELECT c.Country,
           COUNT(*)               AS invoices,
           ROUND(SUM(i.Total), 2) AS revenue
    FROM Invoice i
    JOIN Customer c ON c.CustomerId = i.CustomerId
    GROUP BY c.Country
    ORDER BY revenue DESC
    LIMIT 5
""", con)

print(df)

Country  invoices  revenue
    USA        91   523.06
 Canada        56   303.96
 France        35   195.10
 Brazil        35   190.10
Germany        28   156.48

That is the whole mechanism. The answer to the opening question is two things: there is no export step to repeat or forget, and the column types arrive with the data rather than being guessed from text. Nothing here has to be re-run by hand next month.

2. What each line is doing

import sqlite3 costs nothing and installs nothing, because it is part of Python itself. That is worth knowing on a locked-down machine where installing software needs permission.

sqlite3.connect('Chinook_Sqlite.sqlite') opens the file and hands back a connection, which is your open line to the database. One warning: if that filename does not exist, SQLite creates a new empty database rather than complaining. So a typo in the path produces zero tables and no error, which is a confusing five minutes the first time.

pd.read_sql(query, con) sends the SQL, reads the result, and builds a DataFrame with the column names from your query. Notice that the names came from the AS aliases: invoices and revenue are labels you chose, and naming things in SQL means not renaming them in Python later. SQL aliases covers that habit.

The triple-quoted string is doing quiet work too. It lets the SQL sit on several lines, indented and readable, exactly as you would write it in a database tool. A long query squeezed onto one line is the main reason people stop formatting their SQL once it moves into Python.

3. The cursor, and when you actually want it

The other way to get data out looks like this.

cur = con.cursor()
cur.execute("SELECT COUNT(*) FROM Track")
print(cur.fetchone())

(3503,)

A cursor is a pointer into a result set that you step through yourself. fetchone() takes one row, fetchall() takes them all. Notice what came back: a tuple holding a single number, with no column name attached and a trailing comma that confuses everyone once.

Say when that would be preferable to a DataFrame, before reading on.

Two cases, and both are real. When you want one value rather than a table, as above, building a whole DataFrame to hold the number 3503 is silly. And when you are changing data rather than reading it, with INSERT, UPDATE, DELETE or CREATE TABLE, a cursor is the correct tool, and you must call con.commit() afterwards or your change quietly disappears when the program ends.

For everything else, which as an analyst is nearly everything, read into a DataFrame. The labels are not a convenience; they are what stops you from writing row[3] and being wrong about which column that is after somebody adds a field.

4. Passing values safely

Sooner or later the query needs a value from a variable: a date, a customer, a threshold. There is an obvious way to do it and it is the wrong one.

# Do not do this.
threshold = 600000
df = pd.read_sql(f"SELECT Name FROM Track WHERE Milliseconds > {threshold}", con)

Write it with a placeholder instead, and pass the value separately.

df = pd.read_sql(
    "SELECT Name, Milliseconds FROM Track WHERE Milliseconds > ? LIMIT 3",
    con,
    params=(600000,),
)
print(df)

               Name  Milliseconds
   Sleeping Village        644571
    You Shook Me(2)        619467
How Many More Times        711836

The ? is a placeholder, and params supplies what goes there. The database receives the query and the value as two separate things, so the value can never be read as instructions. With an f-string they arrive as one string, and any text inside it that happens to look like SQL is executed as SQL. That is the whole of what people mean by SQL injection, and it is a real hazard the moment a value comes from anywhere you do not control.

Two smaller reasons to prefer placeholders even on data you trust. Quoting and date formats are handled for you, which removes a persistent source of "why does this work in the database tool and not here". And params=(600000,) needs that trailing comma, because without it the brackets are just brackets rather than a tuple of one item.

5. Reaching Postgres, MySQL and the rest

Everything above changes by one line when the database is a server rather than a file. The query, the DataFrame and the placeholders stay the same.

The usual route is SQLAlchemy, which gives every engine one consistent way to describe a connection.

python -m pip install sqlalchemy psycopg2-binary   # Postgres
python -m pip install sqlalchemy pymysql            # MySQL

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine('postgresql+psycopg2://user:password@host:5432/dbname')
df = pd.read_sql("SELECT * FROM sales LIMIT 5", engine)

That string is a connection URL and it reads left to right: which engine, which driver, who you are, which machine, which port, which database. Swap postgresql+psycopg2 for mysql+pymysql and the same line reaches MySQL.

One thing not to do: never type a real password into a file you will commit. Read it from an environment variable with os.environ['DB_PASSWORD'] instead, and keep the value out of your repository. Git for analysts covers what else must never be committed.

6. Where to draw the line between SQL and pandas

Once both tools are in one script, the same job can be done in either, and the choice affects how long you wait.

The rule that holds up: filter and aggregate in SQL, then reshape and chart in pandas. Databases are built to reduce millions of rows to hundreds, and doing that first means only the hundreds travel into memory. Pulling a whole table into pandas and grouping it there works on a small table and stops working on a real one, usually on the day it matters.

So WHERE, JOIN, GROUP BY and HAVING belong in the query. Plotting, reshaping, joining to something that is not in the database, and anything iterative belong in pandas. If the grouping half is unfamiliar, GROUP BY and HAVING and pandas GroupBy are the same idea in both languages.

Now picture your own current script. Which line first turns a big thing into a small thing, and how many rows are sitting in memory before it? If the answer is all of them, that line probably belongs in the query.

What goes wrong, and the fix

Six that come up constantly.

"no such table" on a database you know has tables. The path was wrong, so SQLite created a new empty file. Check the path, and list what is actually there with pd.read_sql("SELECT name FROM sqlite_master WHERE type='table'", con).

Your INSERT or UPDATE did nothing. You did not call con.commit(). Reads need no commit; writes do.

The database is locked. Another program has it open with unsaved changes, very often DB Browser. Press Write Changes there, or close it.

Numbers arrive as text. Rare from a database and common from CSV, which is the point. If it happens, the column is genuinely stored as text in the table, and CAST(col AS REAL) in the query is the honest fix.

The script hangs on a big query. You asked for more rows than you need. Add a LIMIT while developing, then take it off once the query is right.

The connection is never closed. Use with sqlite3.connect(path) as con: or call con.close(). Leaving connections open is what eventually produces the locked-database message above.