Python's example of MySQL data manipulation

This article introduces Python3 using Pymysql Connection database, and realizes simple adding and deleting. Has a good reference value. Let's take a look at the little series.

This article introduces Python3 using Pymysql Connection database, and realizes simple adding and deleting.

What is Pymysql?

Pymysql is a library used in the python3.x version to connect to the MySQL server, and MySQLdb is used in python2.x.

Pymysql Installation

Before using Pymysql, we need to make sure that Pymysql is installed.

Pymysql Download Address: Https://github.com/PyMySQL/PyMySQL.

If it is not installed, we can install the latest version of Pymysql using the following command:

$ pip install PyMySQL

If your system does not support the PIP command, you can install it in the following ways:

1. Download the installation package installation using the GIT command (you can also download it manually):

$ git clone https://github.com/pymysql/pymysql$ CD pymysql$ python3 setup.py Install

2. If you need to make a version number, you can use the Curl command to install:

$ # x.x to Pymysql version number $ curl-l Https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | Tar xz$ CD pymysql*$ python3 setup.py Install

Note: Make sure that you have root privileges to install the above modules.

Database connection

Before you connect to a database, verify the following:

• You have created a database TestDB

• In the TestDB database, you have created table employee

• Employee table fields First_name,last_name,age,sex and income

• Connection database TestDB Use the user name "TestUser", the password is "test123", you can set your own or directly use the root user name and its password, MySQL database user authorization please use the GRANT command

• The Pymysql module has been installed on your machine.

Instance:

The following example links the MySQL TestDB database:

#!/usr/bin/python3__author__ = ' Mayi ' Import pymysql# Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", "  TESTDB ") # Create a Cursor object using the cursor () method cursorcursor = Db.cursor () # Execute SQL query cursor.execute using the Execute () method (" Select VERSION () ") # Use the Fetchone () method to get the single data. Data = Cursor.fetchone () print ("Database version:%s"% data) # Close database connection Db.close ()

Create a database table

If a database connection exists we can use the Execute () method to create a table for the database, creating table employee as follows:

#!/usr/bin/python3__author__ = ' Mayi ' Import pymysql# Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", " TESTDB ") # Use the cursor () method to create a cursor object cursorcursor = Db.cursor () # Execute SQL using the Execute () method and delete Cursor.execute if the table exists (" drop table if EXISTS EMPLOYEE ") # CREATE TABLE with pre-processing statements sql =" "CREATE Table EMPLOYEE (first_name char () not NULL, Last_Name char (a), age int,
  sex CHAR (1), INCOME FLOAT) "" "Cursor.execute (SQL) # Close database connection Db.close ()

Database Insert Operations

The following instance inserts records into table employee using the Execute SQL INSERT statement:

#!/usr/bin/python3__author__ = ' Mayi ' Import pymysql# Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", "  TESTDB ") # using the cursor () method to get the cursor cursor = db.cursor () # SQL INSERT statement sql =" "INSERT into EMPLOYEE (first_name, last_name, age, SEX, INCOME) VALUES (' Mac ', ' Mohan ', ' M ', ' + ') ' "" Try: # Execute SQL statement cursor.execute (SQL) # Commit to Database Execution Db.commit () except: # If something went wrong Error rollback Db.rollback () # Close database connection Db.close ()

The above example can also be written as follows:

#!/usr/bin/python3__author__ = ' Mayi ' Import pymysql# Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", "  TESTDB ") # Gets an action cursor using the cursor () method cursors = db.cursor () # SQL INSERT statement sql =" INSERT into EMPLOYEE (first_name, \ last_name, age, SEX,  INCOME) \ VALUES ('%s ', '%s ', '%d ', '%c ', '%d ') '% \ (' Mac ', ' Mohan ', ', ' M ', ') Try: # Execute SQL statement cursor.execute (SQL) # Execute SQL Statement db.commit () except: # rollback Db.rollback () # When an error occurs * Close database connection db.close ()

Database query Operations

Python queries MySQL uses the Fetchone () method to get a single piece of data, using the Fetchall () method to get multiple data.

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

• Fetchall (): Receive all returned result rows

• RowCount: This is a read-only property and returns the number of rows affected after executing the Execute () method

Instance:

Query all data with the salary (payroll) field greater than 1000 in the employee table:

#!/usr/bin/python3__author__ = ' Mayi ' Import pymysql# Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", " TESTDB ") # Use the cursor () method to get the cursor = db.cursor () # SQL query Statement sql =" SELECT * from EMPLOYEE \ WHERE INCOME > '%d ' "% (100 0) Try: # Execute SQL statement cursor.execute (SQL) # get all record list results = Cursor.fetchall () for row in results:fname = row[0] lname = row  [1] age = row[2] sex = row[3] income = row[4] # Prints the result print ("fname=%s,lname=%s,age=%d,sex=%s,income=%d"% \ (fname, lname, Age, sex, income)) Except:print ("Error:unable to Fecth Data") # Close database connection Db.close ()

Database update operations

The update action is used to update data in the data table, and the following instance modifies all the sex fields in the TestDB table to ' M ', and the Age field increments by 1:

#!/usr/bin/python3__author__ = ' Mayi ' Import pymysql# Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", " TESTDB ") # Use the cursor () method to get the cursor = db.cursor () # SQL UPDATE statement sql =" Update EMPLOYEE SET age = age + 1 WHERE SEX = '%c ' "% ( ' M ') Try: # Execute SQL statement cursor.execute (SQL) # Commit to Database Execution Db.commit () except: # Rollback when an error occurs db.rollback () # Close database connection Db.close ()

Delete operation

The delete operation deletes data from the data table, and the following example shows all data in the Delete data table employee with age greater than 20:

#!/usr/bin/python3__author__ = ' Mayi ' Import pymysql# Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", " TESTDB ") # Use the cursor () method to get the cursor cursor = db.cursor () # SQL DELETE statement sql =" Delete from EMPLOYEE WHERE age > '%d ' "% () Try: # Execute SQL statement cursor.execute (SQL) # Commit modify Db.commit () except: # Rollback when an error occurs db.rollback () # Close connection Db.close ()

Execution transactions

Transaction mechanisms ensure data consistency.

A transaction should have 4 properties: atomicity, consistency, isolation, persistence. These four properties are often called acid properties.

• Atomicity (atomicity). A transaction is an inseparable unit of work, and the operations included in the transaction are either done or not.

• Consistency (consistency). The transaction must be to change the database from one consistency state to another. Consistency is closely related to atomicity.

• Isolation (Isolation). Execution of one transaction cannot be disturbed by other transactions. That is, the operations inside a transaction and the data used are isolated from other transactions that are concurrently executing, and cannot interfere with each other concurrently.

• Persistence (durability). Persistence, also known as permanence (permanence), refers to the fact that once a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on it.

Instance

# SQL Delete Record statement sql = "Delete from EMPLOYEE WHERE age > '%d '" "%" try: # Execute SQL statement cursor.execute (SQL) # Submit to Database Db.commit () E Xcept: # rollback Db.rollback () when an error occurs

For a database that supports transactions, in Python database programming, when the cursor is established, an invisible database transaction is automatically started.

Commit () method all the update operations of the cursor, the rollback () method rolls back all operations of the current cursor. Each method starts a new transaction.

Error handling

Abnormal	Describe
Warning	Triggered when there is a critical warning, such as the insertion of data is truncated and so on. Must be a subclass of StandardError.
Error	Warning all other error classes. Must be a subclass of StandardError.
Interfaceerror	Triggered when there is an error in the Database interface module itself (not a database error). Must be a subclass of error.
Databaseerror	Triggers when a database-related error occurs. Must be a subclass of error.
DataError	Triggered when an error occurs when there is data processing, for example: except for 0 errors, data over-range, and so on. Must be a subclass of Databaseerror.
Operationalerror	Refers to an error that occurs when a database is manipulated by a non-user control. For example, an operation database is an error that occurs when a connection is accidentally disconnected, the database name is not found, a transaction fails, a memory allocation error, and so on. Must be a subclass of Databaseerror.
Integrityerror	Integrity-related errors, such as foreign key check failures, and so on. Must be a databaseerror subclass.
Internamerror	An internal error in the database, such as a cursor failure, a transaction synchronization failure, and so on. Must be a databaseerror subclass.
Programmingerror	program errors, such as data table (table) not found or existing, SQL statement syntax error, parameter number error, and so on. Must be a subclass of Databaseerror.
notsupportederror	does not support errors, such as using functions or APIs that are not supported by the database. For example, the rollback () function is used on a connection object, but the database does not support transactions or the transaction is closed. Must be a subclass of Databaseerror.