I'm using python pymysql to connect to the MySQL database. If you don't have pymysql installed on your computer, you can install it directly from the command line via pip install pymysql.
I. Inserting a single piece of data into a mysql database via a python script
When writing sql statements, use %s for placeholders, regardless of the type of the field.
Two ways of inserting a single piece of data are documented here:
1, directly with the execute method to execute the sql statement
# Import pymysql packages
import pymysql
# Create database connections
conn = (host="localhost",port=3306,user="root",passwd="123456",db="test1" )
# Get a cursor object
cursor=()
# Perform database insertion operations
('insert into student(id,name,age) values (123456,"tom",12)')
#Submit
()
#Close the connection
()
()2. Separate out the sql statement and use %s as a placeholder in the statement
# Import pymysql packages import pymysql # Create database connections conn = (host="localhost",port=3306,user="root",passwd="123456",db="test1" ) # Get a cursor object cursor=() #sql statement with %s as a placeholder and a tuple for the argument sql="insert into student values(%s,%s,%s)" param=(23456,'lilei',20) # Perform database inserts (sql,param) #Submit () #Close the connection () ()
Second, through the python script to mysql database batch insert data
Two ways of inserting data in bulk are documented here:
1. Loop through execute, using the for statement
# Import pymysql packages
import pymysql
# Create database connections
conn = (host="localhost",port=3306,user="root",passwd="123456",db="test1" )
# Get a cursor object
cursor=()
# Set parameter i, for statement loop
for i in range(1,10):
param=str(i)
sql="insert into student values(%s,'yy',20)"
(sql,param)
()
#Close the connection
()
()2. By executemany, the format of the data must be list[tuple(),tuple(),tuple()] or tuple(tuple(),tuple(),tuple()))
import pymysql # Open a database connection conn = (host="localhost",port=3306,user="root",passwd="123456",db="test1" ) # Use the cursor() method to get the operation cursor cursor = () #Batch insertion of data sql="insert into student values(%s,%s,%s)" # each value as a tuple and the entire set of parameters as a tuple param=((111111,'haha',13),(22222,'hehe',34)) # or each value as a tuple and the entire set of parameters as a list : param=[(111111,'haha',13),(22222,'hehe',34)] # Insert data in bulk using the executemany method (sql,param) #Submit () #Close () ()
Third, the cursor execution command method
1, callproc (self, procname, args): used to execute the procedure, the parameters received for the procedure name and parameter list, the return value for the number of affected lines
2, execute(self, query, args): execution of a single sql statement, the parameters received for the sql statement itself and the use of the parameter list, the return value for the number of affected lines
3, executeemany (self, query, args): the execution of a single sql statement, but repeat the execution of the parameters in the parameter list, the return value for the number of affected lines
4, nextset(self): move to the next result set
Fourth, the cursor to accept the return value of the method
1. fetchall(self): receive all the returned lines.
2, fetchmany(self, size=None): receive size return result line. If the value of size is greater than the number of rows returned, then the data will be returned.
3. fetchone(self): return a result line.
4. scroll(self, value, mode='relative'): moves the pointer to a particular line. If mode='relative', move the value bar from the current row, if mode='absolute', move the value bar from the first row of the result set.
V. cursor executes the query statement and returns the result
# Execute the operation of the query
("select * from cdinfo")
# Use the fetcall method to get all the results returned by the query, save the results to tup, each result is of tuple type and all the tuples form a tuple set
tup=()
print（tup）# Output the entire result
print tup[0][3]#Output tuple set，The fourth element of the first tupleTo this article on python to MySQL database to insert data to this article, more related to python database to insert data content, please search for my previous articles or continue to browse the following related articles I hope you will support me in the future more!