Python operates mysql database
- 高洛峰Original
- 2016-11-23 11:31:131480browse
Python’s standard database interface is Python DB-API, which provides developers with a database application programming interface.
Python database interface supports a lot of databases, you can choose the database that suits your project:
GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
Oracle
Sybase
You can visit the Python database interface and API to view a detailed list of supported databases.
You need to download different DB API modules for different databases. For example, if you need to access Oracle database and Mysql data, you need to download Oracle and MySQL database modules.
DB-API is a specification. It defines a series of necessary objects and database access methods to provide consistent access interfaces for various underlying database systems and various database interface programs.
Python's DB-API implements interfaces for most databases. After using it to connect to each database, you can operate each database in the same way.
Python DB-API usage process:
Introduce API module.
Get the connection to the database.
Execute SQL statements and stored procedures.
Close the database connection.
What is MySQLdb?
MySQLdb is an interface for Python to connect to Mysql database. It implements the Python database API specification V2.0 and is based on the MySQL C API.
How to install MySQLdb?
In order to write MySQL scripts with DB-API, you must ensure that MySQL is installed. Copy the following code and execute it:
#!/usr/bin/python
import MySQLdb
If the output after execution is as shown below, it means you have not installed the MySQLdb module ：
Traceback (most recent call last):
File "test.py", line 3, in
import MySQLdb
ImportError: No module named MySQLdb
To install MySQLdb, please visit http://sourceforge.net/projects/mysql-python. From here, you can choose the installation package suitable for your platform, which is divided into precompiled binary files and source code installation packages.
If you choose the binary file distribution version, the installation process can be completed with basic installation prompts. If you are installing from source code, 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
The 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 directly use the root username and password. For Mysql database user authorization, please use the Grant command.
The Python MySQLdb module has been installed on your machine.
If you are not familiar with sql statements, you can visit our basic SQL tutorial
Example:
The following example links to Mysql's TESTDB database:
#!/usr/bin/python
import MySQLdb
# Open the database connection
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB" )
# Use the cursor() method to get the operation cursor
cursor = db.cursor()
# Use the execute method to execute SQL statements
cursor.execute("SELECT VERSION()")
# Use the fetchone() method to obtain a database.
data = cursor.fetchone()
print "Database version: %s " % data
# Close the database connection
db.close()
Execute the above script The output is as follows ：
Database version : 5.0.45
Create database table
If the database connection exists we can use the execute() method to create a table for the database, create table EMPLOYEE as follows:
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")
# Use the cursor() method to get the operation cursor
cursor = db.cursor()
# If the data table already exists, use the execute() method to delete the table.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
#Create data table SQL statement
sql = """CREATE TABLE EMPLOYEE (
) FIRST_NAME CHAR(20) NOT NULL,
LAST_NAMECHAR(20 ),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
#Close database connection
db.close()
Database insert operation
The following example uses the SQL INSERT statement to insert records into the table EMPLOYEE:
#!/usr/bin/python
import MySQLdb
#Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")
# Use the 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', 200 0)"""
try:
#Execute sql statement
cursor.execute(sql)
#Submit to 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 in the following form:
#!/usr/bin/python
import MySQLdb
# Open the database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# Use the cursor() method to get 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 sql statement
cursor.execute (sql)
# Submit to the database for execution
db.commit()
except:
# Rollback when an error occurs
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") ' %
Database query operation
Python queries Mysql using 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 the returned result rows.
rowcount: This is a read-only property and returns the number of rows affected after executing the execute() method.
Example:
Query all data with a salary field greater than 1000 in the EMPLOYEE table:
#!/usr/bin/python
import MySQLdb
# Open the database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")
# Use the cursor() method to obtain the operation cursor
cursor = db.cursor()
# SQL query statement
sql = "SELECT * FROM EMPLOYEE
using = cursor.fetchall()
for row in results:
[4]
print "Error: unable to fetch 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
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 AGE fields. Increment by 1:
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123" , "TESTDB" )
# Use the 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 database for execution
db.commit()
except:
# Rollback when an error occurs
db.rollback()
# Close the database connection
db.close()
Execute transactions
The transaction mechanism can ensure data consistency.
Transactions should have 4 attributes: atomicity, consistency, isolation, and durability. These four properties are often called ACID properties.
Atomicity. A transaction is an indivisible unit of work. All operations included in the transaction are either done or none.
Consistency. A transaction must change the database from one consistency state to another. Consistency and atomicity are closely related.
Isolation. The execution of a transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and transactions executed concurrently cannot interfere with each other.
Durability. Continuity, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Subsequent operations or failures should not have any impact on it.
Python DB API 2.0 transactions provide two methods 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:
In database programming, when a cursor is created, an invisible database transaction is automatically started.
The commit() method performs all update operations on the cursor, and the rollback() method rolls back all operations on the current cursor. Each method starts a new transaction.
Error handling
DB API defines some errors and exceptions for database operations. The following table lists these errors and exceptions:
Exception
Description
Warning is triggered when there is a serious warning, such as Inserted data is truncated and so on. Must be a subclass of StandardError.
Error All other error types except warnings. Must be a subclass of StandardError.
InterfaceError Triggered when an error occurs in the database interface module itself (not an error in the database). Must be a subclass of Error.
DatabaseError Triggered when an error related to the database occurs. 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 is unexpectedly disconnected, the database name is not found, transaction processing fails, memory allocation errors, etc. are errors that occur when operating the database. Must be a subclass of DatabaseError.
IntegrityError Errors related to integrity, such as foreign key check failure, etc. Must be a DatabaseError subclass.
InternalError Internal error of the database, such as cursor failure, transaction synchronization failure, etc. Must be a DatabaseError subclass.
ProgrammingError Programming error, such as the data table (table) is not found or already exists, SQL statement syntax error, wrong number of parameters, etc. Must be a subclass of DatabaseError.
NotSupportedError Not supported error refers to the use of 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 has been closed. Must be a subclass of DatabaseError.