数据帧.to_sql()插入Server数据库时会导致错误

Question

我试图从数据框架向Server数据库生成一个INSERT语句：

data_frame.to_sql(table, engine, if_exists='append', index=False)

数据框架是根据JSON响应创建的。

我在向数据库中插入带有单引号的字符串值时遇到了问题。原因似乎是，如果字段中任何地方都有单引号，则数据帧字段将被双引用。该行为的示例：

INSERT INTO my_table(column1, column2, column3) VALUES ('value1', "value2's", 'value3');

因为value2 2中有一个单引号，所以.to_sql()用双引号代替了字段。但是，Server认为这个值是一个列，因为它是双引号，插入失败。

如何让.to_sql()以如下方式格式化INSERT语句：

INSERT INTO my_table(column1, column2, column3) VALUES ('value1', 'value2''s', 'value3');

错误消息.to_sql()生成：

The identifier that starts with 'value2's' is too long. Maximum length is 128.

上面的错误消息表明数据库将数据帧值视为列名。如果值是双引号而不是单引号，则Server通常会出现此错误。

图书馆(有些可能与此问题无关)：

import pyodbc
import sqlalchemy
from sqlalchemy import MetaData
import pandas as pd
from sqlalchemy.engine import URL

任何帮助都是非常感谢的！

Answer 1

如何确定错误消息应用于df.to_sql()语句？

请考虑以下示例：

import os
import pandas as pd
from sqlalchemy import Column, MetaData, Table, VARCHAR, create_engine
import urllib

# Connection setup...
conn_params = urllib.parse.quote_plus(
    "Driver=ODBC Driver 18 for SQL Server;" +
    "Server=tcp:%s,%s;" % (os.getenv("DB_SERVER"), os.getenv("DB_PORT")) +
    "Database=%s;" % os.getenv("DB_NAME") +
    "Uid=%s;" % os.getenv("DB_USER") +
    "Pwd={%s};" % os.getenv("DB_PASSWORD") +
    "Encrypt=yes;" +
    "TrustServerCertificate=yes;"
)
url = f"mssql+pyodbc:///?odbc_connect={conn_params}"
engine = create_engine(
    url,
    isolation_level="REPEATABLE READ"
)

# Database setup...
m = MetaData()
t = Table(
    "my_table",
    m,
    Column("column1", VARCHAR(20)),
    Column("column2", VARCHAR(20)),
    Column("column3", VARCHAR(20)),
    )
m.create_all(engine)

# Dataframe setup...
df = pd.DataFrame(data={
    "column1": [1, 2, 3],
    "column2": ["Sam", "Daniel", "Jack"],
    "column3": ["Carter", "Jackson", "O'Neill"]
})

# Data insertion...
with engine.begin() as conn:
    conn.execute(t.delete())
    engine.echo = True
    df.to_sql(t.name, engine, if_exists="append", index=False)
    for row in conn.execute(t.select()).fetchall():
        print(row)

在控制台上执行此操作将提供以下输出：

# python3 demo.py
2022-08-27 05:43:11,069 INFO sqlalchemy.engine.Engine SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] 
FROM [INFORMATION_SCHEMA].[TABLES] 
WHERE [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max))
2022-08-27 05:43:11,069 INFO sqlalchemy.engine.Engine [cached since 0.01563s ago] ('BASE TABLE', 'my_table', 'dbo')
2022-08-27 05:43:11,074 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-27 05:43:11,076 INFO sqlalchemy.engine.Engine INSERT INTO my_table (column1, column2, column3) VALUES (?, ?, ?)
2022-08-27 05:43:11,076 INFO sqlalchemy.engine.Engine [generated in 0.00060s] ((1, 'Sam', 'Carter'), (2, 'Daniel', 'Jackson'), (3, 'Jack', "O'Neill"))
2022-08-27 05:43:11,079 INFO sqlalchemy.engine.Engine COMMIT
('1', 'Sam', 'Carter')
('2', 'Daniel', 'Jackson')
('3', 'Jack', "O'Neill")

注意，O'Neill字符串已用双引号(即"O'Neill" )包装，但这纯粹是SQLAlchemy的回显形式。实际数据已正确插入表中，如sqlcmd所示

# /opt/mssql-tools18/bin/sqlcmd \
        -S $DB_SERVER \
        -U $DB_USER \
        -P $DB_PASSWORD \
        -C \
        -d $DB_NAME \
        -Q "select * from dbo.my_table"
column1              column2              column3             
-------------------- -------------------- --------------------
1                    Sam                  Carter              
2                    Daniel               Jackson             
3                    Jack                 O'Neill             

(3 rows affected)

Answer 2

我认为"AlwaysLearning“提供的答案是正确的。这不是严格意义上的答案，而是解决相同问题的另一种方法，即从DataFrame到ODBC数据库。

from arrow_odbc import insert_into_table
import pyarrow as pa
import pandas


def dataframe_to_table(df):
    table = pa.Table.from_pandas(df)
    reader = pa.RecordBatchReader.from_batches(table.schema, table.to_batches())
    insert_into_table(
        connection_string=connection_string,
        user="user",
        password="password",
        chunk_size=100,
        table="my_table",
        reader=reader,
    )

完全披露:我是arrow-odbc的作者，所以我的观点可能有偏见。