[Python] when creating a table using sql alchemy in python, why do string columns become...

I have a dataframe that I want to write to a table in SQL Server. I use SQL alchemy:

import pandas as pd
import sqlalchemy
from sqlalchemy import create_engine, text

server =
database =
driver =
database_con = f'mssql://@{server}/{database}?driver={driver}'

engine = create_engine(database_con, fast_executemany=True)
con = engine.connect()

pd.DataFrame({'col1':['a','b','c','dd'], 'col2':[11,22,33,44]}).to_sql(
name='test',
con=con,
if_exists='append',
index=None)
con.commit()


This writes and creates the table successfully in SSMS. But when I examine the column definitions, I see that the string column is a data type varchar(max)



This causes issues later on if we want to create indexes using one of these varchar columns (cannot create index on varchar max column).

Is there a way to specify when creating the table that I want a varchar(10) for col1, and not default to varchar(max)?

Continue reading...