Correct posture for connecting Python to mysql database

This article describes how to use Python to connect to the mysql database, if you are interested, you can refer to the Python database interface that supports a large number of databases. you can choose a database suitable for your project:

GadFly

MSQL

MySQL

PostgreSQL

Microsoft SQL Server 2000

Informix

Interbase

Oracle

Sybase

For different databases, you need to download different database API modules. for example, if you need to access the Oracle database and Mysql data, you need to download the Oracle and MySQL database modules.

DB-API is a specification that defines a series of required object and database access methods to provide consistent access interfaces for a variety of underlying database systems and a variety of database interface programs.

Python DB-API, for the majority of the database implementation interface, using it to connect to the database, you can use the same way to operate the database.

Python DB-API use process:

Introduce the API module.

Obtain the connection to the database.

Execute SQL statements and stored procedures.

Close the database connection.

1. what is MySQLdb?

MySQLdb is an interface used to connect Python to the Mysql database. it implements the Python database API specification V2.0 and is created based on MySQL c api.

2. how to install MySQLdb?

To write a MySQL script with a DB-API, make sure that MySQL is installed. Copy and execute the following code:

#!/usr/bin/python# -*- coding: UTF-8 -*- import MySQLdb

If the output result after execution is as follows, it means that you have not installed the MySQLdb module:

Traceback (most recent call last): File "test.py", line 3, in 
 
    import MySQLdbImportError: No module named MySQLdb
 

If you select the binary file release version, the installation process will be completed with the installation prompt. If you install the SDK from the source code, you need to switch to the top-level Directory of the MySQLdb release, and enter the following command:

$ gunzip MySQL-python-1.2.2.tar.gz$ tar -xvf MySQL-python-1.2.2.tar$ cd MySQL-python-1.2.2$ python setup.py build$ python setup.py install

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

III. database connection

Before connecting to the database, confirm the following:

You have created the database 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.

Python MySQLdb module has been installed on your host.

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", "codecloud", "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

4. 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/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. connect ("localhost", "codecloud", "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 ()

V. database insertion

The 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", "codecloud", "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", "codecloud", "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))..................................

VI. 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 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", "codecloud", "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

VII. 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/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. connect ("localhost", "codecloud", "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 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.

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 handling

Db api defines database operation errors and exceptions. The following table lists these errors and exceptions:

The above is all the content of this article, hoping to help you learn.

For more articles about the correct posture of connecting Python to the mysql database, please follow the PHP Chinese network!