[tip] create indexes on an existing table - gordthompson/sqlalchemy-access GitHub Wiki

When we create a table using pandas' to_sql() method the resulting table has no primary key or indexes. Fortunately it is easy to add them afterwards by executing DDL statements. For example

import pandas as pd
import sqlalchemy as sa
import sqlalchemy_access as sa_a

# create tables via to_sql()
df = pd.DataFrame([(1, "Homer"),], columns=["ID", "ParentName"],)
dtype_dict = {"ID": sa_a.AutoNumber, "ParentName": sa_a.ShortText(20)}
df.to_sql("parent", engine, if_exists="replace", index=False, dtype=dtype_dict)
#
df = pd.DataFrame([(1, 1, "Lisa"),], columns=["ID", "ParentID", "ChildName"],)
dtype_dict = {
    "ID": sa_a.AutoNumber,
    "ParentID": sa_a.LongInteger,
    "ChildName": sa_a.ShortText(20),
}
df.to_sql("child", engine, if_exists="replace", index=False, dtype=dtype_dict)

# now add primary keys and foreign key index
sql1 = f"""\
ALTER TABLE parent 
ALTER COLUMN ID {sa_a.AutoNumber.__visit_name__} PRIMARY KEY"""

sql2 = f"""\
ALTER TABLE child 
ALTER COLUMN ID {sa_a.AutoNumber.__visit_name__} PRIMARY KEY"""

sql3 = f"CREATE INDEX IX_child_ParentID ON child (ParentID)"

with engine.begin() as conn:
    for statement in [sql1, sql2, sql3]:
        conn.exec_driver_sql(statement)
print("Indexes created.")

The __visit_name__ variable inserts the correct DDL column type into the statement (COUNTER in this case).

We can also add a foreign key constraint like this:

statement = """\
ALTER TABLE child
   ADD CONSTRAINT FK_child_ParentID
   FOREIGN KEY (ParentID) REFERENCES parent (ID)
   ON UPDATE CASCADE
   ON DELETE CASCADE
"""
with engine.begin() as conn:
    conn.exec_driver_sql(statement)
print("FK constraint created.")