将Python数据框写入MSSQL表 [英] Writing Python Dataframe to MSSQL Table

问题描述

我目前有一个23列和20,000行的Python数据框。

I currently have a Python dataframe that is 23 columns and 20,000 rows.

我想使用Python代码将数据帧写入具有凭据的MSSQL服务器中。

Using Python code, I want to write my data frame into a MSSQL server that I have the credentials for.

作为测试，我可以使用以下代码成功地向表中写入一些值：

As a test I am able to successfully write some values into the table using the code below:

connection = pypyodbc.connect('Driver={SQL Server};' 
                              'Server=XXX;' 
                              'Database=XXX;' 
                              'uid=XXX;' 
                              'pwd=XXX')

cursor = connection.cursor()

for index, row in df_EVENT5_15.iterrows():
    cursor.execute("INSERT INTO MODREPORT(rowid, OPCODE, LOCATION, TRACKNAME)

cursor.execute("INSERT INTO MODREPORT(rowid, location) VALUES (?,?)", (5, 'test'))
connection.commit()

但是我怎么写所有数据框表中的行添加到MSSQL服务器上吗？为此，我需要在Python环境中编写以下步骤：

But how do I write all the rows in my data frame table to the MSSQL server? In order to do so, I need to code up the following steps in my Python environment:

1. 删除MSSQL服务器表中的所有行

1. Delete all the rows in the MSSQL server table

将我的数据帧写入服务器

Write my dataframe to the server

推荐答案

我意识到距您提出要求已经有一段时间了，但是删除SQL Server表中所有行（问题的第1点）的最简单方法是发送命令

I realise it's been a while since you asked but the easiest way to delete ALL the rows in the SQL server table (point 1 of the question) would be to send the command

TRUNCATE TABLE Tablename

这将删除所有数据在表中，但将表和索引保留为空，因此您或DBA无需重新创建它。在运行时，它还使用较少的事务日志。

This will drop all the data in the table but leave the table and indexes empty so you or the DBA would not need to recreate it. It also uses less of the transaction log when it runs.

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