Python allows developers to interact with various relational databases (such as Oracle, SQLite, and MySQL) and relational database systems (RDBMS) through several different libraries, which must conform to standards defined in PEP 249.
In this database programming tutorial, we will discuss how to connect to a MySQL database using Python and make database queries. You can read more about the PEP 249 by visiting the Python Database API Specification.
How to Create a Database Connection in Python
To create a connection to a MySQL database in Python, developers first need to download a database connector, which is a module that will enable your Python scripts to interact with data. This tutorial will be using the MySQL database since it is one of the most popular and widely used databases.
Not familiar with MySQL or want to increase your MySQL development skills? We have a list of the Best Online Courses to Learn MySQL to help get you started.
To download the Python MySQL database connector, you can use PIP with the following command:
$ pip install mysql-connector-python
There are a number of other modules programmers can use to make database connections, such as PyMySQL, MySqlClient, and OurSQL. However, this programming tutorial uses the MySQL Python connector, which is officially supported by Oracle and is written purely in Python.
To begin working with this module, you will need to import it into your script using the following command:
import mysql.connector
Next, you need to create a connection to the database. There are two ways of doing this: you can either use the connect() function or use the MySQLConnection object.
How to Use the connect() Method in Python
Here is an example of of a connection string you can use to connect to a database named student:
conn = mysql.connector.connect( user='jane', password = 'my-password' , host = "192.168.5.7" , database = 'student')
There are four key arguments that a programmer needs to connect to a database:
- user: The username used to connect to a database
- password: The users password
- host: IP address of the host where the database is hosted. Note that the default is 127.0.0.1 (localhost)
- database: Name of the database you are trying to connect to
You may be wondering why there is no argument for your MySQL server’s port. In actuality, there is, and the default is 3306. In case you set your server to use another port you can use the port argument.
The connect() does have more optional arguments that you can read about in the official Connector/Python documentation.
How to Use MySQLConnection to Connect to a Database
Here is some example code showing how to use Python and the MySQLConnection object to connect to a database:
from mysql.connector import (connection) conn = connection.MySQLConnection (user='jane', password = 'my-password' , host = "192.168.5.7" , database = 'student')
The rest of this database programming tutorial will use the connect() method.
How to Execute a MySQL Query in Python
In order to execute a MySQL query, you need a MySQLCursor object. This object interacts with a MySQLConnection object and the database.
You can create a MySQLCursor in Python using the following code example:
cursor = cnx.cursor()
Programmers can then use the execute() method to process a query:
my-query = “SELECT * FROM Students” result = cursor.execute(my-query)
If you are using the INSERT, DELETE, or UPDATE statements, then you need to commit your connection after executing the query. You can do so with the commit() method, show in the following code example:
conn.commit()
After you are finished interacting with the database, ensure that you close the connection and the cursor to release system resources. You can do this using the close() method:
cursor.close() conn.close()
Here is a fully working code example showing how to connect to a MySQL database with Python, query a table, and close the connection:
import mysql.connector from mysql.connector import Error from mysql.connector import errorcode try: connection = mysql.connector.connect(database='school', password='pass456', user='root') if connection.is_connected(): dbVersion = connection.get_server_info() print("Succeful connection to MySQL Server version:", dbVersion) except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Please check your access credentials (username or password)") elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Please check the database name") else: print(err) connection.close()
Notice the try.. except clause; developers can use this catch error in their database interactions to catch any errors.
Final Thoughts on Using Databases with Python
In this database programming tutorial, we learned how to connect a Python application to a MySQL database. Remember, you always need to close your connection after using the database to free up system resources using the close() method.
Read: Project Management Software for Database Developers
The post How to Use Databases With Python appeared first on Database Journal.
