Python uses pymysql to connect with MySQL to add, delete, change and query

List of articles

• • One 、 install PyMysql：
  • • • 1. Mode one ： Using the command line
      • 2. Mode two ： adopt PyCharm compiler
  • Two 、 operation MySQL Database steps
  • • • 1. Use import Import the corresponding class
      • 2. Get a connection to the database
      • 3. Create cursor object
      • 4. perform SQL sentence
      • 5. Close database connection
  • 3、 ... and 、 Case study
  • • • 1. Query single data
      • 2. Query multiple data
      • 3. Create database tables
      • 4. insert record

One 、 install PyMysql：

1. Mode one ： Using the command line

open cmd Enter the following code ：

pip install PyMysql

2. Mode two ： adopt PyCharm compiler

If you use a development tool ：pycharm, You can go right in setting Input Project interperter Go inside to download , Click the plus sign to download the corresponding dependent package l 了

Two 、 operation MySQL Database steps

1. Use import Import the corresponding class

import pymysql

Tips ： The following methods can be used to eliminate Pycharm Warnings in the compiler （ Just import , To wrap ）

import warnings
warnings.filterwarnings("ignore")

2. Get a connection to the database

db = pymysql.connect(" host IP Address ", " user name ", " password ", " The name of the database to connect to ")

3. Create cursor object

cursor = db.cursor()

4. perform SQL sentence

 cursor.execute("SQL sentence ")

5. Close database connection

db.close()

3、 ... and 、 Case study

Several functions ：

function explain fetchone() Returns a query object fetchall() Go back to all the lines rowcount() Return execution execute(): The number of rows affected after the operation

1. Query single data

def findAll():
# 1. Open database connection 
db = pymysql.connect("localhost", "root", "root", "student")
# 2. Create cursor object 
cursor = db.cursor()
# perform SQL Inquire about 
cursor.execute("select * from user")
# Get a single piece of data 
dataOne = cursor.fetchone()
# Close database connection 
db.close()

2. Query multiple data

def findAll():
# 1. Open database connection 
db = pymysql.connect("localhost", "root", "root", "student")
# 2. Create cursor object 
cursor = db.cursor()
# Query all records 
cursor.execute("select * from user")
dataAll = cursor.fetchall()
print(dataAll)
# Close database connection 
db.close()

3. Create database tables

Be careful ： Determine whether the table name exists before creating , If there are exceptions

def createTable():
db = pymysql.connect("localhost", "root", "root", "student")
cursor = db.cursor()
# Create table SQL
sql = """create table student(sno varchar(12),name varchar(12))"""
# Perform the create table operation 
cursor.execute(sql)
db.close()

4. insert record

def insert():
print(" perform ：insert...")
db = pymysql.connect("localhost", "root", "root", "student")
cursor = db.cursor()
sql = """insert into student(sno,name)values('2018010211',' Zhang Xiaofei ')"""
try:
# Perform the operation 
cursor.execute(sql)
# Commit transaction 
db.commit()
# Number of switching back 
count = cursor.rowcount
print(count)
print(" Submit successfully ")
except:
# Rollback on error 
db.rollback()
print(" Something unusual happened ...")
db.close()

Tips ： Other operations only need to be modified SQL Sentence can be used