python - 使用 Sqlalchemy 连接 SQL Server - Pandas

Question

尝试将数据帧写入数据库，但出现以下错误。

创建数据框

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

连接到 Azure 上的 SQL Server

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

尝试将数据插入数据库

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)}
    )

错误

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

熊猫版本：'1.1.5'

完全错误

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

谢谢

标签： pythonsqlsql-serverpandassqlalchemy