Let us learn how to perform a Python database connection, and thereby, connecting your Python application to a MySQL database.
It is important for almost all the programming applications to connect to a backend database to be able to resolve real-life problems.
Python has, therefore, provides a standard database interface called Python DB API. Some of the databases that this API (application programming interface) supports are as follows:
- Microsoft SQL Server
Python has different DB – APIs for each of these different databases. You have to select and download the one you need to connect to.
What Does Python DB – API Include?
The Python DB API includes several operations that are generally used while creating a connection from the Python application with a MySQL server.</p.
- Import Python DB – API
- Create a database connection
- Execute SQL queries and stored procedures
- Close the database connection
- Python Database Interfaces
Also Read: Python Range Function
Python DB Interface Types
- Generic database Interface:
Python’s primary database API is Python DB – API which is a generic one and is supported by most of the databases.
- Relational database interface:
This API is particularly for databases which do not fall into generic API capabilities. It takes a relational DBMS approach rather than the generic database approach.
How To Connect Python with MySQL Database?
MySQLdb is an application programming interface which enables you to connect a Python program with a database server, and it is built on top MySQL C API.
To perform the connection, open Python Shell, and type the following command
>> import MySQLdb
If the above statement executes successfully, then you can go ahead with creating a database connection, and executing SQL queries.
The import statement may give the following error
>> import MySQLdb Traceback (most recent call last): File "<pyshell#1>", line 1, in import MySQLdb ModuleNotFoundError: No module named 'MySQLdb'
Python DB – API module is not installed on your machine if you get this error. You can download the MySQL database connector for Python from SourceForge.
Alternatively, you can also download and install Python MySQL connector from their official website – MySQL/Python Connector.
Once you’ve successfully established the Python database connection with the MySQL server, you can now start writing the following Python code to check the database connectivity and perform the basic CRUD operations on the database.
Python Database Connection To MySQL Program
Here’s a sample code to check the version of the MySQL database server that the Python program connects to.
import MySQLdb conn = MySQLdb.connect("localhost", "username", "password","sample_db") cursor = conn.cursor() cursor.execute("SELECT VERSION()") x = cursor.fetchone() print "The MySQL database server version is:", x cursor.close() conn.close()
This is how we can connect to the MySQL server from a Python application. Let us now see the different functions available to perform the CRUD operations.
MySQL Database Operations
This query enables you to create a table within the SQL database server, and this is the starting point for the following operations to occur.
The Read query enables you to read and display the data available in a MySQL table.
This SQL statement updates the existing data within a table. Therefore, you can update multiple rows simultaneously as well.
This SQL query helps in deleting the records from a table.
This is a SQL query to insert records into a MySQL table. It is, however, mandatory that you should first define the table with Create statement and use the INSERT query.
If you face any issues while establishing a Python database connection, please drop in your thoughts in the comment section. We’d be happy to help.