In this section of the tutorial, we will discuss the steps to connect the python application to the database.
There are the following steps to connect a python application to our database.
- Import mysql.connector module
- Create the connection object.
- Create the cursor object
- Execute the query
Creating the connection
To create a connection between the MySQL database and the python application, the connect[] method of mysql.connector module is used.
Pass the database details like HostName, username, and the database password in the method call. The method returns the connection object.
The syntax to use the connect[] is given below.
Consider the following example.
Example
Output:
Here, we must notice that we can specify the database name in the connect[] method if we want to connect to a specific database.
Example
Output:
Creating a cursor object
The cursor object can be defined as an abstraction specified in the Python DB-API 2.0. It facilitates us to have multiple separate working environments through the same connection to the database. We can create the cursor object by calling the 'cursor' function of the connection object. The cursor object is an important aspect of executing queries to the databases.
The syntax to create the cursor object is given below.
Example
Output:
MySQLCursor: [Nothing executed yet]
View Discussion
Improve Article
Save Article
View Discussion
Improve Article
Save Article
Databases are powerful tools for data scientists. DB-API is Python’s standard API used for accessing databases. It allows you to write a single program that works with multiple kinds of relational databases instead of writing a separate program for each one. This is how a typical user accesses databases using Python code written on a Jupyter notebook, a Web-based editor.
There is a mechanism by which the Python program communicates with the DBMS:
- The application program begins its database access with one or more API calls that connect the program to the DBMS.
- Then to send the SQL statement to the DBMS, the program builds the statement as a text string and then makes an API call to pass the contents to the DBMS.
- The application program makes API calls to check the status of its DBMS request and to handle errors.
- The application program ends its database access with an API call that disconnects it from the database.
The two main concepts in the Python DB-API are:
1] Connection objects used for
- Connect to a database
- Manage your transactions.
Following are a few connection methods:
- cursor[]: This method returns a new cursor object using the connection.
- commit[]: This method is used to commit any pending transaction to the database.
- rollback[]: This method causes the database to roll back to the start of any pending transaction.
- close[]: This method is used to close a database connection.
2] Query objects are used to run queries.
This is a python application that uses the DB-API to query a database.
Python3
from
dbmodule
import
connect
connection
=
connect[
'databasename'
,
'username'
,
'pswd'
]
cursor
=
connection.cursor[]
cursor.execute[
'select * from mytable'
]
results
=
cursor.fetchall[]
cursor.close[]
connection.close[]
- First, we import the database module by using the connect API from that module. To open a connection to the database, you use the connection function and pass in the parameters that are the database name, username, and password. The connect function returns the connection object.
- After this, we create a cursor object on the connection object. The cursor is used to run queries and get the results.
- After running the queries using the cursor, we also use the cursor to fetch the results of the query.
- Finally, when the system is done running the queries, it frees all resources by closing the connection. Remember that it is always important to close connections to avoid unused connections taking up resources.