当前有一个包含我的Server的SQl DOcker映像,我目前正在通过运行一个python脚本连接到它,该脚本将源我的excel文件并将其转换为dataframe,然后该文件将被转换为我的DB的一个表。现在,我一直得到这个错误pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near 'nvarchar'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)"),我的python代码在这里:
import pandas as pd
import pyodbc
# Import CSV
data = pd.read_excel (r"C:\Users\c.stembridge\OneDrive - NEWREST GROUP SERVICES\Overtime Forecast Report.xlsx", sheet_name='Daily Hrs2')
df = pd.DataFrame(data)
# Connect to SQL Server
conn = pyodbc.connect("DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost;UID=SA;PWD=Working@2022;DATABASE=testdb;Encrypt=no;TrustServerCertificate=yes")
cursor = conn.cursor()
# Create Table
cursor.execute('''
CREATE TABLE Overtime_Forecast (
date nvarchar(10) primary key,
day nvarchar(9),
hrs int,
dl int,
catered int,
hrs_diff_btwn_last_day int,
catered_flight_diff int,
employees_OT_count int,
carriers int,
whole int,
half int,
total_carts int
)
''')
# Insert DataFrame to Table
for row in df.itertuples():
cursor.execute('''
INSERT INTO Overtime_Forecast (
date nvarchar(10) primary key,
day nvarchar(9),
hrs int,
dl int,
catered int,
hrs_diff_btwn_last_day int,
catered_flight_diff int,
employees_OT_count int,
carriers int,
whole int,
half int,
total_carts int)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
''',
row.Date,
row.Day,
row.Hrs,
row.DL,
row.Catered,
row.Hrs_diff_btwn_last_day,
row.Catered_flight_diff,
row.Employees_OT_count,
row.Carriers,
row.Whole,
row.Half,
row.Total_carts
)
conn.commit()发布于 2022-06-29 16:27:07
从插入中删除类型:
INSERT INTO Overtime_Forecast (
date,
day,
hrs,
dl,
catered,
hrs_diff_btwn_last_day,
catered_flight_diff,
employees_OT_count,
carriers,
whole,
half,
total_carts ...https://stackoverflow.com/questions/72804705
复制相似问题