3.9. Read SQL
File paths works also with URLs
SQL functions uses SQLAlchemy, which supports many RDBMS
Read SQL query or database table into a DataFrame
3.9.1. SetUp
>>> import pandas as pd
>>>
>>> pd.set_option('display.max_columns', 50)
>>> pd.set_option('display.max_rows', 200)
>>> pd.set_option('display.width', 500)
>>> pd.set_option('display.memory_usage', 'deep')
>>> pd.set_option('display.precision', 4)
3.9.2. Download Database
>>> from urllib.request import urlopen
>>> from pathlib import Path
>>> import sqlite3
>>>
>>>
>>> DATA = 'https://python3.info/_static/example.db'
>>> FILE = '/tmp/myfile.db'
>>>
>>> with urlopen(DATA) as db:
... Path(FILE).write_bytes(db.read())
12288
3.9.3. SQLAlchemy
Create a SQLAlchemy engine to connect to the database
from sqlalchemy import create_engineengine = create_engine('sqlite:////tmp/myfile.db')with engine.connect() as db: ...
>>> from sqlalchemy import create_engine
>>>
>>> engine = create_engine('sqlite:////tmp/myfile.db')
>>>
>>> with engine.connect() as db:
... ...
3.9.4. Read SQL Table
pd.read_sql_table()Read SQL database table into a DataFrame
Works only with SQLAlchemy, not with sqlite3
>>> from sqlalchemy import create_engine
>>>
>>> engine = create_engine('sqlite:////tmp/myfile.db')
>>>
>>> with engine.connect() as db:
... df = pd.read_sql_table('users', db, index_col='id')
>>>
>>> df
firstname lastname age email lastlogin is_active groups
id
1 Alice Apricot 30 alice@example.com 2000-01-01 True users;staff
2 Bob Blackthorn 31 bob@example.com 2000-01-02 True users;staff
3 Carol Corn 32 carol@example.com 2000-01-03 True users
4 Dave Durian 33 dave@example.org 2000-01-04 True users
5 Eve Elderberry 34 eve@example.org 2000-01-05 True users;staff;admins
6 Mallory Melon 15 mallory@example.net NaT False NaN
3.9.5. Read SQL Query
pd.read_sql_query()Read SQL query into a DataFrame
>>> from sqlalchemy import create_engine
>>>
>>> engine = create_engine('sqlite:////tmp/myfile.db')
>>>
>>> SQL = 'SELECT * FROM users'
>>> with engine.connect() as db:
... df = pd.read_sql_query(SQL, db, index_col='id')
>>>
>>> df
firstname lastname age email lastlogin is_active groups
id
1 Alice Apricot 30 alice@example.com 2000-01-01 1 users;staff
2 Bob Blackthorn 31 bob@example.com 2000-01-02 1 users;staff
3 Carol Corn 32 carol@example.com 2000-01-03 1 users
4 Dave Durian 33 dave@example.org 2000-01-04 1 users
5 Eve Elderberry 34 eve@example.org 2000-01-05 1 users;staff;admins
6 Mallory Melon 15 mallory@example.net NaN 0 NaN
>>> SQL = 'SELECT * FROM users'
>>> with sqlite3.connect(FILE) as db:
... df = pd.read_sql_query(SQL, db, index_col='id')
>>>
>>> df
firstname lastname age email lastlogin is_active groups
id
1 Alice Apricot 30 alice@example.com 2000-01-01 1 users;staff
2 Bob Blackthorn 31 bob@example.com 2000-01-02 1 users;staff
3 Carol Corn 32 carol@example.com 2000-01-03 1 users
4 Dave Durian 33 dave@example.org 2000-01-04 1 users
5 Eve Elderberry 34 eve@example.org 2000-01-05 1 users;staff;admins
6 Mallory Melon 15 mallory@example.net NaN 0 NaN
3.9.6. Read SQL
pd.read_sql()Read SQL query or database table into a DataFrame.
This function is a convenience wrapper around
read_sql_tableandread_sql_query(for backward compatibility)A SQL query will be routed to
read_sql_queryWhile a database table name will be routed to
read_sql_table.
3.9.7. Use Case - 1
SetUp:
>>> import sqlite3
>>> import pandas as pd
>>>
>>> DATABASE = '/tmp/myfile.db'
Prepare data:
>>> DATA = [
... {'firstname': 'Alice', 'lastname': 'Apricot', 'age': 30},
... {'firstname': 'Bob', 'lastname': 'Blackthorn', 'age': 31},
... {'firstname': 'Carol', 'lastname': 'Corn', 'age': 32},
... {'firstname': 'Dave', 'lastname': 'Durian', 'age': 33},
... {'firstname': 'Eve', 'lastname': 'Elderberry', 'age': 34},
... {'firstname': 'Mallory', 'lastname': 'Melon', 'age': 15},
... ]
Prepare SQL statements:
>>> SQL_CREATE = """
... CREATE TABLE users (
... id INTEGER PRIMARY KEY AUTOINCREMENT,
... firstname TEXT NOT NULL,
... lastname TEXT NOT NULL,
... age INTEGER NOT NULL
... );
... """
>>> SQL_INSERT = """
... INSERT INTO users (firstname, lastname, age)
... VALUES (:firstname, :lastname, :age);
... """
>>> SQL_SELECT = """
... SELECT *
... FROM users;
... """
Write data to database:
>>> with sqlite3.connect(DATABASE) as db:
... _ = db.execute(SQL_CREATE)
... _ = db.executemany(SQL_INSERT, DATA)
Read data from database to Pandas DataFrame:
>>> with sqlite3.connect(DATABASE) as db:
... df = pd.read_sql(SQL_SELECT, db, index_col='id')
Result:
>>> df
firstname lastname age
id
1 Alice Apricot 30
2 Bob Blackthorn 31
3 Carol Corn 32
4 Dave Durian 33
5 Eve Elderberry 34
6 Mallory Melon 15