"Go" python operation MySQL Database

Python operation MySQL Database

The Python standard database interface for Python Db-api,python DB-API provides developers with a database application programming interface.

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

You can access the Python database interface and API to view a detailed list of supported databases.

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.

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.

How do I 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 do not have the MySQLdb module installed:

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

To install MYSQLDB, visit Http://sourceforge.net/projects/mysql-python, (Linux platform can access: https://pypi.python.org/pypi/ Mysql-python) From here you can choose the installation package for your platform, which is divided into precompiled binaries and source code installation packages.

If you choose a binary file release, the installation process will complete with the basic installation instructions. If you are installing from source code, you will need to switch to the top-level directory of the MYSQLDB release 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: Make sure that you have root privileges to install the above modules.

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.
• If you are unfamiliar with SQL statements, you can access our basic SQL tutorials

Instance:

The following example links the MySQL TestDB database:

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

import MySQLdb

# Open database connection
db = MySQLdb.connect ("localhost", "testuser", "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 ()

Execute the above script output as follows:

Database version:5.0.45

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 / python
#-*-coding: UTF-8-*-

import MySQLdb

# Open database connection
db = MySQLdb.connect ("localhost", "testuser", "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 ()

Database Insert Operations

The following instance inserts records into table EMPLOYEE using the Execute SQL INSERT statement:

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

import MySQLdb

# Open database connection
db = MySQLdb.connect ("localhost", "testuser", "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", "testuser", "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 ()

Instance:

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 / python
#-*-coding: UTF-8-*-

import MySQLdb

# Open database connection
db = MySQLdb.connect ("localhost", "testuser", "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 all records list
    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 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 / python
#-*-coding: UTF-8-*-

import MySQLdb

# Open database connection
db = MySQLdb.connect ("localhost", "testuser", "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 ()

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 / python
#-*-coding: UTF-8-*-

import MySQLdb

# Open database connection
db = MySQLdb.connect ("localhost", "testuser", "test123", "TESTDB")

# Use cursor () method to obtain the operation cursor
cursor = db.cursor ()

# SQL delete statement
sql = "DELETE FROM EMPLOYEE WHERE AGE>‘% d ’”% (20)
try:
    # Execute SQL statement
    cursor.execute (sql)
    # Commit changes
    db.commit ()
except:
    # Rollback on error
    db.rollback ()

# Close the 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 ’”% (20)
try:
    # Execute SQL statement
    cursor.execute (sql)
    # Submit to the database
    db.commit ()
except:
    # Rollback on error
    db.rollback () 

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.

"Go" python operation MySQL Database