The to_sql() method for Pandas Dataframe is extremely useful, as shown by the example from the documentation
import pandas as pd
from sqlalchemy import create_engine
# Create sqlite engine
engine = create_engine('sqlite:///test.db')
# Create 'users' table using Dataframe method to_sql()
df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})
df.to_sql('users', con=engine, if_exists='replace')
# Check to see if it worked
with engine.connect() as conn:
print(conn.execute(text("Select * from users")).fetchall())
I would like to set the name column to be a primary key.
The simplest recommendations I could find were variants on inject SQL text.
There should be a simple object-oriented method for this (very natural) problem. After all, SqlAlchemy has a very nice interface for accessing properties of tables and columns.
1
With SQLAlchemy, you can define the table structure more explicitly. Try using the code below. You will need to import Table, MetaData, and Column from sqlalchemy.
Another approach is to set up the table structure first, and then import the data using Python. I find this method easier for my work.
from sqlalchemy import Table, Column, String, MetaData
# Create a metadata object
metadata = MetaData()
# Define a table schema with a primary key
users_table = Table(
'users', metadata,
Column('name', String, primary_key=True)
)
After much experimentation, I found two methods that work. In the first method, one first loads the dataframe using the to_sql() method. The challenge is then how to use the ORM capabilities to set the primary key. This snippet shows the basic steps:
# load dataframe into the database
with Session(bind=engine) as session:
df.to_sql('users', con=session.connection(), index=True, if_exists='replace')
session.commit()
# Use reflection to load metadata from the database
metadata = MetaData().reflect(bind=engine)
# Get the handle to the table
users = Table('users', metadata, autoload_with=engine, must_exist=True)
# Get the handle to the name column and add to primary keys
name = users.columns['name']
users.constraints.add(PrimaryKeyConstraint(name))
The second method declares the table first, then ‘appends’ the dataframe to the table.
from sqlalchemy.orm import declarative_base
Base = declarative_base()
# User is a class that inherits from the Declarative base
# The third argument is called the 'attributes' dictionary
User = type('User', (Base,),
{
'__tablename__': 'users',
'index': Column(Integer, autoincrement=True),
'name': Column(String, nullable=False, primary_key=True),
}
)
# Create the tables in the database
Base.metadata.create_all(engine)
# load dataframe into the database table with to_sql()
with Session(bind=engine) as session:
df.to_sql('users', con=session.connection(), if_exists='append')
session.commit()
# Get the handle to the table
users = Table('users', Base.metadata, autoload_with=engine)
The basic pattern here is that the ORM declares the class User for the rows. Each row is an instance of the class User. The Declarative pattern associates a table with a row class via the __tablename__ key. The tables are generated by the MetaData() create_all method.
The database doesn’t know anything about this User class or about Declarative patterns. One needs to get that information into the ORM using the reflect method of a MetaData() object before one can use ORM methods to work with the table metadata.