Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to operate the MySQL database in Python. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

What is MySQLdb?

MySQLdb is an interface for Python to link to Mysql databases. It implements Python database API specification V2.0, which is based on MySQL C API.

Install the Python MySQLdb module

Linux version: (configure yum source)

Pip install MySQL-python

Yum install MySQL-python

How do I load MySQLdb?

In order to write MySQL scripts in DB-API, you must make sure that MySQL is installed. Copy the following code and execute:

#! / usr/bin/python

#-*-coding: UTF-8-*-

Import MySQLdb

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

Traceback (most recent call last):

File "test.py", line 3, in

Import MySQLdb

ImportError: No module named MySQLdb database connection

Before connecting to the database, confirm the following:

You have created the database TESTDB. Exe.

You have created the table EMPLOYEE in the TESTDB database

The EMPLOYEE table fields are FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.

Connect to the database TESTDB using the user name "testuser", the password is "test123", you can set or directly use the root user name and password, Mysql database user authorization, please use the Grant command.

The Python MySQLdb module has been installed on your machine.

Example:

The following example links the TESTDB database of Mysql:

#! / usr/bin/python

#-*-coding: UTF-8-*-

Import MySQLdb

# Open database connection

Db = MySQLdb.connect ("localhost", "testuser", "test123", "TESTDB", charset='utf8')

# obtain operation cursors 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 piece of data

Data = cursor.fetchone ()

Print "Database version:% s"% data

# close database connection

Db.close ()

The output of executing the above script is as follows:

Database version: 5.0.45 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 EMPLOYEE:

#! / usr/bin/python

#-*-coding: UTF-8-*-

Import MySQLdb

# Open database connection

Db = MySQLdb.connect ("localhost", "testuser", "test123", "TESTDB", charset='utf8')

# obtain operation cursors using the cursor () method

Cursor = db.cursor ()

# use the execute () method to delete the table if the table already exists.

Cursor.execute ("DROP TABLE IF EXISTS EMPLOYEE")

# create a data table SQL statement

Sql = "" CREATE TABLE EMPLOYEE (

FIRST_NAME CHAR (20) NOT NULL

LAST_NAME CHAR (20)

AGE INT

SEX CHAR (1)

INCOME FLOAT) ""

Cursor.execute (sql)

# close database connection

Db.close () database insert operation

The following example inserts a record into the table EMPLOYEE using the execute SQL INSERT statement:

#! / usr/bin/python

#-*-coding: UTF-8-*-

Import MySQLdb

# Open database connection

Db = MySQLdb.connect ("localhost", "testuser", "test123", "TESTDB", charset='utf8')

# obtain operation cursors using the cursor () method

Cursor = db.cursor ()

# SQL insert statement

Sql = "" INSERT INTO EMPLOYEE (FIRST_NAME

LAST_NAME, AGE, SEX, INCOME)

VALUES ('Mac',' Mohan', 20, 'Manners, 2000) ""

Try:

# execute sql statement

Cursor.execute (sql)

# submit to the database for execution

Db.commit ()

Except:

# Rollback in case there is any error

Db.rollback ()

# close database connection

Db.close ()

The above examples can also be written in the following form:

#! / usr/bin/python

#-*-coding: UTF-8-*-

Import MySQLdb

# Open database connection

Db = MySQLdb.connect ("localhost", "testuser", "test123", "TESTDB", charset='utf8')

# obtain operation cursors using the cursor () method

Cursor = db.cursor ()

# SQL insert statement

Sql = "INSERT INTO EMPLOYEE (FIRST_NAME,\)

LAST_NAME, AGE, SEX, INCOME)\

VALUES (% s,% s) "%\

('Mac',' Mohan', 20, 'Manners, 2000)

Try:

# execute sql statement

Cursor.execute (sql)

# submit to the database for execution

Db.commit ()

Except:

# Roll back when an error occurs

Db.rollback ()

# close 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)'%\

(user_id, password))

.. Database query operation

The Python query Mysql uses the fetchone () method to get a single piece of data and the fetchall () method to get 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 the execute () method is executed.

Example:

Query all data in the EMPLOYEE table where the salary (payroll) field is greater than 1000:

#! / usr/bin/python

#-*-coding: UTF-8-*-

Import MySQLdb

# Open database connection

Db = MySQLdb.connect ("localhost", "testuser", "test123", "TESTDB", charset='utf8')

# obtain operation cursors using the cursor () method

Cursor = db.cursor ()

# SQL query statement

Sql = "SELECT * FROM EMPLOYEE\"

WHERE INCOME > s "(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 the result

Print "fname=%s,lname=%s,age=%s,sex=%s,income=%s"%\

(fname, lname, age, sex, income)

Except:

Print "Error: unable to fecth data"

# close database connection

Db.close ()

The execution result of the above script is as follows:

Fname=Mac, lname=Mohan, age=20, sex=M, income=2000 database update operation

The update operation is used to update the data of the data table. The following example increments the AGE field in the EMPLOYEE table where the SEX field is'M':

#! / usr/bin/python

#-*-coding: UTF-8-*-

Import MySQLdb

# Open database connection

Db = MySQLdb.connect ("localhost", "testuser", "test123", "TESTDB", charset='utf8')

# obtain operation cursors 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)

# submit to the database for execution

Db.commit ()

Except:

# Roll back when an error occurs

Db.rollback ()

# close database connection

Db.close () delete operation

The delete operation is used to delete data in the data table. The following example demonstrates deleting all data in the data table EMPLOYEE whose AGE is greater than 20:

#! / usr/bin/python

#-*-coding: UTF-8-*-

Import MySQLdb

# Open database connection

Db = MySQLdb.connect ("localhost", "testuser", "test123", "TESTDB", charset='utf8')

# obtain operation cursors using the cursor () method

Cursor = db.cursor ()

# SQL delete statement

Sql = "DELETE FROM EMPLOYEE WHERE AGE >% s"% (20)

Try:

# execute SQL statement

Cursor.execute (sql)

# submit changes

Db.commit ()

Except:

# Roll back when an error occurs

Db.rollback ()

# close the connection

Db.close () executes transaction

The transaction mechanism ensures data consistency.

Transactions should have four attributes: atomicity, consistency, isolation, and persistence. These four attributes are often referred to as ACID attributes.

Atomicity (atomicity). A transaction is an indivisible unit of work, and all the operations included in the transaction are either done or not done.

Consistency (consistency). The transaction must change the database from one consistency state to another. Consistency is closely related to atomicity.

Isolation (isolation). The execution of one transaction cannot be interfered with by other transactions. That is, the operations and the data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions can not interfere with each other.

Persistence (durability). Persistence, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Other operations or failures that follow should not affect it in any way.

Transactions in Python DB API 2.0 provide two methods, commit or rollback.

Example: # SQL delete record statement

Sql = "DELETE FROM EMPLOYEE WHERE AGE >% s"% (20)

Try:

# execute SQL statement

Cursor.execute (sql)

# submit to the database

Db.commit ()

Except:

# Roll back when an error occurs

Db.rollback ()

For databases that support transactions, in Python database programming, an invisible database transaction is automatically started when the cursor is created.

All update operations of the commit () method cursor, and the rollback () method rolls back all operations of the current cursor. Each method starts a new transaction.

Error handling

Some errors and exceptions for database operations are defined in DB API. The following table lists these errors and exceptions:

Exception description Warning is triggered when there is a serious warning, such as when the inserted data is truncated, and so on. Must be a subclass of StandardError. All other error classes except Error warnings. Must be a subclass of StandardError. InterfaceError is triggered when an error occurs in the database interface module itself (not in the database). Must be a subclass of Error. Triggered when a database-related error occurs in DatabaseError. Must be a subclass of Error. DataError is triggered when an error occurs when there is a data processing error, such as a zero division error, data out of range, etc. Must be a subclass of DatabaseError. OperationalError refers to errors that occur when manipulating the database, not controlled by the user. For example: unexpected disconnection, database name not found, transaction failure, memory allocation error, and so on, the operation of the database is an error. Must be a subclass of DatabaseError. Errors related to IntegrityError integrity, such as foreign key check failure. Must be a DatabaseError subclass. Internal errors in the InternalError database, such as cursor failure, transaction synchronization failure, and so on. Must be a DatabaseError subclass. ProgrammingError program errors, such as data table (table) is not found or already exists, SQL statement syntax error, parameter number error, and so on. Must be a subclass of DatabaseError. NotSupportedError does not support errors, which refers to the use of functions or API that are not supported by the database. For example, use the .rollback () function on a connection object, but the database does not support transactions or transactions have been closed. Must be a subclass of DatabaseError.

About how to operate the MySQL database in Python is shared here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.