Stuffing data in the DB

24 June 2021

The below is a way to get data from a pandas dataframe into a relational DB with minimum effort.

import pandas as pd
import numpy as np
from sqlalchemy import create_engine

# ...

engine = create_engine('postgresql://postgres:@host:5432/db_name') # postgres
# engine = create_engine("mssql+pyodbc://scott:tiger@ms_2008/mydb") # MS-SQL

with engine.connect() as con:
	(
		df
		.where(pd.notnull(df), None) # Fixes up NULLs
		.to_sql(
			'table_name', # Table name
			con, # Connection
			if_exists='append', # What to do with data if table already exists
			index=False # Exclude index
		)
	)
	con.commit()

Note that from v2 onwards sqlalchemy requires a commit afterwards as above. sqlalchemy.__version__ can be used to check which version you are using