[pandas] to_sql() creates Long Text (a.k.a. Memo) fields for string columns - gordthompson/sqlalchemy-access GitHub Wiki

String values in a pandas DataFrame column can be larger than the maximum length of an Access "Short Text" column (255 characters), so when using to_sql() to create an Access table the columns are created as "Long Text" (a.k.a. "Memo") by default. This will be fine in many cases, but Long Text columns cannot be used in JOINs so you may want to coerce some columns to Short Text by using the dtype argument to to_sql().

For example:

import pandas as pd
import sqlalchemy_access as sa_a

# test data
df = pd.DataFrame(
    [(1, "x" * 50), (2, "x" * 250),], columns=["id", "text_column"],
)

# determine the length of the longest string in the column
max_text_column_len = df.text_column.map(lambda x: len(x) if x else 0).max()

dtype_dict = (
    {"text_column": sa_a.ShortText(255)}
    if max_text_column_len <= 255
    else None
)

df.to_sql(
    "my_table", engine, if_exists="replace", index=False, dtype=dtype_dict
)