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!