Connecting Python to Databases

Organizations often store their data in relational databases such as PostgreSQL, MySQL, and SQLite. Python can retrieve this information directly, allowing analysts to work with current database records instead of relying on manually exported spreadsheets.

Connecting to SQLite

SQLite is a lightweight relational database that is included with Python's standard library. It is convenient for learning database integration without setting up a separate database server.

Consider a database containing a table named sales.

import sqlite3

connection = sqlite3.connect("business.db")

query = """

SELECT *

FROM sales;

"""

cursor = connection.cursor()

cursor.execute(query)

rows = cursor.fetchall()

for row in rows:

print(row)

connection.close()

This example connects to business.db, executes a SQL query, retrieves the records, and closes the connection.

If the database file does not exist, SQLite can create it. However, the sales table must already exist for this query to work.

Loading SQL Results into Pandas

Pandas provides read_sql_query() to retrieve query results directly into a DataFrame.

import sqlite3

import pandas as pd

connection = sqlite3.connect("business.db")

query = """

SELECT Product, Region, Sales

FROM sales

WHERE Sales > 20000;

"""

df = pd.read_sql_query(query, connection)

print(df)

connection.close()

The SQL statement filters the records before they are transferred to Pandas. This can be more efficient than loading an entire table when only a subset of records is required.

For larger applications, database connections can also be managed through SQLAlchemy, which supports multiple relational database systems. <Cite refs={["turn954920search1","turn954920search0"]}/>