Python's correct posture to connect to MySQL database

The Python database interface supports a very great number of databases, and you can choose the database that works for your project:

Gadfly

MSQL

Mysql

PostgreSQL

Microsoft SQL Server 2000

Informix

Interbase

Oracle

Sybase

Different databases you need to download different DB API modules, such as 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. It defines a set of required objects and database access methods to provide a consistent access interface for a wide variety of underlying database systems and a wide variety of database interface programs.

Python's Db-api, which implements the interface for most databases, uses it to connect to each database and manipulate the databases in the same way.

Python DB-API Usage Process:

Introduce API modules.

Gets the connection to the database.

Executes SQL statements and stored procedures.

Close the database connection.

First, what is MYSQLDB?

MYSQLDB is the interface for the Python link MySQL database, which implements the Python database API specification V2.0, built on the MySQL C API.

Second, how to install MySQLdb?

In order to write MySQL scripts with Db-api, you must make sure that MySQL is already installed. Copy the following code and execute:

#! / usr / bin / python
#-*-coding: UTF-8-*-
 
import MySQLdb
If the output 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 <module>
  import MySQLdb
ImportError: No module named MySQLdb
If you choose a binary distribution, the basic installation instructions for the installation process are complete. If you are installing from source, you need to change to the top-level directory of the MySQLdb distribution and type 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: Please make sure you have root permissions to install the above modules.

Database connection

Before connecting to the database, please confirm the following:

You have created the database TESTDB.

In the TESTDB database you have created the table EMPLOYEE

EMPLOYEE table fields 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 use the root user name and password directly. For the MySQL database user authorization, use the Grant command.

The Python MySQLdb module has been installed on your machine.

Example:

The following example links to Mysql's TESTDB database:

#! / usr / bin / python
#-*-coding: UTF-8-*-
 
import MySQLdb
 
# Open database connection
db = MySQLdb.connect ("localhost", "codecloud", "test123", "TESTDB")
 
# Use cursor () method to obtain the operation cursor
cursor = db.cursor ()
 
# Use the execute method to execute SQL statements
cursor.execute ("SELECT VERSION ()")
 
# Use fetchone () method to get a database.
data = cursor.fetchone ()
 
print "Database version:% s"% data
 
# Close the database connection
db.close ()
The output of executing the above script is as follows:

Database version: 5.0.45
Fourth, create a database table

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

#! / usr / bin / python
#-*-coding: UTF-8-*-
 
import MySQLdb
 
# Open database connection
db = MySQLdb.connect ("localhost", "codecloud", "test123", "TESTDB")
 
# Use cursor () method to obtain the operation cursor
cursor = db.cursor ()
 
# If the data table already exists, delete the table using the execute () method.
cursor.execute ("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.execute (sql)
 
# Close the database connection
db.close ()
Five, the database insert operation

The following example uses a SQL INSERT statement to insert records into the table EMPLOYEE:

#! / usr / bin / python
#-*-coding: UTF-8-*-
 
import MySQLdb
 
# Open database connection
db = MySQLdb.connect ("localhost", "codecloud", "test123", "TESTDB")
 
# Use cursor () method to obtain the operation cursor
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.execute (sql)
  # Submit to the database for execution
  db.commit ()
except:
  # Rollback in case there is any error
  db.rollback ()
 
# Close the database connection
db.close ()
The above example can also be written as follows:

#! / usr / bin / python
#-*-coding: UTF-8-*-
 
import MySQLdb
 
# Open database connection
db = MySQLdb.connect ("localhost", "codecloud", "test123", "TESTDB")
 
# Use cursor () method to obtain the operation cursor
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:
  # Execute the SQL statement
  cursor.execute (sql)
  # Submit to the database for execution
  db.commit ()
except:
  # Rollback on error
  db.rollback ()
 
# Close the database connection
db.close ()
Example:

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))
........................
Six, database query operation

Python query Mysql uses fetchone () method to get a single piece of data, and fetchall () method to get multiple pieces of 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 by the execute () method.

Example:

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

#! / usr / bin / python
#-*-coding: UTF-8-*-
 
import MySQLdb
 
# Open database connection
db = MySQLdb.connect ("localhost", "codecloud", "test123", "TESTDB")
 
# Use cursor () method to obtain the operation cursor
cursor = db.cursor ()
 
# SQL query
sql = "SELECT * FROM EMPLOYEE \
    WHERE INCOME> '% d' "% (1000)
try:
  # Execute SQL statement
  cursor.execute (sql)
  # Get a 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 results
   print "fname =% s, lname =% s, age =% d, sex =% s, income =% d"% \
       (fname, lname, age, sex, income)
except:
  print "Error: unable to fecth data"
 
# Close the database connection
db.close ()
The above script execution results are as follows:

fname = Mac, lname = Mohan, age = 20, sex = M, income = 2000
Seven, database update operation

The update operation is used to update the data in the data table. The following example changes all the SEX fields in the TESTDB table to 'M', and the AGE field is incremented by 1:

#! / usr / bin / python
#-*-coding: UTF-8-*-
 
import MySQLdb
 
# Open database connection
db = MySQLdb.connect ("localhost", "codecloud", "test123", "TESTDB")
 
# Use cursor () method to obtain the operation cursor
cursor = db.cursor ()
 
# SQL update statement
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
             WHERE SEX = '% c' "% ('M')
try:
  # Execute SQL statement
  cursor.execute (sql)
  # Submit to the database for execution
  db.commit ()
except:
  # Rollback on error
  db.rollback ()
 
# Close the database connection
db.close ()
Execute transaction

The transaction mechanism can ensure data consistency.

A transaction should have 4 attributes: atomicity, consistency, isolation, and durability. These four attributes are often called ACID characteristics.

Atomicity A transaction is an indivisible unit of work, and the operations included in the transaction are either done or not done.

Consistency. A transaction must change the database from one consistency state to another. Consistency and atomicity are closely related.

Isolation. The execution of one transaction cannot be interrupted by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrently executed transactions must not interfere with each other.

Durability. Persistence is also called permanence, which means that once a transaction is committed, its changes to the data in the database should be permanent. No further operations or faults should affect it.

Python DB API 2.0 transactions provide two methods for commit or rollback.

Example:

# SQL delete record statement
sql = "DELETE FROM EMPLOYEE WHERE AGE> '% d'"% (20)
try:
  # Execute SQL statement
  cursor.execute (sql)
  # Submit to the database
  db.commit ()
except:
  # Rollback on error
  db.rollback ()
For databases that support transactions, in Python database programming, when a cursor is created, an invisible database transaction is automatically started.

The commit () method updates all cursor operations, and the rollback () method rolls back all operations of the current cursor. Each method starts a new transaction.

Mistake Reason

The DB API defines some errors and exceptions for database operations. The following table lists these errors and exceptions:

The above is the entire content of this article, I hope it will be helpful to everyone's learning