[Python] Learning notes MySQL database operations

1 Python Standard Database Interface Db-api Introduction

The Python standard database interface is Python DB-API, which provides developers with a database application programming interface. Python DB-API supports a wide variety of databases, and you can choose which databases are relevant to your project. The databases supported by Python Db-api are as follows:

• Gadfly
• MSQL
• Mysql
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• Oracle
• Sybase

different databases you need to download different DB API modules, such as you need to access the Oracle database and MySQL data, you need to download the Oracle and MySQL database modules. Python's Db-api, which implements the interface for most databases, uses it to connect to each database and manipulate the databases in the same way.

　　Python DB-API Usage Process:

1) introduce API modules.

2) gets the connection to the database.

3) executes SQL statements and stored procedures.

4) close the database connection.

　　Python database learning, mainly in learning Db-api learning process, remember these four steps, you have successfully more than half.

2 installation of MySQL

Because bloggers are currently only concerned with MySQL usage, this article is limited to how to use Python to manipulate MySQL databases. Here's a brief look at how to install MySQL for your own Python IDE:

Step1 confirm that MySQL is already installed,　　 Execute the following code:

# Encoding:utf-8 # !/usr/bin/python
Import MySQLdb

If the error after execution indicates that MySQL is not installed, you do not have to install MySQL.

　　STEP2 download MySQL and install:

One: please click here;

Two: please click here.

The installation steps are similar to those of Python's other libraries and are not detailed. You can use the Step1 method to confirm that you have successfully installed MySQL.

3 Database connections

Before connecting to the database, please confirm the following: Connect to the database TestDB use the user name "TestUser", the password is "test123", you can set your own or directly use the root user name and its password.

The following example links the MySQL TestDB database, which corresponds to the four steps of the DB-API:

# Encoding:utf-8 # !/usr/bin/python
Introducing API Modules Import MySQLdb

Get a connection to a database

# Open a database connection
db = MySQLdb.connect ("localhost","testuser"," test123 ","TESTDB" )

# 3 Execute SQL statements and stored procedures
#get an operation cursor using the cursor () methodcursor =db.cursor ()#executing SQL statements using the Execute methodCursor.execute ("SELECT VERSION ()")#Use the Fetchone () method to get a database. data =Cursor.fetchone ()Print "Database version:%s"%data

# 4 Closing the database connection #To close a database connectionDb.close ()

　　The execution results show:

Database version:5.0.45

4 Creating a database table

We are trying to create a custom database table:

database table name: Employeeemployee

Data fields: First_name,last_name,age,sex and INCOME.

#Encoding:utf-8#!/usr/bin/pythonImportMySQLdb#Open a database connectiondb = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )#get an operation cursor using the cursor () methodcursor =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 statementsql ="""CREATE TABLE EMPLOYEE (first_name char () not NULL, last_name char (+), age INT, SEX CHAR (1), INCOME FLOAT)"""cursor.execute (SQL)#To close a database connectionDb.close ()

5 Inserting data operations

To insert a record into a database table EMPLOYEE using a SQL INSERT statement:

#Encoding:utf-8#!/usr/bin/pythonImportMySQLdb#Open a database connectiondb = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL INSERT statementsql ="""INSERT into EMPLOYEE (first_name, last_name, age, SEX, INCOME) VALUES (' Mac ', ' Mohan ', ' M ', ' 2000 ') /c13>"""Try:   #Execute SQL statementcursor.execute (SQL)#commit to database executionDb.commit ()except:   #Rollback In case there was any errorDb.rollback ()#To close a database connectionDb.close ()

Here are a few important points to ask:

　　1) Please note that you must have Conn.commit () to commit the transaction, or you cannot actually insert the data.

2) Dollback () is a rollback operation, and rollback (Rollback) refers to the behavior of a program or data processing error, which restores the program or information to the last correct state. Rollback includes types such as program rollback and data rollback.

6 Database Query Operations

Python queries MySQL uses the Fetchone () method to get a single piece of data, using the Fetchall () method to get multiple 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 the Execute () method is executed.

Query all data with the salary (payroll) field greater than 1000 in the employee table:

#Encoding:utf-8#!/usr/bin/pythonImportMySQLdb#Open a database connectiondb = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL Query Statementssql ="SELECT * from EMPLOYEE WHERE INCOME > '%d '"% (1000)Try:   #Execute SQL statementcursor.execute (SQL)#get list of all recordsResults =Cursor.fetchall () forRowinchResults: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"#To close a database connectionDb.close ()

The execution results show:

Fname=mac, Lname=mohan, age=20, Sex=m, income=2000

7 Database Update operations

The update action is used to update the data in the data table, and the following instance modifies all the SEX fields in the TestDB table to ' M ', and the Age field increments by 1:

#Encoding:utf-8#!/usr/bin/pythonImportMySQLdb#Open a database connectiondb = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL UPDATE statementsql ="UPDATE EMPLOYEE SET age = age + 1WHERE SEX ='%c' "% (' M ')Try:   #Execute SQL statementcursor.execute (SQL)#commit to database executionDb.commit ()except:   #Roll Back when an error occursDb.rollback ()#To close a database connectionDb.close ()

8 transaction mechanism of the database

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.

Like what:

# SQL Delete Record statement " DELETE from the EMPLOYEE WHERE age > '%d ' "%" try:   #  Execute SQL statement    cursor.execute (SQL)    # Submit to Database    db.commit ()except:   # rollback 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.

9 Common functions

Here's a summary of commonly used functions:

1) the Connect () method is used to create a connection to a database where parameters can be specified: User name, password, host, and so on. This is only connected to the database and you need to create a cursor to manipulate the database.

2) commit () Commit
3) rollback () rollback

4) cursor is used to execute the command method:
Callproc (self, procname, args): Used to execute stored procedure, received parameter is stored procedure name and parameter list, return value is the number of rows affected
Execute (Self, query, 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
Executemany (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
Nextset (self): move to the next result set
5) The cursor is used to receive the return value of the method:
Fetchall (self): receives all the returned result rows.
Fetchmany (self, Size=none): Receives a size bar that returns the result row. If the value of size is greater than the number of result rows returned, the cursor.arraysize data is returned.
Fetchone (self): Returns a result row.
6) Scroll (self, value, mode= ' relative '): Moves the pointer to a row. If mode= ' relative ', the value bar is moved from the current row, if mode= ' absolute ', Represents the move value bar from the first row of the result set.

10 Reference Content

[1] W3cschool. CC python operation MySQL Database

[2] A man's Travels

[3] Insect Master

[Python] Learning notes MySQL database operations