Python SQL Alchemy and Working with existing database
Python SQL Alchemy and Working with existing database
Work with an existing database
Suppose we have built the following database and table,
from sqlalchemy import create_engine, MetaData, Table, Integer
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
engine = create_engine("sqlite:///mydatabase.db")
metadata_obj = MetaData()
Table('user_order',
metadata_obj,
Column('id', Integer, primary_key=True)
)
metadata_obj.create_all(engine)
engine.dispose()
Basic Use
The simplest usage is to autoload_with and engine.
engine = create_engine("sqlite:///mydatabase.db")
session = Session(engine)
Base = automap_base()
Base.prepare(autoload_with=engine)
Order = Base.classes.user_order
session.query(Order).all()
Through MetaData
Pass a pre-declared MetaData object to automap_base(), the metadata itself can be reflected from an engine, and/or defined by Table Objects Ourselves.
from sqlalchemy import create_engine, MetaData, Table, Integer, Column
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
# this is an example of reflecting metadata from engine
engine = create_engine("sqlite:///mydatabase.db")
metadata_obj = MetaData()
metadata_obj.reflect(engine, only=['user_order'])
Base = automap_base(metadata=metadata_obj)
Base.prepare()
Order = Base.classes.user_order
session = Session(engine)
session.query(Order).all()
from sqlalchemy import create_engine, MetaData, Table, Integer, Column
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
# this is an example of explicitly defining the table to metadata
engine = create_engine("sqlite:///mydatabase.db")
metadata_obj = MetaData()
Table('user_order',
metadata_obj,
Column('id', Integer, primary_key=True)
)
Base = automap_base(metadata=metadata_obj)
Base.prepare()
Order = Base.classes.user_order
session = Session(engine)
session.query(Order).all()
Explicitly Specify the Classes
The automap_base() is not necessary anymore in this use case, even declarative_base should work as well.
from sqlalchemy import create_engine, Integer, Column
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
engine = create_engine("sqlite:///mydatabase.db")
session = Session(engine)
Base = automap_base()
class Order(Base):
__tablename__ = 'user_order'
id = Column(Integer, primary_key=True)
Base.prepare()
Common Failures
One failure happens when we try to build mapped classes from existing tables that DO NOT have PRIMARY KEY (or simply cannot be auto mapped). In this case, even though MetaData is able to reflect the table schema, Base cannot be prepared to build corresponding mapped classes because primary key is required.
This failure is very hard to be identified, because It seems there is even no error message.
To deal with this, we have to explicitly define the mapped class ourself (which is documented above), and make a column the primary key. While, this is too cumbersome if we are working with a large table.
Therefore, here we show how to leverage the auto reflected columns as much as possible, make copies and overwrite a column to be the primary key.
A table written by dataframe does not have a primary key, let’s use this as an example.
import pandas as pd
from sqlalchemy import create_engine, MetaData
from copy import deepcopy
engine = create_engine("sqlite:///mydatabase.db")
df = pd.DataFrame(
{
'id': [1, 2, 3, 4, 5],
'name': ['alice', 'bob', 'carol', 'david', 'emily']
}
)
df.to_sql('users', con=engine, if_exists='replace', index=False)
The reflection still works, but the mapped class does not show in Base.classes.
metadata_obj = MetaData()
metadata_obj.reflect(engine, only=['users'])
# the users table is already in the metadata
metadata_obj.tables
# build mapped classes from the metadata that are reflected from the engine
Base = automap_base(metadata=metadata_obj)
Base.prepare()
# Base.classes.users ! this will fail... users class is not yet ready
# Of course you can declare everthing from scratch and forget about the automap
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
session = Session(engine)
session.query(User).all()
The approach we use is to take out the columns from the table, make id a primary key, then write the new table to the metadata_obj.
new_columns = []
reflected_table = metadata_obj.tables['users']
for old_column in reflected_table.columns.keys():
# Column.copy() method is deprecated
copy = deepcopy(reflected_table.columns[old_column])
# a drawback of deepcopy is that the table information is also copied
# we need a clean column with no table built
copy.table = None
if old_column == 'id':
copy.primary_key = True
new_columns.append(copy)
# recreate the table with new columns and bind with metadata
metadata_obj.clear()
Table("users", metadata_obj, *new_columns)
# automap with new metadata
Base = automap_base(metadata=metadata_obj)
Base.prepare()
# now the mapped classes are constructed correctly.
User = Base.classes.users
session.query(User).all()
