Python Mysql Database operation

This example describes the use of the MySQLdb module in Python. Share to everyone for your reference. The specific usage analysis is as follows:

MySQLdb actually a bit like PHP or ASP in a connection to the database mode, but MYSQLDB is for MySQL connection interface, we can connect mysqldb in Python to achieve various operations of the data.

Python connects MySQL with Oursql, Pymysql, Myconnpy, MySQL Connector, and so on, but this is really another class library mysqldb,mysqldb is the interface for the Python link MySQL database, It implements the Python database API specification V2.0, built on the MySQL C API. It can be obtained and installed from: Https://pypi.python.org/pypi/MySQL-python, and many distribution Linux sources have the module, which can be installed directly from the source.

One, the database connection

MYSQLDB provides the Connect method to establish a connection to the database, receive several parameters, and return the connection object:

Copy the Code code as follows:

Conn=mysqldb.connect (host= "localhost", user= "root", passwd= "jb51", db= "test", charset= "UTF8")

the more commonly used parameters include:
Host : The database hostname. By default, the local host
User: Database login name. The default is the current user
passwd: The Secret of database landing. Default is empty
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 on the parameters can be found here http://mysql-python.sourceforge.net/MySQLdb.html

The connection object is then provided with support for transactional operations, in the standard way:
Commit () Commit
Rollback () rollback

Look at a simple query example as follows:

Copy the Code code as follows:

#!/usr/bin/python
# Encoding:utf-8
Import MySQLdb
# Open Database connection
db = MySQLdb.connect ("localhost", "root", "361way", "test")
# get an action cursor using the cursor () method
cursor = Db.cursor ()
# Execute SQL statements 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 the database connection
Db.close ()

The script execution results are as follows:
Database version:5.5.40

Second, cursor method execution and return value

The cursor method provides two types of operations: 1. Execute the command, 2. Receive the return value.
The cursor method used to execute the command

Copy the Code code as follows:

Used to execute stored procedures, received parameters are stored procedure name and parameter list, return value is the number of rows affected
Callproc (self, procname, args)
Executes a single SQL statement, receives the parameters for the SQL statement itself and the parameter list used, and returns the number of rows affected
Execute (Self, query, args)
Executes a heads-up SQL statement, but repeats the parameters in the list of parameters, with the returned value being the number of rows affected
Executemany (self, query, args)
Move to the next result set
Nextset (self)
The cursor is used to receive the return value of the method
Receives all the returned result rows.
Fetchall (self)
Receives a size bar to return the result row. If the value of size is greater than the number of result rows returned, the cursor.arraysize data is returned
Fetchmany (self, size=none)
Returns a result row
Fetchone (self)
Moves the pointer to a row. If mode= ' relative ', 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, creating table employee as follows:

Copy the Code code as follows:

#!/usr/bin/python
# Encoding:utf-8
Import MySQLdb
# Open Database connection
db = MySQLdb.connect ("localhost", "root", "361way", "test")
# get an action cursor using the cursor () method
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 a data table 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 the database connection
Db.close ()

2. Database insert operation

Copy the Code code as follows:

#!/usr/bin/python
# Encoding:utf-8
Import MySQLdb
# Open Database connection
db = MySQLdb.connect ("localhost", "root", "361way", "test")
# get an action cursor using the cursor () method
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 are any error
Db.rollback ()
# Close the database connection
Db.close ()

Here is an example of a single-SQL execution, and Cursor.executemany's usage interested readers can refer to the relevant AWS host Asset management system examples.
The above example can also be written in the form of a placeholder Fu, as follows:

Copy the Code code as follows:

#!/usr/bin/python
# Encoding:utf-8
Import MySQLdb
# Open Database connection
db = MySQLdb.connect ("localhost", "testuser", "test123", "TESTDB")
# get an action cursor using the cursor () method
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
# Roll Back when an error occurs
Db.rollback ()
# Close the database connection
Db.close ()

You can also pass a parameter as a variable, as follows:

Copy the Code code as follows:

..................................
user_id = "Test"
Password = "Password123"
Con.execute (' INSERT into Login ' values ('%s ', '%s ') '% \
(user_id, password))
..................................

3. Database query operation
To query all data with the salary (payroll) field greater than 1000 in the employee table as an example:

Copy the Code code as follows:

#!/usr/bin/python
# Encoding:utf-8
Import MySQLdb
# Open Database connection
db = MySQLdb.connect ("localhost", "root", "361way", "test")
# get an action cursor using the cursor () method
cursor = Db.cursor ()
# SQL Query Statement
sql = "SELECT * from EMPLOYEE \
WHERE INCOME > '%d ' "% (1000)
Try
# Execute SQL statement
Cursor.execute (SQL)
# Get a list of all records
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

4. Database Update operation
The update action is used to update data in the data table, and the following instance modifies the SEX field in the test table to ' M ' and the Age field increments by 1:

Copy the Code code as follows:

# Encoding:utf-8

#!/usr/bin/python
Import MySQLdb
# Open Database connection
db = MySQLdb.connect ("localhost", "root", "361way", "test")
# get an action cursor using the cursor () method
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
# Roll Back when an error occurs
Db.rollback ()
# Close the database connection
Db.close ()

5. Executive Affairs

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:

Copy the Code code as follows:

# SQL Delete Record statement
sql = "DELETE from the EMPLOYEE WHERE age > '%d '"% (20)
Try
# Execute SQL statement
Cursor.execute (SQL)
# Submit to Database
Db.commit ()
Except
# Roll Back when an error occurs
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.

Hopefully this article will help you with Python programming.

Turn from: http://www.jb51.net/article/57290.htm

Python Mysql Database operation