If you are not familiar with SQL statements, visit our basic SQL tutorial instance:The following example links to the TESTDB database of Mysql:
#! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation cursor = db. cursor () # Use execute to execute SQL sentence cursor.exe cute ("SELECT VERSION ()") # Use the fetchone () method to obtain a database. Data = cursor. fetchone () print "Database version: % s" % data # close Database connection db. close ()
The output result is as follows:Database version : 5.0.45
Create a database tableIf the database connection exists, we can use the execute () method to create a table for the database, as shown below:
#! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation cursor = db. cursor () # If the data table already exists, use the execute () method to delete the table. Cursor.exe cute ("drop table if exists employee") # CREATE a data table SQL statement 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 operationThe following example uses the SQL INSERT statement to INSERT records to the table "EMPLOYEE:
#! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. 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 () handle T: # Rollback in case there is any error db. rollback () # disable database connection to db. close ()
The preceding example can also be written as follows:
#! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. 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) # 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 ()
Instance:The following code uses variables to pass parameters to SQL statements:
..................................user_id = "test123"password = "password"con.execute('insert into Login values("%s", "%s")' % \ (user_id, password))..................................
Database query operationsFor 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. Instance:
Query all the data with the salary (salary) Field greater than 1000 in the EMPLOYEE table:
#! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. 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) failed T: print" Error: unable to fecth data "# disable database connection to db. close ()
The execution result of the above script is as follows:
fname=Mac, lname=Mohan, age=20, sex=M, income=2000
Database update operationsThe 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/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. 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 ()
Execute transactionsThe 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.
The transaction of Python db api 2.0 provides two methods: commit or rollback.
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 HandlingDb 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. |
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. |