YouTip

# Python3 MySQL Database Connection – PyMySQL Driver This tutorial introduces how to use Python3 with (https://github.com/PyMySQL/PyMySQL) to connect to a database and implement simple CRUD (Create, Read, Update, Delete) operations. ### What is PyMySQL? PyMySQL is a library used to connect to MySQL servers in Python3.x versions. In Python2, mysqldb was used instead. PyMySQL follows the Python Database API v2.0 specification and includes a pure-Python MySQL client library. * * * Before using PyMySQL, we need to ensure that PyMySQL is installed. PyMySQL download address: [https://github.com/PyMySQL/PyMySQL](https://github.com/PyMySQL/PyMySQL). If it is not yet installed, we can use the following command to install the latest version of PyMySQL: $ pip3 install PyMySQL If your system does not support the pip command, you can install it using the following methods: 1. Use the git command to download and install the package (you can also download it manually): $ git clone https://github.com/PyMySQL/PyMySQL $ cd PyMySQL/ $ python3 setup.py install 2. If you need to specify a version number, you can use the curl command to install: $ # X.X is the version number of PyMySQL $ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz $ cd PyMySQL* $ python3 setup.py install $ # Now you can delete the PyMySQL* directory **Note:** Please ensure you have root privileges to install the above modules. > During the installation process, you might encounter an "ImportError: No module named setuptools" error message, which means you haven't installed setuptools. You can visit [https://pypi.python.org/pypi/setuptools](https://pypi.python.org/pypi/setuptools) to find installation methods for various systems. > > > Linux system installation example: > > $ wget https://bootstrap.pypa.io/ez_setup.py $ python3 ez_setup.py * * * ## 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 has fields: FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME. * The username used to connect to the TESTDB database is "testuser", and the password is "test123". You can set your own or directly use the root username and its password. For MySQL database user authorization, please use the Grant command. * The Python **pymysql** module is installed on your machine. * If you are not familiar with SQL statements, you can visit our (#). ### Example: The following example connects to the TESTDB database in MySQL: ## Example (Python 3.0+) import pymysql db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB')cursor = db.cursor()cursor.execute("SELECT VERSION()")data = cursor.fetchone()print("Database version : %s " % data)db.close() Executing the above script produces the following output: Database version : 5.5.20-log * * * ## Creating Database Tables If a database connection exists, we can use the execute() method to create a table in the database. The following example creates the EMPLOYEE table: ## Example (Python 3.0+) import pymysql db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB')cursor = db.cursor()cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")sql = """CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )"""cursor.execute(sql)db.close() * * * ## Database Insert Operation The following example uses the SQL INSERT statement to insert records into the EMPLOYEE table: ## Example (Python 3.0+) import pymysql db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB')cursor = db.cursor()sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""try: cursor.execute(sql)db.commit()except: db.rollback()db.close() The above example can also be written in the following form: ## Example (Python 3.0+) import pymysql db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB')cursor = db.cursor()sql = "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('%s', '%s', %s, '%s', %s)" % ('Mac', 'Mohan', 20, 'M', 2000)try: cursor.execute(sql)db.commit()except: db.rollback()db.close() 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 Python queries MySQL using the fetchone() method to get a single row of data, and the fetchall() method to get multiple rows of data. * **fetchone():** This method fetches the next row of a query result set. The result set is an object. * **fetchall():** Receives all the returned result rows. * **rowcount:** This is a read-only attribute and returns the number of rows affected after executing the execute() method. ### Example: Query all data from the EMPLOYEE table where the salary (INCOME) field is greater than 1000: ## Example (Python 3.0+) import pymysql db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB')cursor = db.cursor()sql = "SELECT * FROM EMPLOYEE WHERE INCOME > %s" % (1000)try: cursor.execute(sql)results = cursor.fetchall()for row in results: fname = rowlname = rowage = rowsex = rowincome = rowprint("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % (fname, lname, age, sex, income))except: print("Error: unable to fetch data")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 data in a data table. The following example increments the AGE field by 1 for all records in the TESTDB table where SEX is 'M': ## Example (Python 3.0+) import pymysql db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB')cursor = db.cursor()sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')try: cursor.execute(sql)db.commit()except: db.rollback()db.close() * * * ## Delete Operation The delete operation is used to delete data from a data table. The following example demonstrates deleting all data from the EMPLOYEE table where AGE is greater than 20: ## Example (Python 3.0+) import pymysql db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB')cursor = db.cursor()sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)try: cursor.execute(sql)db.commit()except: db.rollback()db.close() * * * ## Executing Transactions Transaction mechanisms can ensure data consistency. Transactions should have four properties: atomicity, consistency, isolation, and durability. These four properties are commonly referred to as the ACID properties. * Atomicity. A transaction is an indivisible unit of work. All operations within the transaction must either all be done or none at all. * Consistency. A transaction must transform the database from one consistent state to another consistent state. Consistency is closely related to atomicity. * Isolation. The execution of one transaction must not be interfered with by other transactions. That is, the operations and data used within one transaction are isolated from concurrent other transactions, and concurrent transactions cannot interfere with each other. * Durability. Durability, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Subsequent other operations or failures should not have any impact on it. The Python DB API 2.0 provides two methods for transactions: commit or rollback. ### Example ## Example (Python 3.0+) sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)try: cursor.execute(sql)db.commit()except: db.rollback() For databases that support transactions, in Python database programming, an implicit database transaction automatically begins when the cursor is established. The commit() method commits all update operations of the cursor, and the rollback() method rolls back all operations of the current cursor. Each method starts a new transaction. * * * ## Error Handling The DB API defines some errors and exceptions for database operations. The following table lists these errors and exceptions: | Exception | Description | | --- | --- | | Warning | Triggered when there is a serious warning, such as data truncation during insertion, etc. Must be a subclass of StandardError. | | Error | All other error classes besides warnings. Must be a subclass of StandardError. | | InterfaceError | Triggered when an error occurs in the database interface module itself (not a database error). 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, data out of range, etc. Must be a subclass of DatabaseError. | | OperationalError | Refers to errors that are not user-controlled but occur during database operations. For example, unexpected disconnection, database name not found, transaction processing failure, memory allocation error, etc. Must be a subclass of DatabaseError. | | IntegrityError | Errors related to integrity, such as foreign key check failure, etc. Must be a subclass of DatabaseError. | | InternalError | Internal database errors, such as cursor failure, transaction synchronization failure, etc. Must be a subclass of DatabaseError. | | ProgrammingError | Programming errors, such as table not found or already exists, SQL syntax error, incorrect number of parameters, etc. Must be a subclass of DatabaseError. | | NotSupportedError | Unsupported error, referring to the use of functions or APIs not supported by the database. For example, using the .rollback() function on a connection object when the database does not support transactions or the transaction is already closed. Must be a subclass of DatabaseError. | The inheritance structure of the exceptions is as follows: Exception|__Warning |__Error |__InterfaceError |__DatabaseError |__DataError |__OperationalError |__IntegrityError |__InternalError |__ProgrammingError |__NotSupportedError