pymysql operates the database

1. Introduction

Pymsql is a module in Python that operates MySQL. Its usage is almost the same as MySQLdb, but currently pymysql supports python3. . Use

1. Install

　 pip install pymysql

2. Use operation

Let’s start with a complete connection plus basic operations

import pymysql
  
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标
cursor = conn.cursor()
  
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
  
# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
  
# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
  
  
# 提交，不然无法保存新建或者修改的数据
conn.commit()
  
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

Insert data into the database, use try statements, and roll back automatically when an exception occurs

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()

# 关闭数据库连接
db.close()

3. Insert multiple pieces of data into the data table. Use the executemany method to insert multiple pieces of data in the production environment. After obtaining the data in the background, pass in the statement in the form of a list ([('v1','v2' ),('v3','v4')])

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标
cur = conn.cursor()
 if request.method == "POST":
        title = request.POST.get("title")
        title_en = request.POST.get("title_en")
        content = request.POST.get("content")
        content_en = request.POST.get("content_en")
        notification_type =request.POST.get("notification_type").strip()
        user_list = request.POST.get("user_list")
        updated_datetime = datetime.now()
        created_datetime = datetime.now()
        values_list = []
         for user in user_id_list:
                temp = updated_datetime,created_datetime,title,title_en,content,content_en,notification_type,user['id']
                values_list.append((temp))
　　　　　try:
        　　cur.executemany('''insert into app_notification(updated_datetime, created_datetime, title, title_en,
                                  content, content_en, notification_type, is_read, recipient_id)
                      values(%s, %s, %s, %s, %s, %s, %s, 0, %s)''',values_list)
            conn.commit()
            conn.close()
　　　　　
　　　　except Exception as err:
    　　　　conn.rollback()
    　　　　logging.error(err)
    　　　　logging.error(traceback.format_exc())
    　　　　conn.close()

# Get the latest self-increasing ID

　 new_id = cursor.lastrowid

4. Database query operation

Python queries Mysql using the fetchone() method to obtain a single piece of data, Use the fetchall() method to get multiple pieces of data.

fetchone(): This method gets the next query result set. The result set is an object

fetchall(): receives all the returned result rows.

rowcount: This is a read-only property and returns the number of rows affected after executing the execute() method.

import pymysql
  
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.execute("select * from hosts")
  
# 获取第一行数据
row_1 = cursor.fetchone()
  
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()
  
conn.commit()
cursor.close()
conn.close()

Note: When fetching data, proceed in order. You can use cursor.scroll(num, mode) to move the cursor position, such as:

cursor.scroll(1, mode='relative') # Move relative to the current position

cursor.scroll(2,mode='absolute') # Move relative to absolute position

5. fetch data type

About the default data obtained is the ancestor type. If you want or dictionary type data, that is:

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
  
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
  
# 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()")
  
result = cursor.fetchone()
  
conn.commit()
cursor.close()
conn.close()

Error handling

DB API defines some database operation errors and exceptions, which are listed in the following table These errors and exceptions: