[Python] Use Sqlalchemy to Connect SQL Server - Pandas

Try to write the dataframe into DB, but getting the error below.

Create Dataframe

import pandas as pd
d = {'col1': [1, 2], 'col2': ['VIP', 'Eco']}
df = pd.DataFrame(data=d)


Connect to SQL Server at Azure

from sqlalchemy import create_engine
engine = create_engine
('mssql+pyodbc://userass@dns:1433/database')


Try to Insert the data into DB

from sqlalchemy.sql import table, column, select, update, insert
from sqlalchemy.types import Integer, Text, String, DateTime

df.to_sql(
'Table_Test',
engine,
if_exists='append',
index=False,
chunksize=100,
dtype = {
"ID" : Integer,
"Type" : String(10)}
)


Error

ValueError: ID (<class 'sqlalchemy.sql.sqltypes.Integer'>) not a string
​

Pandas Version: '1.1.5'

full error

try:...
Traceback (most recent call last):
File "<ipython-input-19-993c20acc8ac>", line 2, in <module>
df.to_sql(
File "C:\anaconda3\lib\site-packages\pandas\core\generic.py", line 2605, in to_sql
sql.to_sql(
File "C:\anaconda3\lib\site-packages\pandas\io\sql.py", line 589, in to_sql
pandas_sql.to_sql(
File "C:\anaconda3\lib\site-packages\pandas\io\sql.py", line 1816, in to_sql
raise ValueError(f"{col} ({my_type}) not a string")
ValueError: ID (<class 'sqlalchemy.sql.sqltypes.Integer'>) not a string

Continue reading...