使用pymssql将数据插入SQL Server表 [英] Insert Data to SQL Server Table using pymssql

问题描述

我正在尝试将数据帧写入SQL Server表.我的代码:

I am trying to write the data frame into the SQL Server Table. My code:

conn = pymssql.connect(host="Dev02", database="DEVDb")
cur = conn.cursor()
query = "INSERT INTO dbo.SCORE_TABLE VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
cur.executemany(query, df_sql)
conn.commit()
cur.close()
conn.close()

df_sql的维数为(5860, 20)，即数据框中的列数与SQL Server表中的列数相同.仍然出现以下错误:

The dimension of the df_sql is (5860, 20) i.e. the number of columns in the data frame is same as the number of columns in the SQL Server Table. Still I am getting following error:

ValueError:sql中的占位符多于可用的参数

ValueError: more placeholders in sql than params available

以下更新

根据评论之一，我尝试使用turbodbc如下:

As per one of the comments, I tried using turbodbc as below:

conn = turbodbc.connect(driver="{SQL Server}", server="Dev02", Database="DEVDb")
conn.use_async_io = True
cur = conn.cursor()
query = "INSERT INTO dbo.STG_CONTACTABILITY_SCORE VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
cur.executemany(query, df_sql.values)
cur.commit()
cur.close()
conn.close()

我遇到以下错误:

ValueError:具有多个元素的数组的真值是 模糊的.使用a.any()或a.all()

ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all()

我不明白.怎么了我看到了df_sql.values，但没有发现任何问题.

I don't get it. What is wrong here. I see df_sql.values and I don't find anything wrong.

ndarray的第一行如下:

The first row of ndarray is as below:

[nan 'DUSTIN HOPKINS' 'SOUTHEAST MISSOURI STATE UNIVERSITY' 13.0
  '5736512217' None None 'Monday' '8:00AM' '9:00AM' 'Summer' None None None
  None '2017-12-22 10:39:30.626331' 'Completed' None '1-11KUFFZ'
  'Central Time Zone']

推荐答案

我认为您只需要指定每个列名即可，并且不要忘记该表必须具有id字段来收费数据帧索引:

I think you just need to specify each column name and don't forget the table must have the id field to charge the data frame index:

conn = pymssql.connect(host="Dev02", database="DEVDb")
cur = conn.cursor()
query = """INSERT INTO dbo.SCORE_TABLE(index, column1, column2, ..., column20)
            VALUES (?, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 
            %s, %s, %s, %s, %s, %s)"""
cur.executemany(query, df_sql)
conn.commit()
cur.close()
conn.close()

好吧，我一直在使用熊猫，并且将最后一个数据帧导出到csv中，如下所示:

Ok I have been using pandas and I exported the last data frame to csv like:

df.to_csv('new_file_name.csv', sep=',', encoding='utf-8')

然后我只使用了pyobdc和BULK INSERT Transact-SQL，如:

Then I just used pyobdc and BULK INSERT Transact-SQL like:

import pyodbc

conn = pyodbc.connect(DRIVER='{SQL Server}', Server='server_name', Database='Database_name', trusted_connection='yes')
cur = conn.cursor()

cur.execute("""BULK INSERT table_name
               FROM 'C:\\Users\\folders path\\new_file_name.csv'
               WITH
               (
                   CODEPAGE = 'ACP',
                   FIRSTROW = 2,
                   FIELDTERMINATOR = ',',
                   ROWTERMINATOR = '\n'
               )""")
conn.commit()

cur.close()
conn.close()

将15314行记录到SQL Server中是一秒钟.我希望这能给您一个主意.

It was a second to charge 15314 rows into SQL Server. I hope this gives you an idea.

这篇关于使用pymssql将数据插入SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！