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.
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:
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:
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.
Here is an example of of a connection string you can use to connect to a database named student:
There are four key arguments that a programmer needs to connect to a database:
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.
Here is some example code showing how to use Python and the MySQLConnection object to connect to a database:
The rest of this database programming tutorial will use the connect() method.
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:
Programmers can then use the execute() method to process a 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:
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:
Here is a fully working code example showing how to connect to a MySQL database with Python, query a table, and close the connection:
Notice the try.. except clause; developers can use this catch error in their database interactions to catch any errors.
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
DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.
Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.
Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved

Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.

source