python operations MySQL database
Python standard database interface for the Python DB-API, Python DB-API provides a database application programming interface for developers.
Python database interface supports very large databases, you can choose your database project:
- GadFly
- mSQL
- MySQL
- PostgreSQL
- Microsoft SQL Server 2000
- Informix
- Interbase
- Oracle
- Sybase
You can access the Python database interface and API See detailed list of supported databases.
Different database you will need to download a different DB API module, for example, you need to access Oracle databases and Mysql data, you need to download the Oracle and MySQL database module.
DB-API is a specification that defines a set of objects and the database must access mode, in order to provide a consistent interface to access a wide variety of underlying database system and a variety of database interface program.
Python's DB-API, for most of the database implements the interface, use it after connecting the database, you can use the same manner each database.
Python DB-API using the process:
- The introduction of API module.
- Get connected to the database.
- Execute SQL statements and stored procedures.
- Close the database connection.
What is MySQLdb?
MySQLdb is an interface for Python Mysql database link, which implements the Python Database API specification V2.0, based on the establishment of the MySQL C API.
How to install MySQLdb?
To write MySQL scripts with DB-API, you must ensure that you have installed MySQL. Copy the following code, and execute it:
#!/usr/bin/python # -*- coding: UTF-8 -*- import MySQLdb
If the output is as follows after the execution, which means you do not have MySQLdb module is installed:
Traceback (most recent call last): File "test.py", line 3, in <module> import MySQLdb ImportError: No module named MySQLdb
Install MySQLdb, visit http://sourceforge.net/projects/mysql-python , (the Linux platform can be accessed: https://pypi.python.org/pypi/MySQL-python ) From here choose for your platform installation package, pre-compiled into binaries and source code package.
If you select binary release version, the installation process prompts to complete the basic installation. If installing from source code, you need to switch to MySQLdb release top-level directory, 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 you have root privileges to install the modules described above.
Database Connectivity
Before connecting to the database, make sure the following items:
- You have created a database TESTDB.
- In TESTDB database you have created the table EMPLOYEE
- EMPLOYEE table field FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
- Users connect to the database using TESTDB named "testuser", password "test123", you can set your own directly or root user name and password, Mysql database user license, please use the Grant command.
- On your machine already installed Python MySQLdb module.
- If you are not familiar with the sql statement, you can visit our SQL tutorial basis
Example:
The following examples of TESTDB Mysql database link:
#!/usr/bin/python # -*- coding: UTF-8 -*- import MySQLdb # 打开数据库连接 db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # 使用execute方法执行SQL语句 cursor.execute("SELECT VERSION()") # 使用 fetchone() 方法获取一条数据库。 data = cursor.fetchone() print "Database version : %s " % data # 关闭数据库连接 db.close()
Implementation of the above script outputs the results are as follows:
Database version : 5.0.45
Create a database table
If the database connection exists, we can use the execute () method to create tables for the database, create a table EMPLOYEE shown in the following:
#!/usr/bin/python # -*- coding: UTF-8 -*- import MySQLdb # 打开数据库连接 db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # 如果数据表已经存在使用 execute() 方法删除表。 cursor.execute("DROP TABLE IF EXISTS EMPLOYEE") # 创建数据表SQL语句 sql = """CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )""" cursor.execute(sql) # 关闭数据库连接 db.close()
Database insert
The following example uses execute a SQL INSERT statement to insert a record to the table EMPLOYEE:
#!/usr/bin/python # -*- coding: UTF-8 -*- import MySQLdb # 打开数据库连接 db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 插入语句 sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)""" try: # 执行sql语句 cursor.execute(sql) # 提交到数据库执行 db.commit() except: # Rollback in case there is any error db.rollback() # 关闭数据库连接 db.close()
The above example can also be written as follows:
#!/usr/bin/python # -*- coding: UTF-8 -*- import MySQLdb # 打开数据库连接 db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 插入语句 sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \ LAST_NAME, AGE, SEX, INCOME) \ VALUES ('%s', '%s', '%d', '%c', '%d' )" % \ ('Mac', 'Mohan', 20, 'M', 2000) try: # 执行sql语句 cursor.execute(sql) # 提交到数据库执行 db.commit() except: # 发生错误时回滚 db.rollback() # 关闭数据库连接 db.close()
Example:
The following code uses variables to pass parameters to a SQL statement:
.................................. user_id = "test123" password = "password" con.execute('insert into Login values("%s", "%s")' % \ (user_id, password)) ..................................
Database queries
Python Mysql query using fetchone () method to obtain a single data using fetchall () method to obtain a plurality of data.
- fetchone (): This method gets the next query result set.The result set is a target
- fetchall (): returns the result to receive all rows.
- rowcount: This is a read-only attribute, and returns the execution execute () method after the number of rows affected.
Example:
Discover EMPLOYEE table salary (wage) data field is greater than all of 1000:
#!/usr/bin/python # -*- coding: UTF-8 -*- import MySQLdb # 打开数据库连接 db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 查询语句 sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > '%d'" % (1000) try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # 打印结果 print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \ (fname, lname, age, sex, income ) except: print "Error: unable to fecth data" # 关闭数据库连接 db.close()
The above script execution results are as follows:
fname=Mac, lname=Mohan, age=20, sex=M, income=2000
Database updates
Data updating operation for updating the data table, the following examples will TESTDB table SEX field all changes to 'M', AGE field is incremented by 1:
#!/usr/bin/python # -*- coding: UTF-8 -*- import MySQLdb # 打开数据库连接 db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 更新语句 sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') try: # 执行SQL语句 cursor.execute(sql) # 提交到数据库执行 db.commit() except: # 发生错误时回滚 db.rollback() # 关闭数据库连接 db.close()
Deletion
Delete data delete data for the table, the following example demonstrates delete data in the table EMPLOYEE AGE is greater than 20, all of the data:
#!/usr/bin/python # -*- coding: UTF-8 -*- import MySQLdb # 打开数据库连接 db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 删除语句 sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20) try: # 执行SQL语句 cursor.execute(sql) # 提交修改 db.commit() except: # 发生错误时回滚 db.rollback() # 关闭连接 db.close()
Executive Affairs
Transaction mechanism to ensure data consistency.
Services should have four properties: atomicity, consistency, isolation, durability. These four properties are often referred to as the ACID properties.
- Atomic (atomicity). A transaction is an indivisible unit of work, such operations are included in the transaction either do or do not do.
- Consistency (consistency). Services must be changed to make the database from one consistent state to another consistent state. Consistency and atomicity are closely related.
- Isolation (isolation). Execute a transaction can not be other transactions interference. That is a transaction internal operation and use of other data transaction concurrency is isolated and can not interfere with each other between individual transactions execute concurrently.
- Persistence (durability). Persistent also called permanent (permanence), it means that once a transaction commits, change its data in the database should be permanent. The following other operations or its failure should not have any impact.
Python DB API 2.0 provides two methods of transaction commit or rollback.
Example:
# SQL删除记录语句 sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20) try: # 执行SQL语句 cursor.execute(sql) # 向数据库提交 db.commit() except: # 发生错误时回滚 db.rollback()
For transactional database, database programming in Python, when the cursor is established, automatically begins a database transaction invisible.
commit () method for all the updates cursor, rollback () method to roll back all the operations of the current cursor. Each method has started a new business.
Error Handling
DB API defines some errors and abnormal operation of the database, the following table lists the errors and exceptions:
abnormal | description |
---|---|
Warning | When there is a serious warning to trigger, such as inserting data is truncated, and so on. It must be a subclass of StandardError. |
Error | All other error classes other than warnings. It must be a subclass of StandardError. |
InterfaceError | Fires when the database interface module has its own error (instead of an error database) occurs. It must be a subclass of Error. |
DatabaseError | Trigger and database-related errors. It must be a subclass of Error. |
DataError | When there is a data processing error occurred when triggered, for example: divide by zero error, the data out of range, and so on. It must be a subclass of DatabaseError. |
OperationalError | It refers to a non-user-controlled, but the error occurred while the database. For example: accidentally disconnected, the database name is not found, the transaction fails, memory allocation error, etc. operations database error occurred. It must be a subclass of DatabaseError. |
IntegrityError | Integrity-related errors, such as a foreign key check fails and the like. It must be DatabaseError subclass. |
InternalError | Internal database error, such as a cursor (cursor) fails, the failure transaction synchronization and so on. It must be DatabaseError subclass. |
ProgrammingError | Procedural errors, such as data table (table) not found or already exists, SQL statement syntax error, an error number of parameters, and so on. It must be a subclass of DatabaseError. |
NotSupportedError | Not supported error, refers to the use of functions such as API or database does not support. For example, using .rollback on the connection object () function, but the database does not support transactions or transaction has been closed. It must be a subclass of DatabaseError. |