The correct way to connect Python to mysql database

The 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 need to download different files for different databases DB API module, for example, if you need to access Oracle database and MySQL data, you need to download the Oracle and MySQL database modules.

DB-API is a specification. It defines a series of necessary objects and database access methods to provide a consistent access interface for a variety of underlying database systems and a variety of 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 the API module.

Get the connection to the database.

Execute SQL statements and stored procedures.

Close the database connection.

1. What is MySQLdb?

MySQLdb is an interface for Python to connect to the Mysql database. It implements the Python database API specification V2.0 and is based on the MySQL C API. .

2. How to install MySQLdb?

In order to use DB-API to write MySQL scripts, you must ensure that MySQL has been installed. Copy the following code and execute it:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
 
import MySQLdb

If the output after execution is as follows, it means you do not have the MySQLdb module installed:

Traceback (most recent call last):
 File "test.py", line 3, in <module>
  import MySQLdb
ImportError: No module named MySQLdb

If you choose the binary file release version, the installation process will be completed with basic installation prompts. If installing from source code, you need to switch 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.

3. Database connection

Before connecting to the database, please confirm the following:

You have created the database TESTDB.

In the TESTDB database you 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.

Example:

The following example links to the TESTDB database of Mysql:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
 
import MySQLdb
 
# 打开数据库连接
db = MySQLdb.connect("localhost","codecloud","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标
cursor = db.cursor()
 
# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")
 
# 使用 fetchone() 方法获取一条数据库。
data = cursor.fetchone()
 
print "Database version : %s " % data
 
# 关闭数据库连接
db.close()

The output result of executing the above script is as follows:

Database version : 5.0.45

4. Create a database table

If the database connection exists, we can use the execute() method to create a table for the database. Create the table EMPLOYEE as follows:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
 
import MySQLdb
 
# 打开数据库连接
db = MySQLdb.connect("localhost","codecloud","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()

5. Database insert operation

The following example uses the SQL INSERT statement to insert records into the table EMPLOYEE:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
 
import MySQLdb
 
# 打开数据库连接
db = MySQLdb.connect("localhost","codecloud","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 in the following form:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
 
import MySQLdb
 
# 打开数据库连接
db = MySQLdb.connect("localhost","codecloud","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 the SQL statement:

..................................
user_id = "test123"
password = "password"
 
con.execute('insert into Login values("%s", "%s")' % \
       (user_id, password))
..................................

Six , Database query operation

Python queries Mysql to obtain a single piece of data using the fetchone() method, and to obtain multiple pieces of data using the fetchall() method.

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 in the EMPLOYEE table whose salary field is greater than 1000:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
 
import MySQLdb
 
# 打开数据库连接
db = MySQLdb.connect("localhost","codecloud","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 execution results of the above script are as follows:

fname=Mac, lname=Mohan, age=20, sex=M, income=2000

7. Database update operation

The update operation is used to update the data of the data table. The following example Modify all SEX fields in the TESTDB table to 'M', and increment the AGE field by 1:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
 
import MySQLdb
 
# 打开数据库连接
db = MySQLdb.connect("localhost","codecloud","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()

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删除记录语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
  # 执行SQL语句
  cursor.execute(sql)
  # 向数据库提交
  db.commit()
except:
  # 发生错误时回滚
  db.rollback()

For databases that support transactions, in Python database programming, when the 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:

Above That’s the entire content of this article, I hope it will be helpful to everyone’s study.

For more articles related to the correct posture of connecting Python to mysql database, please pay attention to the PHP Chinese website!