pandas - sql 执行失败 - 错误 - Pandas.to.sql()

Question

我想在数据库表中保存一个数据框。我做了什么 ：

连接到 azure Sql server 数据库

import pyodbc

# Create
server = 'XXXXXXXXXXXXXXXXXXXX'
database = 'XXXXXXXXXXXXXXXXXXX' 
username = 'XXXXXXXXXXXXXXXX'
password = 'XXXXXXXXXXXX'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

2. 创建表

#create_table = """
CREATE TABLE forecast_data (
    CompanyEndDate text,
    Retailer text,
    Store_Name text,
    Category text,
    Description text,
    QtySold int);

cursor.execute(create_table)
cnxn.commit()

3. 使用熊猫 to_sql

data.to_sql('forecast_data', con=cnxn)

我收到此错误：

ProgrammingError                          Traceback (most recent call last)
/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1680         try:
-> 1681             cur.execute(*args, **kwargs)
   1682             return cur

ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW)")

The above exception was the direct cause of the following exception:

DatabaseError                             Traceback (most recent call last)
7 frames
/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1691 
   1692             ex = DatabaseError(f"Execution failed on sql '{args[0]}': {exc}")
-> 1693             raise ex from exc
   1694 
   1695     @staticmethod

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW)")

有人知道发生了什么吗？

标签： pandasazure

Answer 1

时import sqlalchemy，可以使用to_sql。

相关帖子：

熊猫到 sql 服务器

import sqlalchemy
...  
engine = sqlalchemy.create_engine(
           "mssql+pyodbc://user:pwd@server/database",
           echo=False)
data.to_sql('forecast_data', con=cnxn, if_exists='replace')

时import pyodbc，可以使用to_sql。

您的代码应如下所示。您也可以在下面的帖子中阅读我的答案。

登录失败 (pyodbc.InterfaceError) ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]用户 'xxxx' 登录失败