move-data-to-a-db-python/pd.py at master · andrewvillazon/move-data-to-a-db-python · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
"""Move data to a database with pandas

Script demonstrating how to move data from a CSV file to a database
using pandas.

"""

import pandas as pd
import sqlalchemy


df = pd.read_csv(
    filepath_or_buffer="AB_NYC_2019.csv",
    header=0,
    index_col="id",
    quotechar='"',
    parse_dates=["last_review"],
)

# Optionally summarise df
# print(df.shape)
# print(df.info())
# print(df.isnull().sum())

engine = sqlalchemy.create_engine("sqlite:///ab_nyc.sqlite3")

with engine.connect() as connection:
    df.to_sql(
        name="listings_pandas",
        con=connection,
        if_exists="replace",
        index=True,
        index_label="id",
        dtype={"last_review": sqlalchemy.Date},
    )