Using python to operate mysql Databases

Using python to operate mysql Databases
Using python to operate mysql Databases

Python standard database interfaces are Python DB-API, and Python DB-API provides developers with database application programming interfaces.

The Python database interface supports many databases. You can select a database suitable for your project:

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

  You can access the Python database interface and API to view the detailed list of supported databases.

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

  DB-API is a specification that defines a series of required object and database access methods to provide consistent access interfaces for a variety of underlying database systems and a variety of database interface programs.

  Python DB-API, for the majority of the database implementation interface, using it to connect to the database, you can use the same way to operate the database.

  Python DB-API use process:

  • Introduce the API module.
  • Obtain the connection to the database.
  • Execute SQL statements and stored procedures.
  • Close the database connection. What is MySQLdb?

    MySQLdb is an interface used to connect Python to the Mysql database. It implements the Python database API Specification V2.0 and is created based on MySQL c api.

    How to install MySQLdb?

    To write a MySQL script with a DB-API, make sure that MySQL is installed. Copy and execute the following code:

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

    If the output result 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 
         
              import MySQLdbImportError: No module named MySQLdb
         

    Install MySQLdb, visit the http://sourceforge.net/projects/mysql-python (Linux platform access: https://pypi.python.org/pypi/MySQL-python) from here you can choose the installation package that fits your platform, divided into pre-compiled binary files and source code installation packages.

    If you select the binary file release version, the installation process will be completed with the installation prompt. If you install the SDK from the source code, you need to switch to the top-level directory of the MySQLdb release, and enter 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: Make sure that you have the root permission to install the above modules.

    Database Connection

    Before connecting to the database, confirm the following:

    • You have created the database TESTDB.
    • In the TESTDB database, you have created the table "EMPLOYEE ".
    • The fields in the EMPLOYEE table 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 user name and password. For Mysql database user authorization, use the Grant command.
    • Python MySQLdb module has been installed on your host.
    • If you are not familiar with SQL statements, visit our basic SQL tutorial instance:

      The following example links to the TESTDB database of Mysql:

      #! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation cursor = db. cursor () # Use execute to execute SQL sentence cursor.exe cute ("SELECT VERSION ()") # Use the fetchone () method to obtain a database. Data = cursor. fetchone () print "Database version: % s" % data # close Database connection db. close ()

      The output result is 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, as shown below:

      #! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation cursor = db. cursor () # If the data table already exists, use the execute () method to delete the table. Cursor.exe cute ("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.exe cute (SQL) # disable database connection to db. close ()

      Database insert operation

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

      #! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation 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 the SQL statement cursor.exe cute (SQL) # submit it to the database to execute the db. commit () handle T: # Rollback in case there is any error db. rollback () # disable database connection to db. close ()

      The preceding example can also be written as follows:

      #! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation 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: # Run the SQL statement cursor.exe cute (SQL) # submit it to the database to execute the database. commit () commit T: # roll back the db when an error occurs. rollback () # disable database connection to db. close ()

      Instance:

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

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

      Database query operations

      For Python query, Mysql uses 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 returned results.
      • Rowcount:This is a read-only attribute and returns the number of rows affected by execution of the execute () method. Instance:

        Query all the data with the salary (salary) Field greater than 1000 in the EMPLOYEE table:

        #! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation cursor = db. cursor () # SQL query statement SQL = "SELECT * from employee \ where income> '% d'" % (1000) try: # Run the SQL statement cursor.exe cute (SQL) # retrieve the 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) failed T: print" Error: unable to fecth data "# disable database connection to db. close ()

        The execution result of the above script is as follows:

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

        Database update operations

        The update operation is used to update data in the data table. The following instance modifies all the SEX fields in the TESTDB table to 'M', and the AGE field increments by 1:

        #! /Usr/bin/python #-*-coding: UTF-8-*-import MySQLdb # Open the database connection db = MySQLdb. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation cursor = db. cursor () # SQL update statement SQL = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '% C'" % ('M') try: # Run the SQL statement cursor.exe cute (SQL) # submit it to the database to execute the database. commit () commit T: # roll back the db when an error occurs. rollback () # disable database connection to db. close ()

        Execute transactions

        The transaction mechanism ensures data consistency.

        Transactions should have four attributes: atomicity, consistency, isolation, and persistence. These four attributes are generally called ACID properties.

        • Atomicity ). A transaction is an inseparable unit of work. All operations involved in a transaction are either done or not done.
        • Consistency ). Transactions must change the database from one consistent state to another. Consistency is closely related to atomicity.
        • Isolation ). The execution of a transaction cannot be disturbed by other transactions. That is to say, the operations and data used within a transaction are isolated from other concurrent transactions, and the transactions executed concurrently cannot interfere with each other.
        • Durability ). Permanence refers to a transaction that changes the data in the database once committed. Other subsequent operations or faults should not have any impact on them.

          The transaction of Python db api 2.0 provides two methods: commit or rollback.

          Instance:

          # SQL delete record statement SQL = "delete from employee where age> '% d'" % (20) try: # Execute SQL statement cursor.exe cute (SQL) # submit the database. commit () commit T: # roll back the db when an error occurs. rollback ()

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

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

          Error Handling

          Db api defines database operation errors and exceptions. The following table lists these errors and exceptions:

          Exception	Description
          Warning	Triggered when a severe warning occurs, for example, data insertion is truncated. Must be a subclass of StandardError.
          Error	Warning. Must be a subclass of StandardError.
          InterfaceError	This is triggered when an error occurs in the database interface module rather than in the database. Must be a subclass of Error.
          DatabaseError	Database-related errors are triggered. Must be a subclass of Error.
          DataError	This function is triggered when an error occurs during data processing, for example, Division by zero error or data out of range. Must be a subclass of DatabaseError.
          OperationalError	An error occurs when operating the database instead of being controlled by the user. For example, database operations such as accidental disconnection, database name not found, transaction processing failure, and memory allocation error occur. Must be a subclass of DatabaseError.
          IntegrityError	Integrity-related errors, such as foreign key check failure. Must be a subclass of DatabaseError.
          InternalError	Internal database errors, such as cursor failures and transaction synchronization failures. Must be a subclass of DatabaseError.
          ProgrammingError	Program errors, such as data table not found or already exists, SQL statement syntax errors, parameter quantity errors, and so on. Must be a subclass of DatabaseError.
          NotSupportedError	An error is not supported. It indicates that a function or API not supported by the database is used. For example, you can use the. rollback () function on the connection object. However, the database does not support transactions or the transaction has been closed. Must be a subclass of DatabaseError.