This example describes the use of MYSQLDB modules in Python. Share to everyone for your reference. The specific usage analysis is as follows:
MySQLdb is a bit like a PHP or ASP connection to a database model, but MYSQLDB is connected to MySQL interface, we can connect in Python mysqldb to implement the various operations of the data.
Python connection to MySQL's solution is Oursql, Pymysql, Myconnpy, MySQL Connector, etc., but this article to say is indeed another class library mysqldb,mysqldb is used for Python link MySQL database interface, It implements the Python database API specification V2.0, based on the MySQL C API. can be obtained and installed from: Https://pypi.python.org/pypi/MySQL-python, and many distributions of Linux sources have this module, can be directly installed through the source.
One, database connection
MYSQLDB provides a connect method for establishing a connection to the database, receiving several parameters, and returning the Connection object:
Copy Code code as follows:
Conn=mysqldb.connect (host= "localhost", user= "root", passwd= "jb51", db= "test", charset= "UTF8")
The more commonly used parameters include:
Host: Database host name. Default is Local host
User: Database login name. default is current user
PASSWD: The Secret of database login. Null default
DB: The name of the database to use. No default value
The TCP port used by the Port:mysql service. Default is 3306
CharSet: Database encoding
More information about the parameters can be found here http://mysql-python.sourceforge.net/MySQLdb.html
Then, this connection object also provides support for transaction operations, standard methods:
Commit () Commit
Rollback () rollback
Look at a simple query example as follows:
Copy Code code as follows:
#!/usr/bin/python
# Encoding:utf-8
Import MySQLdb
# Open Database connection
db = MySQLdb.connect ("localhost", "root", "361way", "test")
# Use the cursor () method to get the action cursor
cursor = Db.cursor ()
# Execute SQL statement using the Execute method
Cursor.execute ("SELECT VERSION ()")
# Use the Fetchone () method to get a database.
data = Cursor.fetchone ()
Print "Database version:%s"% data
# Close Database connection
Db.close ()
Script execution results are as follows:
Database version:5.5.40
Two, cursor method execution and return value
The cursor method provides two types of operations: 1. Execute command, 2. Receive return value.
Cursor the method used to execute the command
Copy Code code as follows:
Used to execute the stored procedure, the received parameter is the stored procedure name and the parameter list, and the return value is the number of rows affected
Callproc (self, procname, args)
Executes a single SQL statement, the parameters received are the SQL statement itself and the list of parameters used, and the return value is the number of rows affected
Execute (Self, query, args)
Executes a singled out SQL statement, but repeats the parameters in the parameter list, returning the value to the number of rows affected
Executemany (self, query, args)
Move to the next result set
Nextset (self)
Cursor the method used to receive the return value
Receives all returned result rows.
Fetchall (self)
Receive the size bar returns the result row. If the value of size is greater than the number of result rows returned, the Cursor.arraysize bar data is returned
Fetchmany (self, size=none)
Returns a result row
Fetchone (self)
Move the pointer to a row. If mode= ' relative ' indicates that the value bar is moved from the current row, and if mode= ' absolute ', the value bar is moved from the first row of the result set
Scroll (self, value, mode= ' relative ')
This is a read-only property and returns the number of rows affected after executing the Execute () method
RowCount
Third, database operation
1. Create DATABASE tables
If a database connection exists we can use the Execute () method to create a table for the database, as shown in the following CREATE TABLE employee:
Copy Code code as follows:
#!/usr/bin/python
# Encoding:utf-8
Import MySQLdb
# Open Database connection
db = MySQLdb.connect ("localhost", "root", "361way", "test")
# Use the cursor () method to get the action cursor
cursor = Db.cursor ()
# If a datasheet already exists use the Execute () method to delete the table.
Cursor.execute ("DROP TABLE IF EXISTS EMPLOYEE")
# Create a datasheet SQL statement
sql = "" "CREATE TABLE EMPLOYEE (
First_Name CHAR (not NULL),
Last_Name CHAR (20),
Age INT,
SEX CHAR (1),
Income FLOAT) "" "
Cursor.execute (SQL)
# Close Database connection
Db.close ()
2. Database insert operation
Copy Code code as follows:
#!/usr/bin/python
# Encoding:utf-8
Import MySQLdb
# Open Database connection
db = MySQLdb.connect ("localhost", "root", "361way", "test")
# Use the cursor () method to get the action cursor
cursor = Db.cursor ()
# SQL INSERT statement
sql = "" INSERT into EMPLOYEE (first_name,
Last_Name, age, SEX, income)
VALUES (' Mac ', ' Mohan ', ', ' M ', 2000) "" "
Try
# Execute SQL statement
Cursor.execute (SQL)
# Commit to Database execution
Db.commit ()
Except
# Rollback In case there be any error
Db.rollback ()
# Close Database connection
Db.close ()
Here is an example of a single SQL execution, where readers interested in Cursor.executemany can refer to the relevant AWS host asset Management System example.
The above example can also be written in the form of a placeholder Fu, as follows:
Copy Code code as follows:
#!/usr/bin/python
# Encoding:utf-8
Import MySQLdb
# Open Database connection
db = MySQLdb.connect ("localhost", "testuser", "test123", "TestDB")
# Use the cursor () method to get the action 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 ', ', ' M ', 2000)
Try
# Execute SQL statement
Cursor.execute (SQL)
# Commit to Database execution
Db.commit ()
Except
# rollback When an error occurs
Db.rollback ()
# Close Database connection
Db.close ()
You can also pass parameters in the form of a variable, as follows:
Copy Code code as follows:
..................................
user_id = "Test"
Password = "Password123"
Con.execute (' INSERT into Login values ('%s ', '%s ') '% \
(user_id, password))
..................................
3. Database query operation
For example, to query for all data in the employee table where the salary (payroll) field is greater than 1000:
Copy Code code as follows:
#!/usr/bin/python
# Encoding:utf-8
Import MySQLdb
# Open Database connection
db = MySQLdb.connect ("localhost", "root", "361way", "test")
# Use the cursor () method to get the action cursor
cursor = Db.cursor ()
# SQL Query Statement
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 Database connection
Db.close ()
The above script execution results are as follows:
Fname=mac, Lname=mohan, age=20, Sex=m, income=2000
4. Database Update operation
The update operation is used to update the data in the datasheet, and the following instance modifies the SEX field in the test table to ' M ' and the Age field increments by 1:
Copy Code code as follows:
# Encoding:utf-8
#!/usr/bin/python
Import MySQLdb
# Open Database connection
db = MySQLdb.connect ("localhost", "root", "361way", "test")
# Use the cursor () method to get the action 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)
# Commit to Database execution
Db.commit ()
Except
# rollback When an error occurs
Db.rollback ()
# Close Database connection
Db.close ()
5. Executive Affairs
Transaction mechanisms ensure data consistency.
transactions should have 4 properties: atomicity, consistency, isolation, persistence. These four properties are commonly referred to as ACID properties.
① Atomic Sex (atomicity). A transaction is an indivisible unit of work, and all operations included in the transaction are either done or not done.
② 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 a transaction cannot be interfered by other transactions. That is, the operations within a transaction and the data used are isolated from other concurrent transactions, and the transactions performed concurrently cannot interfere with each other.
④ persistence (durability). Persistence is also called permanence (permanence), meaning that once a transaction is committed, its changes to the data in the database should be permanent. The rest of the operation or failure should not have any effect on it.
The Python DB API 2.0 Transaction provides two methods for commit or rollback. Instance:
Copy Code code as follows:
# SQL Delete Record statement
sql = "DELETE from EMPLOYEE WHERE age > '%d '"% (20)
Try
# Execute SQL statement
Cursor.execute (SQL)
# Submit to Database
Db.commit ()
Except
# rollback When an error occurs
Db.rollback ()
For databases that support transactions, in Python database programming, a stealth database transaction is automatically initiated when the cursor is established. All update operations of the commit () method cursor, the rollback () method rolls back all operations of the current cursor. Each method starts a new transaction.
I hope this article will help you with your Python programming.
