The python method for inserting data into a database:
- First read in the data and establish a database connection;
- Then create the database;
- Then the insert data statement is executed, iteratively reading each row of data;
- Just close the database connection at the end.
For example, now we want to insert the following Excel data table into the MySQL database, how to achieve it?
Implementation Code:
#Import the data modules to be used
import pandas as pd
import pymysql
# Read in the data
filepath = 'E:\_DataSet\catering_sale.xls'
data = pd.read_excel(filepath)
# Establish a database connection
db = ('localhost','root','1234','python_analysis')
# Get cursor object
cursor = ()
# Create a database, if the database already exists, be careful not to duplicate the primary key, or error
try:
('create table catering_sale(num int primary key,date datetime, sale float )')
except:
print('The database already exists!')
# Insert data statement
query = """insert into catering_sale (num, date, sale) values (%s,%s,%s)"""
# Iteratively read each row of data
There's a mandatory type conversion for elements in #values, otherwise you'll get an error.
# There should be other, more appropriate ways to look further into it
for r in range(0, len(data)):
num = [r,0]
date = [r,1]
sale = [r,2]
values = (int(num), str(date), float(sale))
(query, values)
# Close cursor, commit, close database connection
# Without these shutdown operations, no data can be viewed in the database after execution
()
()
()
# Re-establish database connection
db = ('localhost','root','1234','python_anylysis')
cursor = ()
# Query the database and print the contents
('''select * from catering_sale''')
results = ()
for row in results:
print(row)
#Close
()
()
()
Knowledge Point Expansion:
database connection pool
Database connections are expensive, a connection goes through TCP three times handshaking and four times waving, and the maximum number of threads on a computer is also limited
The database connection pooling technique is to create the connection first and then take it out and use it directly
import ,
config={
"host": "localhost", "port": "3306",
"user": "root", "password": "password",
"database": "demo"
}
try:
pool=(**config,pool_size=5)
con=pool.get_connection()
con.start_transaction()
cursor = ()
sql = "INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s);"
(sql, (70, "SALES", "HUBAI"))
()
except Exception as e:
if "con" in dir():
()
print(e)
# do not need to close con
to this article on python will insert data into the database code to share the article is introduced to this, more related to python how to insert data into the database content, please search for my previous posts or continue to browse the following related articles I hope you will support me more in the future!