Python operates mysql database

Python’s standard database interface is Python DB-API, which provides developers with a database application programming interface.

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 can visit the Python database interface and API to view a detailed list of supported databases.

You need to download different DB API modules for different databases. For example, if you need to access Oracle database and Mysql data, you need to download Oracle and MySQL database modules.

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

Get the connection to the database.

Execute SQL statements and stored procedures.

Close the database connection.

What is MySQLdb?

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

How to install MySQLdb?

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

#!/usr/bin/python

import MySQLdb

If the output after execution is as shown below, it means you have not installed the MySQLdb module ：

Traceback (most recent call last):

File "test.py", line 3, in

import MySQLdb

ImportError: No module named MySQLdb

To install MySQLdb, please visit http://sourceforge.net/projects/mysql-python. From here, you can choose the installation package suitable for your platform, which is divided into precompiled binary files and source code installation packages.

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

Database connection

Before connecting to the database, please confirm the following:

You have created the database TESTDB.

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

If you are not familiar with sql statements, you can visit our basic SQL tutorial

Example:

The following example links to Mysql's TESTDB database:

#!/usr/bin/python

import MySQLdb

# Open the database connection

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB" )

# Use the cursor() method to get the operation cursor

cursor = db.cursor()

# Use the execute method to execute SQL statements

cursor.execute("SELECT VERSION()")

# Use the fetchone() method to obtain a database.

data = cursor.fetchone()

print "Database version: %s " % data

# Close the database connection

db.close()

Execute the above script The output is as follows ：

Database version : 5.0.45

Create database table

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

#!/usr/bin/python

import MySQLdb

# Open database connection

db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

# Use the cursor() method to get the operation cursor

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 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 uses the SQL INSERT statement to insert records into the table EMPLOYEE:

#!/usr/bin/python

import MySQLdb

#Open database connection

db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

# Use the cursor() method to obtain the operation cursor

cursor = db.cursor()

# SQL INSERT statement

sql = """INSERT INTO EMPLOYEE(FIRST_NAME,

LAST_NAME, AGE, SEX, INCOME)

VALUES ('Mac', 'Mohan', 20, 'M', 200 0)"""

try:

​ #Execute sql statement

​ cursor.execute(sql)

​ #Submit to database for execution

​​ db.commit()

except:

​ # Rollback in case there is any error

db. rollback()

# Close the database connection

db.close()

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

#!/usr/bin/python

import MySQLdb

# Open the database connection

db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# Use the cursor() method to get the operation 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', 20, 'M', 2000)

try:

# Execute sql statement

cursor.execute (sql)

​ # Submit to the database for execution

​ db.commit()

except:

​ # Rollback when an error occurs

​ db.rollback()

​

# Close the 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") ' %

Database query operation

Python queries Mysql using the fetchone() method to obtain a single piece of data, and the fetchall() method to obtain 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 executing the execute() method.

Example:

Query all data with a salary field greater than 1000 in the EMPLOYEE table:

#!/usr/bin/python

import MySQLdb

# Open the database connection

db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

# Use the cursor() method to obtain the operation cursor

cursor = db.cursor()

# SQL query statement

sql = "SELECT * FROM EMPLOYEE

                                                                                                          using = cursor.fetchall()

for row in results:

[4]

​​​​​

                                                                                                                                                       print "Error: unable to fetch data"

# Close the database connection

db.close()

The above script execution results are as follows:

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

Database update operation

The update operation is used to update the data in the data table. The following example changes all the SEX fields in the TESTDB table to 'M' and AGE fields. Increment by 1:

#!/usr/bin/python

import MySQLdb

# Open database connection

db = MySQLdb.connect("localhost","testuser","test123" , "TESTDB" )

# Use the cursor() method to obtain the operation 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)

#Submit to database for execution

db.commit()

except:

    # Rollback when an error occurs

db.rollback()

# Close the database connection

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 delete record statement

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)

try:

#Execute SQL statement

Cursor. execute(sql)

​ # Submit to the database

db.commit()

except:

In database programming, when a 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:

Exception

Description

Warning is triggered when there is a serious warning, such as Inserted data is truncated and so on. Must be a subclass of StandardError.

Error All other error types except warnings. Must be a subclass of StandardError.

InterfaceError Triggered when an error occurs in the database interface module itself (not an error in the database). Must be a subclass of Error.

DatabaseError Triggered when an error related to the database occurs. Must be a subclass of Error.

DataError Triggered when an error occurs during data processing, such as division by zero error, data out of range, etc. Must be a subclass of DatabaseError.

OperationalError refers to errors that are not controlled by the user, but occur when operating the database. For example: the connection is unexpectedly disconnected, the database name is not found, transaction processing fails, memory allocation errors, etc. are errors that occur when operating the database. Must be a subclass of DatabaseError.

IntegrityError Errors related to integrity, such as foreign key check failure, etc. Must be a DatabaseError subclass.

InternalError Internal error of the database, such as cursor failure, transaction synchronization failure, etc. Must be a DatabaseError subclass.

ProgrammingError Programming error, such as the data table (table) is not found or already exists, SQL statement syntax error, wrong number of parameters, etc. Must be a subclass of DatabaseError.

NotSupportedError Not supported error refers to the use of functions or APIs that are not supported by the database. For example, the .rollback() function is used on the connection object, but the database does not support transactions or the transaction has been closed. Must be a subclass of DatabaseError.