python mssql 报表_将Python数据框写入MSSQL表

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

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

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:

Delete all the rows in the MSSQL server table

Write my dataframe to the server

解决方案

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

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.