Python pymysql--mysql Database python operation

Objective

MySQL database is currently one of the most popular databases, many programming languages have its support expansion package interface, of course, Python also has a dedicated extension library to help developers to the MySQL database to do the appropriate operation. Among them, because the Python version is different, the MySQL supports the library also has the difference, at present uses the more widespread has the pymysql and the MYSQLDB, the two function does not have the obvious difference, only the former supports the Python3. Version x, which applies to python2. X version.

Here to Python3. The X version is platform-based and provides a brief introduction to the Pymysql and how to use it.

Installation

Install directly using the PIP command

Pip Install Pymysql

Conceptual basis

In Pymysql (and, of course, other SQL database interfaces are the same), there are three basic concepts: Connection objects, cursor objects, and the results of querying SQL SELECT statements.

1) Connection object: Represents a connection to the database, which is the interface for the commit and rollback operations, provides details of the database package, and can generate cursor objects.

2) Cursor object: Represents the SQL statement that is required to commit execution, and it can also be used to access and traverse the execution results of the SQL statement.

3) Query The result of the SQL SELECT statement: The execution result of the SQL SELECT statement is a nested sequence in Python that represents the rows in the database table.

Use Method 1) to initialize the preparation

ImportPymysql# Initializing the connection configurationConnectconfig = {     'Host':'localhost',     'Port': 3306,     'User':'Root',     'passwd':'******',     'CharSet':'UTF8',     'DB':'TESDBT' }# Create a Connection objectdb =Pymysql.connect (Host=connectconfig['Host'], Port=connectconfig['Port'], user=connectconfig['User'], passwd=connectconfig['passwd'], CharSet=connectconfig['CharSet'], DB=connectconfig['DB'] )# creating a Cursor objectcursor = Db.cursor ()

2) Execute SQL statement example to create a table and add records

Create a data table sql="create table people (name char (), Job char (TEN), pay int (4)); " cursor.execute (SQL)

# Add a recordCursor.execute ('INSERT into people values (%s,%s,%s);',('Bob','Dev', 50000))# or you can add multiple records at oncerows = [('Sue','mus', 70000), ('Ann','ADM', 60000)]cursor.executemany ('INSERT into people values (%s,%s,%s);', rows)

transaction commits are required after the operation is completed for the database to be saved Db.commit ()  In addition, the connection object also has a transaction rollback operation: Db.rollback ()

finally close the cursor object and the Connection object cursor.close () db.close ( )

Example 2--querying, updating, and deleting records

query all records in the People Table cursor.execute ('select * from people; '  = cursor.fetchall () for in result:    print( Row)

"" " Output: (' Bob ', ' dev ', 50000) (' Sue ', ' mus ', 70000) (' Ann ', ' adm ', 60000) " "

Update the People table in the job for ADM staff salary increase 12000cursor.execute ('update people set pay=pay+12000 where job= ' adm '; ' ) cursor.execute ('select * ' from people where job= "ADM"; '  = cursor.fetchall ()print(result)

"" " Output: [(' Ann ', ' adm ', 72000)] " "

Delete the record named Bob in the People table cursor.execute ('Delete from people where name= ' Bob '; ' ) cursor.execute ('select * from people; '  = cursor.fetchall () for in result:    Print  (row)

"" " Output: (' Sue ', ' mus ', 70000) (' Ann ', ' adm ', 72000) " "3) Other uses

①cursor.rowcount property: Returns the number of rows affected after the SQL statement has been executed

use ROWCOUNT to get the number of rows affected cursor.execute ('select * from people; ' )print(cursor.rowcount)  output:3

②cursor.description property: Returns the field Name and field properties obtained after executing the SQL query statement

use description to get table field names and Fields Properties cursor.execute ('select * from people; ' )print(cursor.description)

"" " Output: (' name ', None,none,none,none,none,none), (' Job ', None,none,none,none,none,none), (' Pay ', None,none, None,none,none,none))# Please refer to the relevant API description "" For the specific column property description

format table field name cursor.execute ('select * from people; '   for in cursor.description]print(colnames)

"" " Output: [' name ', ' job ', ' pay '] '" "

③cursor.fetchall (): Returns a list of results after execution of the SQL query statement (the element is a row tuple form)

Cursor.fetchone (): Returns the first row that results from executing the SQL query statement

Cursor.fetchmany (N): Returns the first n rows of results when the SQL query statement has been executed

It is important to note that fetch rows are positioned according to the cursor, that is, each time the fetch-related rows are called, the cursor moves, and the next time the fetch is called, the record is obtained from the position of the last cursor, and an empty sequence is returned when it has reached the end of the table.

successive calls to the Fetch method cursor.execute ('select * from people; '  = cursor.fetchall ()print(rows1)  # Output:[(' Bob ', ' dev ', 50000), (' Sue ', ' mus ', 70000), (' Ann ', ' adm ', 60000)]rows2 = cursor.fetchall ()print(rows2)  # Output:[]

④cursor.scroll (Num,mode): Controls the movement of cursor objects in order to reset the Fetch method

# Use the Scroll method to adjust the cursor position cursor.scroll (1,mode='relative')  #  Move cursor.scroll (2,mode='absolute') relative to the current position order  #  relative absolute position order movement 

The above is the Python database operation of the Pymysql library Some simple introduction, I hope that we can help, if there are any questions welcome you to learn to exchange messages.

For more information about learning Pymysql Welcome to the relevant documentation (https://pymysql.readthedocs.io/en/latest/index.html)

Python pymysql--mysql Database python operation