使用python在现有sqlserver表中追加excel数据

2024-03-29 01:39:13 发布

您现在位置:Python中文网/ 问答频道 /正文

我有一些CSV文件,其中包含重复出现的数据,因此我需要使用这个python脚本更新sqlserver。你知道吗

我尝试过更新Microsoft SQL驱动程序,但这对我没有帮助。你知道吗

下面是我的python代码:

import pandas as pd
import numpy as np
import seaborn as sns
import scipy.stats as stats
import matplotlib.pyplot as plt
from datetime import time 
from datetime import date
import pandas.io.sql
import pyodbc
import xlrd

server ='asd'
db = 'asd'

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + db + ';UID=asd ;PWD=asd')
cursor=conn.cursor()


query = """
INSERT INTO Db.table (
    Emp_ID  ,
    Global_ID,
    Emp_NAME,
    Org,
    SBU,
    BU,
    Sub_BU,
    HR_Location,
    Swipe_Loc,
    Descp,
    InOutDate,
    InTime,
    OutTime,
    ActHrs,
    ShiftCode,
    AttendanceClassification,
    ActualHrs
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""


InOutDate= date.today()
InTime = time(11,11,11)
OutTime = time(11,11,11)
ActHrs = time(11,11,11)
ActualHrs = time(11,11,11)

values = ('2134123', '123213', 'Eqqwe', 'Org' , 'SBU' , 'BU ', 'Sub_BU' , 'HR_Location' ,'Swipe_Loc' ,' Descp' , InOutDate , InTime , OutTime , ActHrs , 'ShiftCode' ,'AttendanceClassification' ,ActualHrs )   
cursor.execute(query, values)
conn.close()

执行查询时出现以下错误:

Traceback (most recent call last):
File "update.py", line 97, in <module>
cursor.execute(query, values)
pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC SQL Server Driver]Optional feature not implemented (0) (SQLBindParameter)')

Tags: importsqltimeasconnquerycursorpyodbc
2条回答

确保DateTime格式在python和SQL之间兼容

您忘记在execute之后添加cursor.commit()。Execute命令只能用于某些selects和read only查询。如果你想改变一些东西,你应该在后面加上cursor.commit()。你知道吗

相关问题 更多 >