Use Python to operate MySQL Data and pythonmysql data

Use Python to operate MySQL Data and pythonmysql data

This article introduces Python3 using PyMySQL to connect to the database and implement simple addition, deletion, modification, and query.

What is PyMySQL?

PyMySQL is a database used to connect to the MySQL server in Python3.x, and mysqldb is used in Python2.x.

Install PyMySQL

Before using PyMySQL, make sure that PyMySQL has been installed.

PyMySQL: https://github.com/pymysql/pymysql.

If not, run the following command to install the latest version of PyMySQL:

$ pip install PyMySQL

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

1. Use the git command to download the installation package (you can also manually download it ):

$ git clone https://github.com/PyMySQL/PyMySQL$ cd PyMySQL$ python3 setup.py install

2. If you need to develop a version number, you can use the curl command to install it:

$ # X. X is the version of PyMySQL $ 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 the root permission to install the above modules.

Database Connection

Before connecting to the database, confirm the following:

• You have created a database named TESTDB.
• In the TESTDB database, you have created the table "EMPLOYEE ".
• The fields in the EMPLOYEE table are FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
• The user name used to connect to the database TESTDB is "testuser" and the password is "test123". You can set it yourself or directly use the root user name and password. For Mysql database user authorization, use the Grant command
• You have installed the PyMySQL module on your host.

Instance:

The following example links to the TESTDB database of Mysql:

#! /Usr/bin/python3 _ author _ = 'mayi' import pymysql # Open the database connection db = pymysql. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to create a cursor object cursorcursor = db. cursor () # Use the execute () method to execute the SQL query cursor.exe cute ("SELECT VERSION ()") # Use the fetchone () method to obtain a single piece of data. data = cursor. fetchone () print ("Database version: % s" % data) # disable Database connection to db. close ()

Create a database table

If the database connection exists, we can use the execute () method to create a table for the database, as shown below:

#! /Usr/bin/python3 _ author _ = 'mayi' import pymysql # Open the database connection db = pymysql. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to create a cursor object cursorcursor = db. cursor () # Use the execute () method to execute SQL. Delete cursor.exe cute ("DROP TABLE IF EXISTS EMPLOYEE") IF the TABLE EXISTS ") # use a pre-processing statement to CREATE a TABLE SQL = "CREATE TABLE EMPLOYEE (FIRST_NAME CHAR (20) NOT NULL, LAST_NAME CHAR (20), AGE INT, SEX CHAR (1 ), income float) "cursor.exe cute (SQL) # disable database connection to db. close ()

Database insert operation

The following example uses the SQL Insert statement to Insert records to the table "EMPLOYEE:

#! /Usr/bin/python3 _ author _ = 'mayi' import 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 ()

The preceding example can also be written as follows:

#! /Usr/bin/python3 _ author _ = 'mayi' import 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 ('% s',' % s ', '% d',' % C', '% D') "% \ ('mac', 'mohan', 20, 'M', 2000) try: # Run the SQL statement cursor.exe cute (SQL) # Run the SQL statement db. commit () commit T: # roll back the db when an error occurs. rollback () # disable database connection to db. close ()

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 result rows.
• Rowcount: This is a read-only attribute and returns the number of rows affected by the execution of the execute () method.

Instance:

Query all the data with the salary (salary) Field greater than 1000 in the EMPLOYEE table:

#! /Usr/bin/python3 _ author _ = 'mayi' import pymysql # Open the database connection db = pymysql. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation cursor = db. cursor () # SQL query statement SQL = "SELECT * from employee \ where income> '% d'" % (1000) try: # Run the SQL statement cursor.exe cute (SQL) # retrieve the list of all 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) Doesn't: print ("Error: unable to fecth data") # disable database connection to db. close ()

Database update operations

The update operation is used to update data in the data table. 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 the database connection db = pymysql. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation cursor = db. cursor () # SQL update statement SQL = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '% C'" % ('M') try: # Run the SQL statement cursor.exe cute (SQL) # submit it to the database to execute the database. commit () commit T: # roll back the db when an error occurs. rollback () # disable database connection to db. close ()

Delete operation

The delete operation is used to delete data in a data table. The following example shows how to delete all data whose AGE is greater than 20 in the data table EMPLOYEE:

#! /Usr/bin/python3 _ author _ = 'mayi' import pymysql # Open the database connection db = pymysql. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation cursor = db. cursor () # SQL delete statement SQL = "delete from employee where age> '% d'" % (20) try: # Run the SQL statement cursor.exe cute (SQL) # submit the modified db. commit () commit T: # roll back the db when an error occurs. rollback () # Close the database connection. close ()

Execute transactions

The transaction mechanism ensures data consistency.

Transactions should have four attributes: atomicity, consistency, isolation, and persistence. These four attributes are generally called ACID properties.

• Atomicity ). A transaction is an inseparable unit of work. All operations involved in a transaction are either done or not done.
• Consistency ). Transactions must change the database from one consistent state to another. Consistency is closely related to atomicity.
• Isolation ). The execution of a transaction cannot be disturbed by other transactions. That is to say, the operations and data used within a transaction are isolated from other concurrent transactions, and the transactions executed concurrently cannot interfere with each other.
• Durability ). Permanence refers to a transaction that changes the data in the database once committed. Other subsequent operations or faults should not have any impact on them.

Instance

# SQL delete record statement SQL = "delete from employee where age> '% d'" % (20) try: # Execute SQL statement cursor.exe cute (SQL) # submit the database. commit () commit T: # roll back the db when an error occurs. rollback ()

For databases that support transactions, an invisible database transaction is automatically started when the cursor is set up in Python database programming.

All update operations of the commit () method cursor, And the rollback () method roll back all operations of the current cursor. Each method starts a new transaction.

Error Handling

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.
InternamError	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.
NotSupportedError	An error is not supported. It indicates that a function or API not supported by the database is used. For example, you can use the rollback () function on the connection object. However, the database does not support transactions or the transaction has been closed. Must be a subclass of DatabaseError.

The above is all the content of this article. I hope this article will help you in your study or work. I also hope to provide more support to the customer's home!