Python permits builders to work together with numerous relational databases (equivalent to Oracle, SQLite, and MySQL) and relational database methods (RDBMS) by way of a number of totally different libraries, which should conform to requirements outlined in PEP 249.
On this database programming tutorial, we are going to talk about how to connect with a MySQL database utilizing Python and make database queries. You may learn extra in regards to the PEP 249 by visiting the Python Database API Specification.
Tips on how to Create a Database Connection in Python
To create a connection to a MySQL database in Python, builders first must obtain a database connector, which is a module that can allow your Python scripts to work together with knowledge. This tutorial might be utilizing the MySQL database because it is likely one of the hottest and broadly used databases.
Not accustomed to MySQL or wish to enhance your MySQL improvement expertise? We’ve an inventory of the Finest On-line Programs to Be taught MySQL to assist get you began.
To obtain the Python MySQL database connector, you should use PIP with the next command:
$ pip set up mysql-connector-python
There are a selection of different modules programmers can use to make database connections, equivalent to PyMySQL, MySqlClient, and OurSQL. Nevertheless, this programming tutorial makes use of the MySQL Python connector, which is formally supported by Oracle and is written purely in Python.
To start working with this module, you’ll need to import it into your script utilizing the next command:
import mysql.connector
Subsequent, you have to create a connection to the database. There are two methods of doing this: you may both use the join() operate or use the MySQLConnection object.
Tips on how to Use the join() Methodology in Python
Right here is an instance of of a connection string you should use to connect with a database named pupil:
conn = mysql.connector.join( consumer='jane', password = 'my-password' , host = "192.168.5.7" , database = 'pupil')
There are 4 key arguments {that a} programmer wants to connect with a database:
- consumer: The username used to connect with a database
- password: The customers password
- host: IP tackle of the host the place the database is hosted. Be aware that the default is 127.0.0.1 (localhost)
- database: Title of the database you are attempting to connect with
You might be questioning why there isn’t any argument in your MySQL server’s port. In fact, there’s, and the default is 3306. In case you set your server to make use of one other port you should use the port argument.
The join() does have extra elective arguments that you would be able to examine within the official Connector/Python documentation.
Tips on how to Use MySQLConnection to Connect with a Database
Right here is a few instance code exhibiting use Python and the MySQLConnection object to connect with a database:
from mysql.connector import (connection)
conn = connection.MySQLConnection (consumer='jane', password = 'my-password' , host = "192.168.5.7" , database = 'pupil')
The remainder of this database programming tutorial will use the join() methodology.
Tips on how to Execute a MySQL Question in Python
So as to execute a MySQL question, you want a MySQLCursor object. This object interacts with a MySQLConnection object and the database.
You may create a MySQLCursor in Python utilizing the next code instance:
cursor = cnx.cursor()
Programmers can then use the execute() methodology to course of a question:
my-query = “SELECT * FROM College students”
consequence = cursor.execute(my-query)
In case you are utilizing the INSERT, DELETE, or UPDATE statements, then you have to commit your connection after executing the question. You are able to do so with the commit() methodology, present within the following code instance:
conn.commit()
After you’re completed interacting with the database, be sure that you shut the connection and the cursor to launch system assets. You are able to do this utilizing the shut() methodology:
cursor.shut()
conn.shut()
Here’s a absolutely working code instance exhibiting how to connect with a MySQL database with Python, question a desk, and shut the connection:
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
attempt:
connection = mysql.connector.join(database='faculty', password='pass456', consumer='root')
if connection.is_connected():
dbVersion = connection.get_server_info()
print("Succeful connection to MySQL Server model:", dbVersion)
besides mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Please examine your entry credentials (username or password)")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Please examine the database identify")
else:
print(err)
connection.shut()
Discover the attempt.. besides clause; builders can use this catch error of their database interactions to catch any errors.
Last Ideas on Utilizing Databases with Python
On this database programming tutorial, we discovered join a Python software to a MySQL database. Keep in mind, you all the time want to shut your connection after utilizing the database to unencumber system assets utilizing the shut() methodology.
Learn: Venture Administration Software program for Database Builders
