Skip to main content
 首页 » 编程设计

python之将 pandas 数据框插入 SQL 临时表

2025年04月02日18Leo_wl

我正在尝试在 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) 

我收到的错误是:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE TABLE permission denied in database 'databasename'. (262) (SQLExecDirectW)") [SQL: u'\nCREATE TABLE [Countries] (\n\t[index] BIGINT NULL, \n\t[ISO_Short] VARCHAR(max) NULL\n)\n\n'

更新:

我想到的另一个选择是使用 Pyodbc 并将国家/地区转换为字典,然后将字典值传递到临时表中。使用这种方法,一切正常,直到我尝试将字典传递给临时表。我有以下代码使用这种方法:

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

请您参考如下方法:

这听起来有点傻,但看看错误:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE TABLE permission denied in database 'databasename'. (262) (SQLExecDirectW)") [SQL: u'\nCREATE TABLE [Countries] (\n\t[index] BIGINT NULL, \n\t[ISO_Short] VARCHAR(max) NULL\n)\n\n'

你有没有名为 databasename 的数据库?由于找不到数据库,因此无法创建表。我运行了相同的代码,它工作得很好。我相信这就是原因