Python3 MySQL Database connection

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.

Pymysql follows the Python database API v2.0 specification and includes the Pure-python MySQL client library.

Pymysql Installation

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

Pymysql: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$ # Now you can delete the pymysql* directory

　　

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

The "Importerror:no module named setuptools" error message may appear during installation, meaning you do not have Setuptools installed and you can access https://pypi.python.org/pypi/ Setuptools find the installation method for each system. Linux System Installation Example: $ wget https://bootstrap.pypa.io/ez_setup.py$ python3 ez_setup.py

　　

Database connection

Before you connect to a database, verify the following:

• You have created the database TESTDB.
• In the TestDB database, you have created table EMPLOYEE
• The Employee table fields are 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 Python mysqldb module is already installed on your machine.

Instance:

The following example links the Mysql TESTDB database

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

　　

Execute the above script output as follows:

Database Version:5.5.20-log

　　

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 Import pymysql # Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", "TESTDB") # using the cursor ( ) method to create a cursor object cursorcursor = Db.cursor () # executes SQL using the Execute () method and deletes cursor.execute if the table exists ("drop table if EXISTS EMPLOYEE") # CREATE table with preprocessing statements sql = "" CREATE Table EMPLOYEE (         first_name  char () not NULL,         last_name  Char         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 Import pymysql # Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", "TESTDB") # using the cursor ( ) method gets the cursor cursor = db.cursor () # SQL INSERT statement sql = "" INSERT into EMPLOYEE (first_name,         last_name, age, SEX, INCOME)         VA Lues (' Mac ', ' Mohan ', ' M ', ' + ') ' "" Try:   # Execute SQL statement   cursor.execute (SQL)   # Commit to Database Execution   Db.commit () Except:   # Rollback If an error occurs   Db.rollback () # Close the database connection Db.close ()

　　

The above example can also be written as follows:

#!/usr/bin/python3 Import pymysql # Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", "TESTDB") # using the cursor ( ) method gets the action cursor = db.cursor () # SQL INSERT statement sql = "INSERT into EMPLOYEE (first_name,        last_name, age, SEX, INCOME)        VALU ES ('%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 ()

　　

The following code uses variables to pass parameters to the SQL statement:

user_id = "test123" password = "password" con.execute (' insert into Login values ('%s ', '%s ') '%              (user_id, password))

　　

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 (): receives all the returned result rows.
• ROWCOUNT: This is a read-only property and returns the number of rows affected after the Execute () method is executed.

Instance:

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

#!/usr/bin/python3 Import pymysql # Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", "TESTDB") # using the cursor ( ) method gets the cursor cursor = db.cursor () # SQL query Statement sql = "SELECT * from EMPLOYEE        WHERE INCOME > '%d '"% (s) try:   # Execute sq L Statement   cursor.execute (SQL)   # gets all the list of records   results = Cursor.fetchall () for   row in results:      fname = row[0]      lname = row[1] Age      = row[2]      sex = row[3]      income = row[4]       # Print the result print      ("fname=%s,lname=%s , age=%d,sex=%s,income=%d "%              (fname, lname, age, sex, income)) except:   print (" Error:unable to fetch Data ") # Close Database Connection Db.close ()

　　

The results of the above script execution are as follows:

Fname=mac, Lname=mohan, age=20, Sex=m, income=2000

　　

Database update operations

The update action is used to update the 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 Import pymysql # Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", "TESTDB") # using the cursor ( ) method gets the cursor cursor = db.cursor () # SQL UPDATE statement sql = "Update EMPLOYEE SET age = age + 1 WHERE SEX = '%c '"% (' M ') Try:   # Execute sq L Statement   cursor.execute (SQL)   # commits 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 Import pymysql # Open database Connection db = Pymysql.connect ("localhost", "testuser", "test123", "TESTDB") # using the cursor ( ) method gets the cursor cursor = db.cursor () # SQL DELETE statement sql = "Delete from EMPLOYEE WHERE age > '%d '" "%" try:   # Execute SQL statement   cur Sor.execute (SQL)   # Commit modification   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.

The transaction for Python DB API 2.0 provides two methods of commit or rollback.

Instance

# SQL Delete Record statement sql = "Delete from EMPLOYEE WHERE age > '%d '" "%" try:   # Execute SQL statement   cursor.execute (SQL)   # submit to Database C3/>db.commit () except:   # 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

Some errors and exceptions for database operations are defined in the DB API, and the following table lists these errors and exceptions:

Exception Description 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. Internalerror 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 Errors are not supported, such as functions or APIs that are not supported by the database. For example, the. Rollback () function is used on the connection object, but the database does not support transactions or the transaction is closed. Must be a subclass of Databaseerror.

Python3 MySQL Database connection