Python MySQL database connection drives the use of pymysql

Keywords: Python

This article introduces the simple use of MySQL connection driven python database

1. Install pymysql module

Modules not installed are installed first using pip

pip3 install pymysql

2. Create database connection

The syntax for creating a database connection using the module pymysql is

The parameters passed are host, user username, password, and db database name

conn = pymysql.connect(host='localhost',user='www-data',password='www-data',db='awesome')

You can also store these key value pairs as a dict and pass them in the following way

conn = pymysql.connect(**kw)

3. Create database buoy

Use the following method to create a buoy, and use the database connection object conn plus the method cursor to create a database connection buoy

cursor = conn.cursor(pymysql.cursors.DictCursor)

4. Execute sql statement

With the database buoy cursor, you can execute sql statements. The execution syntax is as follows

cursor.execute(sql,args)
sql # sql statements to be executed, such as' select * from table '_ name'
args # Replace the formatted string of the sql statement, that is, the sql statement can use% s to represent a string, and then replace it with the corresponding variable or parameter in args. Args is a list or tuple, that is, an ordered sequence, which needs to be one-to-one corresponding to% s in sql
# For example, sql='select * from table_name where id=%s'  args=['12345']
# It is equivalent to replacing% s in sql with parameters in args 
# select * from table_name where id='12345'

5. Execute sql statements in practice

Before implementing sql statements, we first create a database and corresponding tables on the machine for testing

The host, user name, password, database name and table name corresponding to the database are as follows

host: localhost
user: www-data
password: www-data
db: awesome
table_name: users

The sql statement to create the table name is as follows. You need to create the corresponding table in the database

CREATE TABLE `users` (
  `id` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `passwd` varchar(50) NOT NULL,
  `admin` tinyint(1) NOT NULL,
  `name` varchar(50) NOT NULL,
  `image` varchar(500) NOT NULL,
  `created_at` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email` (`email`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The corresponding structure of the created table is as follows

mysql> desc users;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | varchar(50)  | NO   | PRI | NULL    |       |
| email      | varchar(50)  | NO   | UNI | NULL    |       |
| passwd     | varchar(50)  | NO   |     | NULL    |       |
| admin      | tinyint(1)   | NO   |     | NULL    |       |
| name       | varchar(50)  | NO   |     | NULL    |       |
| image      | varchar(500) | NO   |     | NULL    |       |
| created_at | double       | NO   | MUL | NULL    |       |
+------------+--------------+------+-----+---------+-------+
7 rows in set (2.68 sec)

① execute the insert operation

Insert a piece of data into the database

import time
# insert start
sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
args = ['test@qq.com','password',1,'test','about:blank',time.time(),'111111']
# Use replace to replace '?' Replace with '% s'
cursor.execute(sql.replace('?','%s'),args)
conn.commit()
# insert end

Insert the following code

  use_pymysql.py

import time
# insert start
sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
args = ['test@qq.com','password',1,'test','about:blank',time.time(),'111111']
# Use replace to replace '?' Replace with '% s'
cursor.execute(sql.replace('?','%s'),args)
conn.commit()
# insert end

Note: the modification operation needs to be submitted using conn.commit(), otherwise the modification will not take effect

You can see the inserted data in MySQL database query

mysql> select * from users;
+--------+-------------+----------+-------+------+-------------+------------------+
| id     | email       | passwd   | admin | name | image       | created_at       |
+--------+-------------+----------+-------+------+-------------+------------------+
| 111111 | test@qq.com | password |     1 | test | about:blank | 1637723096.69565 |
+--------+-------------+----------+-------+------+-------------+------------------+
1 row in set (0.00 sec)

 

Note: the operation also returns. The returned result is the number of databases affected by this operation. If the returned result is printed, it will be output as 1 this time

 

rs = cursor.execute(sql.replace('?','%s'),args)
conn.commit()
print(rs)

② execute the update operation

Modify data we modify the data just inserted according to the condition id

Before executing the modification, you need to annotate the insert statement just executed, otherwise an error will be reported because the value creation is repeated

# update start
sql = 'update `users` set `email`=?, `passwd`=?, `admin`=?, `name`=?, `image`=?, `created_at`=? where `id`=?'
args = ['test2@qq.com','password',1,'test2','about:blank',time.time(),'111111'] 
print(cursor.execute(sql.replace('?','%s'),args))
conn.commit()
# update end

After execution, the email and name are modified

 

 

  ③ execute the delete operation

Execute the delete operation to delete the data just inserted

# delete start
sql = 'delete from `users` where `id`=?'
args = ['111111'] 
print(cursor.execute(sql.replace('?','%s'),args))
conn.commit()
# delete end

This data is also deleted according to the value specified by the keyword id

④ execute the select operation

Before performing the select operation, we ensure that there is at least one piece of data in the database

cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = 'select * from users'
args = []
# Number of results returned by executing query
# Execute query
rs=cursor.execute(sql,args)
# Get query results
# Get the first result of the query and return a dict. The dict element is the key value pair corresponding to the query
# If there are multiple query results, execute once, move the cursor to the next data, and return another data after execution
print(cursor.fetchone())

Similarly, the returned result of the select operation is that this operation affects several rows of data, that is, several pieces of data are queried according to the criteria, and this time it is 1

To get the result, use the fetchone(), fetchall(), fetchant (Num) of the cursor to get it. The difference is

# Get the first result of the query and return a dict. The dict element is the key value pair corresponding to the query
# If there are multiple query results, execute once, move the cursor to the next data, and execute again to return another data
print(cursor.fetchone())
# Get all the results of the query and return a list. The list element is dict, and the dict element is the key value pair corresponding to the query
print(cursor.fetchall())
# Get the first few results of the query and return a list. The list element is dict, and the dict element is the key value pair corresponding to the query
print(cursor.fetchmany(1))

fetchone() gets dict

fetchall() and fecthmany(num) get a list, and the element of the list is dict

Their corresponding results are as follows

print(cursor.fetchone())
# {'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637723578.5734}

print(cursor.fetchall())
# [{'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637723578.5734}]

print(cursor.fetchmany(1))
# [{'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637723578.5734}]

Note: for a cursor buoy, the buoy will move after obtaining data. If only one piece of data has been queried, and the cursor.fetchone() method has been used to take out this piece of data, the buoy has moved to no data, and then fetchone(), fetchall(), fetchmany (Num) is used to obtain it, then None or an empty list will be returned []

When there is no data at the buoy, use fetchone() to return None, and use fetchall() to return []

⑥ written as a function

To facilitate calling, you can write the executed sql statement as a function. It is divided into two functions. One function executes select to return the result, and the other function executes insert, update and delete to return the number of affected rows

def select(sql,args,size=None):
    log(sql,args)
    cursor =  conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute(sql.replace('?','%s'),args or ())
    if size:
        rs = cursor.fetchmany(size)
    else:
        rs = cursor.fetchall()
    cursor.close
    logging.info('rows returned: %s' % len(rs))
    return rs

def execute(sql,args):
    
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    try:
        cursor.execute(sql.replace('?','%s'),args)
        # The rowcount method returns the influence function
        rs = cursor.rowcount
        cursor.close()
        conn.commit()
    except:
        raise
    return rs

The select function adds a default parameter size, that is, the number of data to be queried. You need to pass an integer to return the number of pieces of this integer. If not, all query results will be returned. The result is a list, and the element of this list is the dict composed of key value pairs of query results

The function executing the modification operation returns the number of affected items. If it returns 0, it means that the database is not affected and the modification fails. Don't forget conn.commit() to submit the modification result, otherwise the modification will not take effect

Posted by Allan- on Wed, 24 Nov 2021 03:23:07 -0800