Pymysql operation database and pymysql Database

Pymysql operation database and pymysql Database

I. Introduction

Pymsql is a module used to operate MySQL in Python. Its usage is almost the same as that of MySQLdb. However, pymysql currently supports python3.x, while the latter does not support version 3.x.

The execution statement is similar to the SQL source code.

Ii. Use

1. Install

Pip install pymysql

2. Operation

First, let's look at a complete connection and basic operations.

Import pymysql # create a connection conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '000000', db = 't1') # create a cursor = conn. cursor () # execute the SQL statement and return the number of rows affected by the receipt. Export t_row = cursor.exe cute ("update hosts set host = '1. 1.1.2 '") # execute the SQL statement and return the affected number of rows # effect_row = cursor.exe cute (" update hosts set host = '1. 1.1.2 'where nid> % s ", (1,) # Run the SQL statement and return the number of affected rows # effect_row = cursor.exe cute.pdf (" insert into hosts (host, color_id) values (% s, % s) ", [(" 1.1.1.11 ", 1), (" 1.1.1.11 ", 2)]) # submit, otherwise, the new or modified data conn cannot be saved. commit () # Close the cursor. close () # close the connection conn. close ()

Insert data to the database and use the try statement to actively roll back when an exception occurs.

#! /Usr/bin/python3import pymysql # Open the database connection db = pymysql. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation cursor = db. cursor () # SQL insert statement SQL = "INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('mac', 'mohan', 20, 'M', 2000) "" try: # execute the SQL statement cursor.exe cute (SQL) # submit it to the database to execute the db. commit () commit T: # roll back the database if an error occurs. rollback () # disable database connection to db. close ()

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

# Create a connection conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '000000', db = 't1') # create cursor 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 ("icationication_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, icationication_type, user ['id'] values_list.append (temp ))
Try: cur.exe cute.pdf (''' insert into app_notification (updated_datetime, created_datetime, title, title_en, content, content_en, icationication_type, is_read, recipient_id) values (% 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()

# Obtain the latest auto-increment ID 　　new_id =  cursor .lastrowid

4. database query operations

For Python query, Mysql uses the fetchone () method to obtain a single piece of data and the fetchall () method to obtain multiple pieces of data.

• Fetchone ():This method gets the next query result set. The result set is an object.
• Fetchall ():Receives all returned results.
• Rowcount:This is a read-only attribute and returns the number of rows affected by execution of the execute () method.

Import pymysql conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '000000', db = 't1') cursor = conn.cursor()cursor.exe cute ("select * from hosts ") # obtain the first row of Data row_1 = cursor. fetchone () # obtain the first n rows of data # row_2 = cursor. fetchiterator (3) # obtain all data # row_3 = cursor. fetchall () conn. commit () cursor. close () conn. close ()

Note: When fetch data is performed in order, you can use cursor. scroll (num, mode) to move the cursor position, for example:

• 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

The data obtained by default is of the Ancestor Type. If you want data of the dictionary type, that is:

#! /Usr/bin/env python #-*-coding: UTF-8-*-import pymysql conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '000000', db = 't1') # Set the cursor to the dictionary type cursor = conn. cursor (cursor = pymysql. cursors. dictCursor) r = cursor.exe cute ("call p1 ()") result = cursor. fetchone () conn. commit () cursor. close () conn. close ()

Error Handling

Db api defines database operation errors and exceptions. The following table lists these errors and exceptions:

Exception	Description
Warning	Triggered when a severe warning occurs, for example, data insertion is truncated. Must be a subclass of StandardError.
Error	Warning. Must be a subclass of StandardError.
InterfaceError	This is triggered when an error occurs in the database interface module rather than in the database. Must be a subclass of Error.
DatabaseError	Database-related errors are triggered. Must be a subclass of Error.
DataError	This function is triggered when an error occurs during data processing, for example, Division by zero error or data out of range. Must be a subclass of DatabaseError.
OperationalError	An error occurs when operating the database instead of being controlled by the user. For example, database operations such as accidental disconnection, database name not found, transaction processing failure, and memory allocation error occur. Must be a subclass of DatabaseError.
IntegrityError	Integrity-related errors, such as foreign key check failure. Must be a subclass of DatabaseError.
InternalError	Internal database errors, such as cursor failures and transaction synchronization failures. Must be a subclass of DatabaseError.
ProgrammingError	Program errors, such as data table not found or already exists, SQL statement syntax errors, parameter quantity errors, and so on. Must be a subclass of DatabaseError.