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