Joining and creating dataframe

The database used here is SQLite. The main data science tricks will not change much depending on the database in use because they all adhere to PEP249 (commonly known as Python DB API 2). For different databases, different third-party Python libraries (such as Psycopg2 for PostgreSQL) must be installed.

Importing libraries and packages

# Mathematical operations and data manipulation
import pandas as pd

# Database
import sqlite3

Set paths

# Path to datasets directory
data_path = "./datasets"
# Path to assets directory (for saving results to)
assets_path = "./assets"

Connect

with sqlite3.connect(f"{assets_path}/discworld.db") as conn:
    cursor = conn.cursor()

Create dataframe

cursor.execute("PRAGMA foreign_keys = 1")
sql = """SELECT
            user.email,
            user.first_name,
            user.last_name,
            user.age,
            user.gender,
            quotes.quotes FROM quotes
    JOIN user ON quotes.user_id = user.email
"""
rows = cursor.execute(sql)

columns = ["Email", "First Name", "Last Name", "Age", "Gender", "Quotes"]
data = []

for row in rows:
    data.append(row)

df = pd.DataFrame(data, columns=columns)
df.style.set_properties(column=["Quotes"], **{"text-align": "left"})
df

	Email	First Name	Last Name	Age	Gender	Quotes
0	swivel@lspace.org	Geoffrey	Swivel	20	M	There's a billion places like home. But only o...
1	swivel@lspace.org	Geoffrey	Swivel	20	M	That's quite against the spirit of witchcraft.
2	swivel@lspace.org	Geoffrey	Swivel	20	M	You bloated lying blutocat!
3	swivel@lspace.org	Geoffrey	Swivel	20	M	Wisdom is one of the few things that looks big...
4	swivel@lspace.org	Geoffrey	Swivel	20	M	Cats gravitate to kitchens like rocks gravitat...
...	...	...	...	...	...	...
995	grodley@lspace.com	Sister	Grodley	39	F	Sometimes you have to trust to experience and ...
996	grodley@lspace.com	Sister	Grodley	39	F	Humanity's a nice place to visit, but you woul...
997	grodley@lspace.com	Sister	Grodley	39	F	To tell the truth, I don't know either, but th...
998	grodley@lspace.com	Sister	Grodley	39	F	But I can't do none of that stuff. That wouldn...
999	grodley@lspace.com	Sister	Grodley	39	F	Not that I don't like him, stinky as he is. I'...

1000 rows × 6 columns

conn.close()