执行在sql上失败- Error - Pandas.to.sql()
执行在sql上失败- Error - Pandas.to.sql()
提问于 2021-05-25 09:52:09
我想在数据库表中保存一个数据帧。我所做的：
连接到azure Sql server DB
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()- 创建表
#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()- Use 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)")有人知道怎么回事吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-05-25 16:25:12
当import sqlalchemy__时，您可以使用to_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__。
您的代码应该如下所示。你也可以在下面的帖子中读到我的答案。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67685624
复制相关文章
相似问题
腾讯云开发者
