Example of the usage of the MySQLdb module in python

2020-04-02 14:19:00
OfStack

This article illustrates the use of the MySQLdb module in python. Share with you for your reference. Specific usage analysis is as follows:

In fact, MySQLdb is a bit like PHP or asp connection database in a mode, but MySQLdb is connected to the interface for mysql, we can connect to MySQLdb in python to achieve a variety of data operations.

The scheme of python connection to mysql has oursql, PyMySQL, myconnpy, mysql Connector, etc., but this article is really another class library MySQLdb, MySQLdb is used for python connection to mysql database interface, it realizes python database API specification V2.0, based on mysql C API established. From: https://pypi.python.org/pypi/MySQL-python to get and install, and many of the distribution in the Linux source has the module, can be installed directly through the source.

Database connection

MySQLdb provides the connect method to establish a connection with the database, receive several parameters, and return the connection object:

conn=MySQLdb.connect(host="localhost",user="root",passwd="jb51",db="test",charset="utf8")

Commonly used parameters include:
Host: database hostname. The default is to use localhost
User: database login name. The default is the current user
Passwd: database login secret. Default is empty
Db: database name to use. No default value
Port: the TCP port used by the MySQL service. The default is 3306
Charset: database encoding
For more information about parameter can check here http://mysql-python.sourceforge.net/MySQLdb.html

Then, this connection object also provides support for transaction operations, in the standard way:
Commit ()
Rollback () rolled back

Take a look at a simple query example:

#!/usr/bin/python

# encoding: utf-8

import MySQLdb

#  Open database connection 

db = MySQLdb.connect("localhost","root","361way","test" )

#  use cursor() Method to get the operation cursor 

cursor = db.cursor()

#  use execute Methods to perform SQL statements 

cursor.execute("SELECT VERSION()")

#  use  fetchone()  Method to get a database. 

data = cursor.fetchone()

print "Database version : %s " % data

#  Close the database connection 

db.close()

The script execution results are as follows:
The Database version: 5.5.40

Cursor method execution and return value

The cursor method provides two types of operations: 1. Execute a command and 2. Receive a return value.
Cursor is the method used to execute commands

//Used to execute a stored procedure. The parameters received are the stored procedure name and parameter list, and the return value is the number of affected rows 

callproc(self, procname, args)

//Execute a single SQL statement, receiving the parameters of the SQL statement itself and the list of parameters used, and returning the value of the number of affected rows 

execute(self, query, args)

//Execute the one-on-one hit SQL statement, but repeat the arguments in the parameter list, returning the number of affected rows 

executemany(self, query, args)

//Move to the next result set 

nextset(self)

cursor The method used to receive the return value 

//Receive all returned rows.

fetchall(self)

//Receive the size bar to return the result row. If the value of the size is greater than the number of result rows returned, the cursor. Arraysize bar data 
 is returned
fetchmany(self, size=None)

//Returns a result row 

fetchone(self)

//Move the pointer to a row. If mode='relative', move the value bar from the current row, and if mode='absolute', move the value bar 
 from the first row of the result set
scroll(self, value, mode='relative')

//This is a read-only property and returns the number of lines affected by the execute() method 

rowcount

Three, database operation

1. Create database tables
If the database connection exists, we can use the execute() method to create the table for the database, as shown below:

#!/usr/bin/python

# encoding: utf-8

import MySQLdb

#  Open database connection 

db = MySQLdb.connect("localhost","root","361way","test" )

#  use cursor() Method to get the operation cursor 

cursor = db.cursor()

#  If the data table is already in use  execute()  Method to drop the table. 

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

#  Create data tables SQL statements 

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()

2. Database insert operation

#!/usr/bin/python

# encoding: utf-8

import MySQLdb

#  Open database connection 

db = MySQLdb.connect("localhost","root","361way","test" )

#  use 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:

   #  perform sql statements 

   cursor.execute(sql)

   #  Commit to the database for execution 

   db.commit()

except:

   # Rollback in case there is any error

   db.rollback()

#  Close the database connection 

db.close()

Here is an example of a single SQL execution, and readers interested in the use of cursor.executemany can refer to the related aws host asset management system example.
The above example can also be written as follows:

#!/usr/bin/python

# encoding: utf-8

import MySQLdb

#  Open database connection 

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

#  use 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:

   #  perform sql statements 

   cursor.execute(sql)

   #  Commit to the database for execution 

   db.commit()

except:

   #  Roll back when an error occurs 

   db.rollback()

#  Close the database connection 

db.close()

Parameters can also be passed as variables, as follows:

..................................

user_id = "test"

password = "password123"

con.execute('insert into Login values("%s", "%s")' % 

             (user_id, password))

..................................

3. Database query operation
For example, query for all data in the EMPLOYEE table where the salary (salary) field is greater than 1000:

#!/usr/bin/python

# encoding: utf-8

import MySQLdb

#  Open database connection 

db = MySQLdb.connect("localhost","root","361way","test" )

#  use cursor() Method to get the operation cursor 

cursor = db.cursor()

# SQL  The query 

sql = "SELECT * FROM EMPLOYEE 

       WHERE INCOME > '%d'" % (1000)

try:

   #  perform SQL statements 

   cursor.execute(sql)

   #  Gets 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=%d,sex=%s,income=%d" % 

             (fname, lname, age, sex, income )

except:

   print "Error: unable to fecth data"

#  Close the database connection 

db.close()

The execution results of the above script are as follows:
Fname =Mac, lname=Mohan, age=20, sex=M, income=2000

4. Database update operation
The update operation is used to update the data of the data table. The following example changes all the SEX fields in the test table to 'M', and the AGE field is incremented by 1:

# encoding: utf-8

#!/usr/bin/python

import MySQLdb

#  Open database connection 

db = MySQLdb.connect("localhost","root","361way","test" )

#  use 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:

   #  perform SQL statements 

   cursor.execute(sql)

   #  Commit to the database for execution 

   db.commit()

except:

   #  Roll back when an error occurs 

   db.rollback()

#  Close the database connection 

db.close()

5. Execute transactions

The transaction mechanism ensures data consistency.
Transactions should have four properties: atomicity, consistency, isolation, and persistence. These four properties are commonly referred to as ACID properties.
(1) atomicity. A transaction is an indivisible unit of work in which all operations are either done or not done.
Consistency. A 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 interrupted by other transactions. That is, the operation within a transaction and the data used are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.
Durability. Permanence, also known as permanence, refers to the fact 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.

Transactions in Python DB API 2.0 provide two method commit or rollback. Example:

# SQL Delete record statement 

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

try:

   #  perform SQL statements 

   cursor.execute(sql)

   #  Commit to the database 

   db.commit()

except:

   #  Roll back when an error occurs 

   db.rollback()

For transaction-enabled databases, in Python database programming, an invisible database transaction starts automatically when the cursor is set up. All update operations for the commit() method cursor, and the rollback () method rolls back all operations for the current cursor. Each method starts a new transaction.

I hope this article has helped you with your Python programming.