python uses MYSQL database based on PYMYSQL

  • 2021-08-31 08:39:20
  • OfStack

When doing tests, you will use the database. Today, I will write an article connecting MYSQL database through python

What is an MYSQL database

MySQL is a relational database management system, developed by MySQL AB Company of Sweden, and currently belongs to Oracle products. MySQL is one of the most popular relational database management systems, and MySQL is one of the best RDBMS (Relational Database Management System, relational database management system) applications in WEB application.

What is PYMYSQL

PyMySQL is a library used to connect to the MySQL server in Python3.x, and mysqldb is used in Python2.

PyMySQL follows the Python database API v 2.0 specification and includes the pure-Python MySQL client library.

PyMySQL installation


pip install pymysql

PyMySQL use

Connect to a database

1. First import the PyMySQL module

2. Connect to the database (via connect ())

3. Create a database object (via cursor ())

4. Make additions, deletions and changes to the database


# coding:utf-8
import pymysql
#  Connect to a database 
count = pymysql.connect(
      host = 'xx.xxx.xxx.xx', #  Database address 
      port = 3306,  #  Database port number 
      user='xxxx',  #  Database account number 
      password='XXXX',  #  Database password 
      db = 'test_sll')  #  Database table name #  Create a database object 
db = count.cursor()

Find data

db. fetchone () fetches 1 piece of data

db. fetchall () fetches all data


# coding:utf-8
import pymysql
#  Connect to a database 
count = pymysql.connect(
      host = 'xx.xxx.xxx.xx', #  Database address 
      port = 3306,  #  Database port number 
      user='xxxx',  #  Database account number 
      password='xxxx',  #  Database password 
      db = 'test_sll')  #  Database name 
#  Create a database object 
db = count.cursor()
#  Write SQL Statement 
sql = "select * from students "
#  Execute sql Command 
db.execute(sql)
#  Get 1 Queries 
# restul = db.fetchone()
#  Get all the query contents 
restul = db.fetchall()
print(restul)
db.close()

Modify data

commit () needs to submit the saved content after executing SQL


# coding:utf-8
import pymysql
#  Connect to a database 
count = pymysql.connect(
      host = 'xx.xxx.xxx.xx', #  Database address 
      port = 3306,  #  Database port number 
      user='xxx',  #  Database account number 
      password='xxx',  #  Database password 
      db = 'test_sll')  #  Database table name 
#  Create a database object 
db = count.cursor()
#  Write SQL Statement 
sql = "update students set age = '12' WHERE id=1"
#  Execute sql Command 
db.execute(sql)
#  Save operation 
count.commit()
db.close()

Delete data


# coding:utf-8
import pymysql
#  Connect to a database 
count = pymysql.connect(
      host = 'xx.xxx.xxx.xx', #  Database address 
      port = 3306,  #  Database port number 
      user='xxxx',  #  Database account number 
      password='xxx',  #  Database password 
      db = 'test_sll')  #  Database table name 
#  Create a database object 
db = count.cursor()
#  Write SQL Statement 
sql = "delete from students where age = 12"
#  Execute sql Command 
db.execute(sql)
#  Save Submission 
count.commit()
db.close()

Add data

The new data involves a transaction problem here. The transaction mechanism can guarantee the uniformity of data. For example, if one data is inserted, there will be no insertion of one and a half, either all of them will be inserted or none of them will be inserted


# coding:utf-8
import pymysql
#  Connect to a database 
count = pymysql.connect(
      host = 'xx.xxx.xxx.xx', #  Database address 
      port = 3306,  #  Database port number 
      user='xxxx',  #  Database account number 
      password='xxx',  #  Database password 
      db = 'test_sll')  #  Database table name 
#  Create a database object 
db = count.cursor()
#  Write SQL Statement 
sql = "insert INTO students(id,name,age)VALUES (2,' Quiet ','26')"
#  Execute sql Command 
db.execute(sql)
#  Save Submission 
count.commit()
db.close()

To this you can find that in addition to the query does not need to save, other operations have to be submitted to save, and you will also find deletion, modification, addition, only modified SQL, other no change

Create a table

To create a table, we first define the fields of the following table contents

字段名 含义 类型
id id varchar
name 姓名 varchar
age 年龄 int


# coding:utf-8
import pymysql
#  Connect to a database 
count = pymysql.connect(
      host = 'xx.xxx.xxx.xx', #  Database address 
      port = 3306,  #  Database port number 
      user='xxxx',  #  Database account number 
      password='xxx',  #  Database password 
      db = 'test_sll')  #  Database table name 
#  Create a database object 
db = count.cursor()
#  Write SQL Statement 
sql = 'CREATE TABLE students (id VARCHAR(255) ,name VARCHAR(255) ,age INT)'
#  Execute sql Command 
db.execute(sql)
db.close()

The above is python based on PYMYSQL using MYSQL database details, more information about python using MySQL please pay attention to other related articles on this site!


Related articles: