python operation mysql code summary

Install the module

windows:pip install pymysql

ubuntu:sudo pip3 install pymysql

python operates on the mysql step

import pymysql

(1) Link to mysql database

db = ES21en. connect(hostname, username, password, database name)

(2) Set character encoding

db.set_charset('utf8')

(3) Create the cursor object

cursor = db.cursor()

(4) Prepare sql statement

sql = '...'

(5) Execute sql statement

cursor.execute(sql)

(6) Obtain all result sets

cursor.fetchall()

(7) Obtain 1 result set

cursor.fetchone()

(8) Get the number of affected rows

cursor.rowcount

(9) Close the database link

db.close()

pymysql's handling of things

Transaction processing is enabled by default

You need to commit or roll back

Complete operation

import pymysql
db = pymysql.connect('127.0.0.1','root','123456','hz03')
db.set_charset('utf8')
cursor = db.cursor()
try:
  sql = 'insert into goods values(null,1," Name of commodity ",12.1)'
  cursor.execute(sql)
  db.commit()
except:
  db.rollback()
print(cursor.rowcount)
db.close()