![\"Python](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/07\/Database-Access-with-Python-3-01.jpg\")
<\/a>Python Database Access – Python 3 MySQL<\/p><\/div>\n
What is Python Database Access?<\/h3>\n
A database is a collection of tables related to each other via columns. For most real-world projects, a database is a must.<\/p>\n
We can use SQL (Structured Query Language) to create, access, and manipulate data.We can also make use of normalization to avoid redundancy of data.<\/p>\n
For database programming, Python supports many database servers-<\/p>\n
MySQL, Oracle, PostgreSQL, SQLite, Sybase, Microsoft SQL Server, mSQL, Microsoft Access, and many more.<\/p>\n
It also supports Data Query Statements, Data Definition Language (DDL), and Data Manipulation Language (DML).<\/p>\n
The standard database interface for Python is Python DB-API. For that, we have the module MySQLdb for MySQL.<\/p>\n
This is independent of database engines, so we can write Python scripts to access any database engine. However, this isn\u2019t compatible with Python 3.<\/p>\n
So, in this Python Database Access tutorial, we use the module PyMySQL.<\/p>\n
Advantages of Database Programming with Python<\/h4>\n
With Python, we have the following benefits:<\/p>\n
- \n
- Platform-independent<\/li>\n
- Faster and more efficient<\/li>\n
- Portable<\/li>\n
- Support for relational database systems<\/li>\n
- Easy to migrate and port database application interfaces<\/li>\n
- Support for SQL cursors<\/li>\n
- It handles open and closed connections<\/li>\n<\/ul>\n
Disadvantages of Database Programming in Python:<\/h4>\n
- \n
- Speed:<\/strong> Python is slower than other languages as it reads tasks line by line. So, it’s preferred less for high-speed tasks.<\/li>\n
- Memory usage:<\/strong> Python uses a lot of RAM.<\/li>\n
- Late errors:<\/strong> Python does not check the bugs before running it, which might crash the database connection while running it.<\/li>\n
- Simpler tools:<\/strong> To perform huge or complex tasks, some people find Python’s built-in database tools very basic.<\/li>\n<\/ul>\n
PyMySQL and Installation<\/h3>\n
PyMySQL implements the Python Database API 2.0. In this Python Database tutorial, we will use it to connect to a MySQL database server from Python.<\/p>\n
We have the following requirements to install PyMySQL-<\/p>\n
1. Python (any of)<\/strong><\/p>\n
- \n
- CPython>=2.6 or >=3.3<\/li>\n
- PyPy>=4.0<\/li>\n
- IronPython 2.7<\/li>\n<\/ul>\n
2. MySQL(any of)<\/strong><\/p>\n
- \n
- MySQL>=4.1<\/li>\n
- MariaDB>=5.1<\/li>\n<\/ul>\n
To install it, run the following command in the command prompt-<\/p>\n
C:\\Users\\lifei>pip install PyMySQL\r\nCollecting PyMySQL<\/pre>\n
Using cached https:\/\/files.pythonhosted.org\/packages\/2f\/be\/4310bb405eb83b615cf9bd4501942d9ff000d8b9372ce84e920facbf5c36\/PyMySQL-0.9.0-py2.py3-none-any.whl
\nCollecting cryptography (from PyMySQL)
\nDownloading https:\/\/files.pythonhosted.org\/packages\/67\/62\/67faef32908026e816a74b4b97491f8b9ff393d2951820573599c105cc32\/cryptography-2.2.2-cp36-cp36m-win_amd64.whl (1.3MB)
\n100% |\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 1.3MB 596kB\/s
\nCollecting idna>=2.1 (from cryptography->PyMySQL)
\nDownloading https:\/\/files.pythonhosted.org\/packages\/4b\/2a\/0276479a4b3caeb8a8c1af2f8e4355746a97fab05a372e4a2c6a6b876165\/idna-2.7-py2.py3-none-any.whl (58kB)
\n100% |\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 61kB 1.3MB\/s
\nCollecting asn1crypto>=0.21.0 (from cryptography->PyMySQL)
\nUsing cached https:\/\/files.pythonhosted.org\/packages\/ea\/cd\/35485615f45f30a510576f1a56d1e0a7ad7bd8ab5ed7cdc600ef7cd06222\/asn1crypto-0.24.0-py2.py3-none-any.whl
\nCollecting six>=1.4.1 (from cryptography->PyMySQL)
\nUsing cached https:\/\/files.pythonhosted.org\/packages\/67\/4b\/141a581104b1f6397bfa78ac9d43d8ad29a7ca43ea90a2d863fe3056e86a\/six-1.11.0-py2.py3-none-any.whl
\nCollecting cffi>=1.7; platform_python_implementation != “PyPy” (from cryptography->PyMySQL)
\nDownloading https:\/\/files.pythonhosted.org\/packages\/2f\/85\/a9184548ad4261916d08a50d9e272bf6f93c54f3735878fbfc9335efd94b\/cffi-1.11.5-cp36-cp36m-win_amd64.whl (166kB)
\n100% |\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 174kB 568kB\/s
\nCollecting pycparser (from cffi>=1.7; platform_python_implementation != “PyPy”->cryptography->PyMySQL)
\nUsing cached https:\/\/files.pythonhosted.org\/packages\/8c\/2d\/aad7f16146f4197a11f8e91fb81df177adcc2073d36a17b1491fd09df6ed\/pycparser-2.18.tar.gz
\nInstalling collected packages: idna, asn1crypto, six, pycparser, cffi, cryptography, PyMySQL
\nRunning setup.py install for pycparser … done
\nSuccessfully installed PyMySQL-0.9.0 asn1crypto-0.24.0 cffi-1.11.5 cryptography-2.2.2 idna-2.7 pycparser-2.18 six-1.11.0
\nAlso, make sure to install a database server on your machine. In this tutorial, we use MySQL. We download it from here-
\ndev.mysql.com\/downloads\/mysql<\/a><\/strong><\/p>\nConnecting Python Database<\/h3>\n
Now that you\u2019ve installed everything, let\u2019s begin connecting to the database. Let\u2019s create a database first.<\/p>\n
1. How to Create a Python Database?<\/h4>\n
mysql> create database demo;<\/strong>
\nQuery OK, 1 row affected (0.21 sec)
\nmysql> use demo;<\/strong>
\nDatabase changed
\nmysql> create user ‘ayushi’@’localhost’ IDENTIFIED BY ‘yourpassword’<\/strong>
\n\u00a0\u00a0\u00a0 -> ;<\/strong>
\nQuery OK, 0 rows affected (0.21 sec)
\nmysql> grant all on demo.* to ‘ayushi’@’localhost’;<\/strong>
\nQuery OK, 0 rows affected (0.22 sec)
\nmysql> create table student(fname varchar(20), lname varchar(20), age int, enrolment_no varchar(12));<\/strong>
\nQuery OK, 0 rows affected (0.62 sec)<\/p>\n2. How to Connect to a Python Database?<\/h4>\n
>>> import pymysql\r\n>>> db=pymysql.connect(\"localhost\",\"ayushi\",\"yourpassword\",\"demo\") #This saves a connection object into db\r\n>>> cursor=db.cursor()\r\n>>> cursor.execute(\"SELECT VERSION()\")\r\n<\/pre>\n
Output<\/strong><\/p>\n
1<\/div>\n>>> print(f\"You're running version {cursor.fetchone()}\")<\/pre>\nOutput<\/strong><\/p>\n
You’re running version (‘8.0.11’,)<\/div>\n>>> db.close() #Closing the database connection<\/pre>\n
A cursor is an object that submits different SQL statements to the database server. A cursor returns a result set object<\/p>\n
How to Create Tables in Python Database?<\/h3>\n
Now, let\u2019s take a look at all operations one by one, starting with creating a table.<\/p>\n
>>> import pymysql\r\n>>> db=pymysql.connect(\"localhost\",\"ayushi\",\"yourpassword\",\"demo\")\r\ncaching sha2: succeeded by fast path.\r\n>>> cursor=db.cursor()\r\n>>> cursor.execute(\"DROP TABLE IF EXISTS student\") #This drops the table and replaces it\u00a0\r\n>>>\u00a0query=\"\"\"CREATE\u00a0TABLE\u00a0student(\r\nfname\u00a0VARCHAR(20),\u00a0lname\u00a0VARCHAR(20),\r\nage\u00a0INT,\u00a0enrolment_no\u00a0VARCHAR(12))\"\"\"\r\n>>>\u00a0cursor.execute(query)\r\n>>>\u00a0db.close()<\/pre>\n
How to Insert a Record in a Python Database?<\/h3>\n
Let\u2019s try inserting a record in \u2018student\u2019.<\/p>\n
>>> import pymysql\r\n>>> db=pymysql.connect(\"localhost\",\"ayushi\",\"yourpassword\",\"demo\")\r\ncaching sha2: succeeded by fast path.\r\n>>> cursor=db.cursor()\r\n>>> query='INSERT INTO student VALUES(\"Ayushi\",\"Sharma\",22,\"0812CS141028\")'\r\n>>> try:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cursor.execute(query)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db.commit() #Commit writing to the database\r\nexcept:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db.rollback() #Rollback the transaction if not complete\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n1\r\n>>> db.close()<\/pre>\n
Let\u2019s check if this makes any changes to the database. In the command prompt:<\/p>\n
mysql> select * from student;\r\n+--------+--------+------+--------------+\r\n| fname\u00a0 | lname\u00a0 | age\u00a0 | enrolment_no |\r\n+--------+--------+------+--------------+\r\n| Ayushi | Sharma |\u00a0\u00a0 22 | 0812CS141028 |\r\n+--------+--------+------+--------------+\r\n1 row in set (0.00 sec)<\/pre>\n
How to Read Records in a Python Database?<\/h3>\n
Now, how can we fetch values from a database?<\/p>\n
Let\u2019s take an example to fetch records of students from \u2018student\u2019 who are older than 22.<\/p>\n
We have added another record for this purpose.<\/p>\n
>>> import pymysql\r\n>>> db=pymysql.connect(\"localhost\",\"ayushi\",\"yourpassword\",\"demo\")\r\ncaching sha2: succeeded by fast path.\r\n>>> cursor=db.cursor()\r\n>>> query=\"select * from student where age>22\"\r\n>>> try:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cursor.execute(query)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 resultset=cursor.fetchall() #To fetch all records that satisfy\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for record in resultset:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 fname=record[0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lname=record[1]\r\n\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 age=record[2]\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 enrolment_no=record[3]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 print(f\"Student: {fname} {lname}; Enrolment: {enrolment_no}; Age: {age}\")\r\nexcept:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 print(\"Sorry, we encountered a problem\")\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\r\n1\r\nStudent: Megha Sharma; Enrolment: 0812CS141015; Age: 24\r\n>>> db.close()<\/pre>\nWe have the following methods and attributes-<\/p>\n
- \n
- fetchone()<\/strong>– This fetches the immediate next row from the result set of the query.<\/li>\n
- fetchall()<\/strong>– This fetches the entire result set; it will exclude the records already extracted.<\/li>\n
- rowcount<\/strong>– This is an attribute. It returns an integer denoting the number of records that a call to execute() affected.<\/li>\n<\/ul>\n
How to Update Records in a Python Database?<\/h3>\n
To update an existing record, we can simply use an SQL query for the same.<\/p>\n
>>> import pymysql\r\n>>> db=pymysql.connect(\"localhost\",\"ayushi\",\"yourpassword\",\"demo\")\r\ncaching sha2: succeeded by fast path.\r\n>>> cursor=db.cursor()\r\n>>> query=\"update student set age=age+1 where age<=22\"\r\n>>> try:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cursor.execute(query)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db.commit()\r\nexcept:\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db.rollback()\r\n1\r\n>>> db.close()<\/pre>\n
Let\u2019s see if this has made any changes to the actual database. In your command prompt:<\/p>\n
mysql> select * from student;\r\n+--------+--------+------+--------------+\r\n| fname\u00a0 | lname \u00a0| age\u00a0 | enrolment_no |\r\n+--------+--------+------+--------------+\r\n| Ayushi | Sharma |\u00a0\u00a0 23 | 0812CS141028 |\r\n| Megha\u00a0 | Sharma |\u00a0\u00a0 24 | 0812CS141015 |\r\n+--------+--------+------+--------------+\r\n2 rows in set (0.00 sec)<\/pre>\n
How to Delete Records in a Python Database?<\/h3>\n
We can also delete records from a database using Python.<\/p>\n
>>> import pymysql\r\n>>> db=pymysql.connect(\"localhost\",\"ayushi\",\"swaysway7!\",\"demo\")\r\ncaching sha2: succeeded by fast path.\r\n>>> cursor=db.cursor()\r\n>>> query=\"delete from student where age>23\"\r\n>>> try:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cursor.execute(query)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db.commit()\r\nexcept:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db.rollback()\r\n1\r\n>>> db.close()<\/pre>\n
And in the command prompt:<\/p>\n
mysql> select * from student;\r\n+--------+--------+------+--------------+\r\n| fname\u00a0 | lname\u00a0 | age\u00a0 | enrolment_no |\r\n+--------+--------+------+--------------+\r\n| Ayushi | Sharma |\u00a0\u00a0 23 | 0812CS141028 |\r\n+--------+--------+------+--------------+\r\n1 row in set (0.00 sec)<\/pre>\n
Commit, Rollback, and Disconnecting in Python Database<\/h3>\n
A commit command tells the database to finalize the write to the database. A rollback lets us revert changes and get back to a previous state.<\/p>\n
For committing, you can use commit(), and for rollback, you can use rollback().<\/p>\n
After we\u2019re done working with the database, we should close the database to release resources. We use close() for this.<\/p>\n
If you don\u2019t get any of this, we suggest reading up on the basic properties of transactions in databases.<\/p>\n
Errors in Transactions in Python Database<\/h3>\n
When holding a transaction, you may come across ten different kinds of errors:<\/p>\n
- fetchall()<\/strong>– This fetches the entire result set; it will exclude the records already extracted.<\/li>\n
- fetchone()<\/strong>– This fetches the immediate next row from the result set of the query.<\/li>\n
- Memory usage:<\/strong> Python uses a lot of RAM.<\/li>\n
- Speed:<\/strong> Python is slower than other languages as it reads tasks line by line. So, it’s preferred less for high-speed tasks.<\/li>\n