PythonPython3java
 

Python operation MySQL database

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

The Python database interface supports a lot of databases, you can choose the database that is right for your project:

  • GadFly
  • mSQL
  • MySQL
  • PostgreSQL
  • Microsoft SQL Server 2000
  • Informix
  • Interbase
  • Oracle
  • Sybase

Different databases You need to download different DB API modules. 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 set of required objects and database access methods to provide a consistent access interface for a wide variety of underlying database systems and a wide variety of database interface programs.

Python's DB-API implements an interface for most databases. Once you connect to each database, you can manipulate the databases in the same way.

Python DB-API usage process:

  • Introduction of API modules.
  • Get a connection to the database.
  • Execute SQL statements and stored procedures.
  • Close the database connection.

What is MySQLdb?

MySQLdb is an interface for Python to link Mysql databases. It implements the Python Database API Specification V2.0, built on the MySQL C API.


How do I install MySQLdb?

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

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

import MySQLdb

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

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

To install MySQLdb, please visit http://sourceforge.net/projects/mysql-python (Linux platform can access: https://pypi.python.org/pypi/MySQL-python) From here you can choose the installation package for your platform, divided into pre-compiled binaries and source code installation packages.

If you choose a binary distribution, the installation process can be completed with a basic installation prompt. If you are installing from source, you will 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 privileges to install the above modules.


Database Connection

Before connecting to the database, please confirm the following:

  • You have created the database TESTDB.
  • You have created the table EMPLOYEE in the TESTDB database
  • The EMPLOYEE table fields are FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
  • The username used by the connection database TESTDB is "testuser" and the password is "test123". You can set it yourself or use the root username and password directly. For the Mysql database user authorization, please use the Grant command.
  • The Python MySQLdb module is already installed on your machine.

Instance:

The following example links Mysql's TESTDB database:

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

import MySQLdb

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

# use cursor()Method to get the operation cursor 
cursor = db.cursor()

# use execute Method to execute SQL statement
cursor.execute("SELECT VERSION()")

# use fetchone() Method to get the operation cursor method to get a piece of data
data = cursor.fetchone()

print "Database version : %s " % data

# Close the database connection
db.close()

Execute the above script output as follows:

Database version : 5.0.45

Create a database table

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

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

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

# use cursor()Method to get the operation cursor 
cursor = db.cursor()

# If the data table already exists execute() Method delete table
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# If the data table already exists
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 the database connection
db.close()

Database insert operation

The following example uses the Execute SQL INSERT statement to insert a record into the table EMPLOYEE:

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

import MySQLdb

# Open database connection
db = MySQLdb.

connect("localhost", "testuser"


, "test123", "TESTDB", charset='utf8' )

 
    
  # Using 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 ('Mac', 'Mohan', 20, 'M', 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 example can also be written as follows:

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

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

# Using 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, %s, %s, %s )" % \ ('Mac', 'Mohan', 20, 'M', 2000) try: # Execute sql statement cursor.execute(sql) # Submit to the database for execution db.commit() except: # Roll back when an error occurs while closing the database connection db.rollback() # Close the database connection db.close()

Instance:

The following code uses variables to pass arguments to SQL statements:

..................................
user_id = "test123"
password = "password"

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

Database query operation

Python queries 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(): Receive all return rows.
  • rowcount: This is a read-only property and returns the number of rows affected after executing the execute() method.

Instance:

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

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

import MySQLdb

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

# Using cursor()Method to get the operation cursor 
cursor = db.cursor()
    
    
  # SQL check sentence
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 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 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 of the data table. The following example increments the AGE field of the SEX field of the EMPLOYEE table to 'M' 1:

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

import MySQLdb

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

# Using cursor()Method to get 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 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 the data in the data table. The following example demonstrates deleting all data in the data table EMPLOYEE with an AGE greater than 20:

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

# Open database connection
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )   
    
  # Using cursor()Method to get the operation cursor 
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 Connection
Db.close()

Executing a transaction

The transaction mechanism ensures data consistency.

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

  • Atomity. A transaction is an inseparable unit of work, and the operations involved in the transaction are either done or not done.
  • Consistency. The transaction must be to change the database from one consistent state to another. Consistency is closely related to atomicity.
  • Isolation. The execution of one transaction cannot be interfered with by other transactions. That is, the internal operations of a transaction and the data used are isolated from other transactions concurrently, and the transactions executed concurrently cannot interfere with each other.
  • 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 have any effect on them.

The Python DB API 2.0 transaction provides two methods commit or rollback.

Instance:

# 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, when a cursor is created, an invisible database transaction is automatically started.

Commit() method updates all updates to the cursor, and the rollback() method rolls back all operations of the current cursor. Every method starts a new transaction.


Error Handling

The DB API defines some database operation errors and exceptions. The following table lists these errors and exceptions:

ExceptionDescription
Warning Triggered when there is a serious warning, such as insert data is truncated, and so on. Must be a subclass of StandardError.
Error Warnings except for all other error classes. Must be a subclass of StandardError.
InterfaceError Triggered when an error occurs in the database interface module itself (instead of a database error). Must be a subclass of Error.
DatabaseError Triggered when an error related to the database occurred. Must be a subclass of Error.
DataError Triggered when an error occurs during data processing, such as divide-by-zero error, data out of range, and so on. Must be a subclass of DatabaseError.
OperationalError refers to non-user-controlled, but an error that occurs when operating the database. For example: the connection is accidentally disconnected, The database name is not found, the transaction failed, the memory allocation error, etc. The operation database is an error. Must be a subclass of DatabaseError.
IntegrityErrorIntegrity related errors, such as foreign key check failures. Must be a DatabaseError subclass.
InternalError An internal error in the database, such as a cursor failure, transaction synchronization failure, and so on. Must be a DatabaseError subclass.
ProgrammingErrorProgram error, such as data table (table) not found or already exists, SQL statement syntax error, The number of parameters is wrong and so on. Must be a subclass of DatabaseError.
NotSupportedError does not support errors, which means using functions or APIs that are not supported by the database. For example on a connection object Use the .rollback() function, however the database does not support transactions or the transaction is closed. Must be a subclass of DatabaseError.





welookups is optimized for learning.© welookups 2018 -
All Right Reserved and you agree to have read and accepted our term and condition