Python Operation MySQL Database

Read Catalogue

• Simple Introduction
• Environment preparation
• instance operations [Create, insert]
  

1. Link MySQL's TestDB database and get the properties of a field
2. Create a Vforbox data table
3. Execute SQL INSERT statement to insert records into table Vforbox
4. Using variables to pass parameters to SQL statements

• instance operations [Query, UPDATE]

1. Querying all data in the Vforbox table that the Name field equals Vforbox
2. Modify all the SEX fields in the Vforbox table to ' M ', and the Age field increments by 1

• DB-API Error Handling

This essay is a Python operation MySQL database, below I will take my own understanding of the notes, as a reference, hoping to help the reader

In order for the reader to understand the author's essay, I will deepen the color

Simple Introduction

• Python Standard Database interface is "Python Db-api", while "Python Db-api" provides developers with a database application programming interface
• Python Database interface supports very many databases here are five examples of

1. Mysql
2. MSQL
3. PostgreSQL
4. Microsoft SQL Server
5. Oracle

• If you want to know more, you can visit the official website Python database interface and API to view a detailed list of supported databases
• Downloading different DB API modules for different databases, such as needing access to MySQL database and Oracle database, requires downloading MySQL and Oracle database modules
• Python's Db-api, which implements the interface for most databases, uses it to connect to each database and to operate the databases in the same way.
• Python Db-api Operation Flow

1. Import DB-API Module
2. Get a connection to a database
3. Execute SQL statements and stored procedures
4. To close a database connection

This essay is a python operation MySQL database, so you need to install the MYSQLDB module in the operating system to support the operation of the MySQL database on Python

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

Back to TopEnvironment Preparation

• First, in order to use Python's mysqldb to operate the pre-MySQL conditions

† You must ensure that the MySQL database is already installed on the operating system (Linux CentOS)

[[email protected] ~] # rpm -qa | grep mysql // Query whether Mysql is installed in the system
[[email protected] ~] # yum -y install mysql mysql-server // Install Mysql
[[email protected] ~] # service mysqld start // Start Mysql
[[email protected] ~] # chkconfig mysqld on // Set Mysql to boot
† Create database testdb on MySQL database

mysql> show databases; // Show all databases
+ -------------------- +
Database |
+ -------------------- +
| information_schema |
| mysql |
| test |
+ -------------------- +
3 rows in set (0.00 sec)
mysql> create database testdb; // Create testdb database
Query OK, 1 row affected (0.00 sec)
† Create data table testtab in testdb database

† testdb table fields have id, name, age, sex

mysql> use testdb; // Enter the testdb database
Database changed
mysql> create table testtab (// Create testtab data table
    -> id int (4),
    -> name varchar (255),
    -> age int (3),
    -> sex varchar (1)
    ->);
Query OK, 0 rows affected (0.01 sec)
mysql> desc testtab; // Show data table properties
+ ------- + -------------- + ------ + ----- + --------- + --- ---- +
Field | Type | Null | Key | Default | Extra |
+ ------- + -------------- + ------ + ----- + --------- + --- ---- +
| id | int (4) | YES | | NULL | |
| name | varchar (255) | YES | | NULL | |
age | int (3) | YES | | NULL | |
| sex | varchar (1) | YES | | NULL | |
+ ------- + -------------- + ------ + ----- + --------- + --- ---- +
4 rows in set (0.00 sec)
† The user name authorized to connect to the database testdb is "root", the password is "test123", all permissions

mysql> grant all privileges on testdb. * to ‘root’ @ ‘%’ identified by ‘test123’ with grant option;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
† Can connect to MySQL to ensure that the Python MySQLdb module is installed in the system

† If the output of importing MySQLdb is shown below, it means that the MySQLdb module is not installed

[[email protected] ~] # python
Python 2.6.6 (r266: 84292, Nov 22 2013, 12:16:22)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: No module named MySQLdb
† Install MySQLdb, click to enter from here to choose the installation package suitable for your platform, divided into pre-compiled binary files and source code installation packages

† Here we use the source package to install, first install the dependent packages, so that there will be many errors when we wait to perform the installation ...

[[email protected] ~] # yum install -y python-devel mysql-devel zlib-devel openssl-devel python-setuptools
† Download MySQL-Python, unzip, install

[[email protected] ~] # cd / usr / local / src /
[[email protected] src] # wget http://download.sourceforge.net/sourceforge/mysql-python/MySQL-python-1.2.3.tar.gz
[[email protected] src] # tar zxf MySQL-python-1.2.3.tar.gz
[[email protected] src] # cd MySQL-python-1.2.3
[[email protected] MySQL-python-1.2.3] # python setup.py build
[[email protected] MySQL-python-1.2.3] # python setup.py install
† If there is no error in the output of the imported MySQLdb, it means that the installation of the MySQLdb module was successful

[[email protected] ~] # python
Python 2.6.6 (r266: 84292, Nov 22 2013, 12:16:22)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>>
Back to top Example operations [create, insert]
Link MySQL's testdb database and get the properties of a field
#! / usr / bin / python
#-*-coding: UTF-8-*-

#Import MySQLdb module
import MySQLdb

#Open database connection
#host host address, user user, passwd password, db database name, port database port
conn = MySQLdb.connect (host = ‘192.168.1.187’, user = ‘root’, passwd = ‘test123’, db = ‘testdb’, port = 3306)

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

# Use the execute method to execute SQL statements
cur.execute ("describe testtab")

# Use the fetchone () method to get the properties of a field
data = cur.fetchone ()

#Print data this data
print data

# Close the database connection
conn.close ()
† Output from executing the above script

(‘Id’, ‘int (4)’, ‘YES’, ‘‘, None, ‘‘)
Create a VFORBOX data table
#! / usr / bin / python
#-*-coding: UTF-8-*-

#Import MySQLdb module
import MySQLdb

#Open database connection
#host host address, user user, passwd password, db database name, port database port
conn = MySQLdb.connect (host = ‘192.168.1.187’, user = ‘root’, passwd = ‘test123’, db = ‘testdb’, port = 3306)

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

#Create database
sql = "" "CREATE TABLE VFORBOX (
         NAME CHAR (20) NOT NULL,
         AGE INT,
         SEX CHAR (1)) "" "

# Use the execute method to execute SQL statements
cur.execute (sql)

# Close the database connection
conn.close ()
† Output from executing the above script

mysql> use testdb;
Database changed
mysql> show tables;
+ ------------------ +
| Tables_in_testdb |
+ ------------------ +
| VFORBOX |
| testtab |
+ ------------------ +
2 rows in set (0.00 sec)
mysql> describe VFORBOX;
+ ------- + ---------- + ------ + ----- + --------- + ------- +
Field | Type | Null | Key | Default | Extra |
+ ------- + ---------- + ------ + ----- + --------- + ------- +
| NAME | char (20) | NO | | NULL | |
| AGE | int (11) | YES | | NULL | |
| SEX | char (1) | YES | | NULL | |
+ ------- + ---------- + ------ + ----- + --------- + ------- +
3 rows in set (0.00 sec)
Execute SQL INSERT statement to insert records into table VFORBOX
#! / usr / bin / python
#-*-coding: UTF-8-*-

#Import MySQLdb module
import MySQLdb

#Open database connection
#host host address, user user, passwd password, db database name, port database port
conn = MySQLdb.connect (host = ‘192.168.1.187’, user = ‘root’, passwd = ‘test123’, db = ‘testdb’, port = 3306)

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

# SQL insert statement
sql = "" "INSERT INTO VFORBOX (
         NAME, AGE, SEX)
VALUES (‘Vforbox’, 18, ‘M’) "" "
try:

# Execute the SQL statement
   cur.execute (sql)

# Submit to the database for execution
   conn.commit ()

except:

# Rollback on error
   conn.rollback ()

# Close the database connection
conn.close ()
† Output from executing the above script

mysql> select * from VFORBOX;
+ --------- + ------ + ------ +
| NAME | AGE | SEX |
+ --------- + ------ + ------ +
| Vforbox | 18 | M |
+ --------- + ------ + ------ +
1 row in set (0.00 sec)
Passing parameters to SQL statements using variables (slightly modified from above)
#! / usr / bin / python
#-*-coding: UTF-8-*-

#Import MySQLdb module
import MySQLdb

#Open database connection
#host host address, user user, passwd password, db database name, port database port
conn = MySQLdb.connect (host = ‘192.168.1.187’, user = ‘root’, passwd = ‘test123’, db = ‘testdb’, port = 3306)

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

# SQL insert statement
sql = "INSERT INTO VFORBOX (NAME, AGE, SEX) VALUES (‘% s ’,‘% d ’,‘% c ’)"% (‘vforbox’, 18, ‘M’)
try:

# Execute the SQL statement
   cur.execute (sql)

# Submit to the database for execution
   conn.commit ()

except:

# Rollback on error
   conn.rollback ()

# Close the database connection
conn.close ()
† Output from executing the above script

mysql> select * from VFORBOX;
+ --------- + ------ + ------ +
| NAME | AGE | SEX |
+ --------- + ------ + ------ +
| Vforbox | 18 | M |
| Vforbox | 18 | M |
+ --------- + ------ + ------ +
1 row in set (0.00 sec)
Back to top Example operations [Query, Update]
Python query Mysql use fetchone () method to get a single piece of data, and fetchall () method to get multiple pieces of data
fetchone (): method to get 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
Query all data in the VFORBOX table whose name field is equal to vforbox
#! / usr / bin / python
#-*-coding: UTF-8-*-

#Import MySQLdb module
import MySQLdb

#Open database connection
#host host address, user user, passwd password, db database name, port database port
conn = MySQLdb.connect (host = ‘192.168.1.187’, user = ‘root’, passwd = ‘test123’, db = ‘testdb’, port = 3306)

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

#SQLQuery Statement
sql = "SELECT * FROM VFORBOX WHERE NAME =‘ vforbox ’”

try:
    #Execute SQL statement
    cur.execute (sql)
#Get all records list
    res = cur.fetchall ()
    for s in res:
      name = s [0]
      age = s [1]
      sex = s [2]
#Print result output
    print "name =% s, age =% d, sex =% s"% (name, age, sex)
except:
    print u "Error: Cannot Stroke Data"

# Close the database connection
conn.close ()
† Output from executing the above script

name = vforbox, age = 20, sex = M
The update operation is used to update the data in the data table, and all the SEX fields in the VFORBOX table are changed to 'M', and the AGE field is incremented
#! / usr / bin / python
#-*-coding: UTF-8-*-

#Import MySQLdb module
import MySQLdb

#Open database connection
#host host address, user user, passwd password, db database name, port database port
conn = MySQLdb.connect (host = ‘192.168.1.187’, user = ‘root’, passwd = ‘test123’, db = ‘testdb’, port = 3306)

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

#SQLUpdate Statement
sql = "UPDATE VFORBOX SET AGE = AGE + 1 WHERE SEX =‘ M ’”
try:
    #Execute SQL statement
    cur.execute (sql)
    #Submit to the database for execution
    conn = commit ()
except:
    conn.rollback ()
# Close the database connection
conn.close ()
† Output from executing the above script

mysql> select * from VFORBOX;
+ --------- + ------ + ------ +
| NAME | AGE | SEX |
+ --------- + ------ + ------ +
| vforbox | 21 | M |
+ --------- + ------ + ------ +
1 rows in set (0.00 sec)
Back to top DB-API error handling
Exception description
Warning Triggered when there is a serious warning, such as the inserted data is truncated, etc., must be a subclass of StandardError
All error classes except Error, must be subclasses of StandardError
InterfaceError is triggered when there is an error in the database interface module itself (not a database error), which must be a subclass of Error
DatabaseError is triggered when database-related errors occur, and must be a subclass of Error
DataError Triggered when an error occurs during data processing, such as: division by zero error, data out of range, etc., must be a subclass of DatabaseError
OperationalError
Refers to errors that are not controlled by the user, but occur when operating the database

For example: the connection was unexpectedly disconnected, the database name was not found, the transaction failed, the memory allocation error, etc. An error occurred while operating the database, and must be a subclass of DatabaseError

IntegrityError Integrity related errors, such as foreign key check failure, etc., must be a DatabaseError subclass
InternalError Database internal errors, such as cursor failure, transaction synchronization failure, etc. Must be a DatabaseError subclass
ProgrammingError Program errors, such as data table (table) not found or existing, SQL statement syntax error, parameter number error, etc., must be a subclass of DatabaseError
NotSupportedError
Unsupported error refers to the use of functions or APIs that are not supported by the database

For example, use the .rollback () function on the connection object, but the database does not support transactions or the transaction is closed, it must be a subclass of DatabaseError

Python MySQL Database