我正在尝试在SQL数据库中创建一个临时表,并从pandas数据框中填充该表。当使用df.to_sql来填充临时表时,我遇到了错误。谢谢您的帮助。
import pandas as pd
from sqlalchemy import create_engine
import pandas.io.sql as psql
import urllib
params = urllib.quote_plus("DRIVER={SQL Server};SERVER=ServerAddressHere;DATABASE=DatabaseNameHere;Trusted_Connection=yes")
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
connection = engine.connect()
resoverall = connection.execute('''SELECT DISTINCT
a.CountryRegionID AS ISO_Short,
b.Name
FROM
CustTable AS a
LEFT JOIN AddressCountryRegion AS b
ON b.CountryRegionID = a.CountryRegionID''')
Countries= pd.DataFrame(resoverall.fetchall())
Countries.columns = resoverall.keys()
Countries= pd.Countries['ISO_Short'].str.upper()
Countries= pd.DataFrame(data=Countries)
temp = connection.execute('''
create table #tempTable
(
ISO_Short varchar(5)
)
''')
Countries.to_sql('Countries',engine)
我收到的错误是:
更新:编程错误:(pyodbc编程错误)(“42000”,“[42000] [Microsoft] [ODBC SQL Server Driver] [SQL Server]在数据库'databasename'中拒绝了CREATE TABLE权限。 (262)(SQLExecDirectW)”)[SQL:u“\ nCREATE TABLE [Countries](\n\t[index] BIGINT NULL,\n\t[ISO_Short] VARCHAR(max) NULL\n)\n\n'”]
我想到的另一个选项是使用Pyodbc将Countries转换为字典,然后将字典值传递到临时表中。 使用此方法,一切正常,直到我尝试将字典传递到临时表。 我使用以下代码来使用此方法:
import pandas as pd
import pyodbc
import pandas.io.sql as psql
cnxn = pyodbc.connect('''DRIVER={SQL Server};SERVER=telsmith;
DATABASE=DatabaseNameHere;Trusted_Connection=yes;''')
cursor = cnxn.cursor()
Countries= '''
SELECT DISTINCT
a.CountryRegionID AS ISO_Short,
b.Name
FROM
CustTable AS a
LEFT JOIN AddressCountryRegion AS b
ON b.CountryRegionID = a.CountryRegionID
'''
Countries= psql.read_sql(Countries, cnxn)
Countries= Countries['ISO_Short'].str.upper()
Countries= pd.DataFrame(data=Countries)
Countriesdict = Countries.to_dict()
Temp = '''
create table #tempTable
(
ISO_Short varchar(5)
)
'''
cnxn.commit()
# This is where I run into difficulty
placeholders = ', '.join(['%s'] * len(Countriesdict ))
columns = ', '.join(Countriesdict .keys())
sql = "INSERT INTO #tempTable VALUES ( %s )" % (placeholders)
cursor.execute(sql, Countriesdict.values())