OJ template

2023-01-23   ES  

python read Excel file, connect the MySQL database insertion or update table

Insert table:

import pandas as pd
from sqlalchemy import create_engine

# connection database
connection_mysql = create_engine('mysql+pymysql://username:[email protected]:8888/dbname?charset=utf8')
print('---insert---')
# Read Excel
df = pd.read_excel('insert_data.xlsx')
# Insert into the database
df.to_sql('table_name',connection_mysql, if_exists='append',index=False)
# Close connection
connection_mysql.dispose()
print('---done---')

update table:

import pandas as pd
import pymysql as pm

# connection database
db = pm.connect(host="88.88.888.888",port=8888,user="username",passwd="password",db="dbname")
# Create a cursor
cursor = db.cursor()
print('---update---')
# Read Excel
df = pd.read_excel('update_data.xlsx')
# update data
data_list = df.values.tolist()
sql_replace="""replace into table_name (field1,field2,field3) values (%s,%s,%s) """
cursor.executemany(sql_replace,data_list)
# Submit
db.commit()
# Close the campaign
cursor.close()
# Close connection
db.close()

source

Related Posts

Example 6-4 UVA11988 Static Links

Python uses regular expressions to process HTML comments and labels

FRESCO’s packaging and use instructions and obtaining the Bitmap object in the cache

HDU1426 Sudoku Killer

OJ template

Random Posts

Tuple irregular value array liuxiao

HBase-MapReduce actual combat: Use MR to import the FRUIT table in HBase into the FRUITMR table in HBase

ASP.NET 2.0 GridView Unlimited Layer -to -complicated Metropolis Implementation

How to connect the Raspberry Pi gateway to the built -in LORAWAN Network Server -teach you how to connect the Raspberry Pi gateway to the 5th article of the server

WeChat confession mysterious code! Quickly send it to TA!