To connect Python3 to MySQL, you need to install the “pymysql” package and use the “connect()” method. This method is used to establish a connection to the MySQL database and accepts several arguments.
Here is the step-by-step guide:
Step 1: Install the pymysql module
Before we install it, let’s check the requirements of the Pymysql package.
Requirements
- Python – one of the following:
- CPython >= 2.7 or >= 3.5
- Latest PyPy
- MySQL Server – one of the following:
- MySQL >= 5.5
- MariaDB >= 5.5
Type the following command to install the PyMySql package:
python3 -m pip install PyMySQL
Step 2: Create a Database and Table in MySQL
When we connect Python to MySQL Database, we must provide the following things in the argument.
- host: Host where the database server is located
- user: Username to log in as
- password: Password to use.
- database: Database to use, None to use a particular one.
- port: MySQL port to use, default is usually OK. (default: 3306)
Now, I am using the MAMP server on my Mac. So, I have already installed PHP, MySQL, and Apache.
See the below table in MySQL:
The above screenshot shows that our table name is Coronas, with two rows. We are doing this because we will fetch and print all the rows in the Python console.
Step 3: Import the pymysql package
Create a Python file called app.py and import the pymysql package.
# app.py
import pymysql
Step 4: Provide your MySQL Credentials
Write the pymysql connect() function and pass the credentials, hostname, and database name as arguments.
# app.py
db = pymysql.connect("your hostname","your username","your password","your database" )
Please write your credentials accordingly.
Step 5: Prepare a cursor object using a cursor() method
Next, the db object is used to create a cursor object, which is used to execute SQL queries.
cur = db.cursor()
cur.execute('SELECT * FROM coronas')
I have created a table named coronas.
We have executed the SELECT statement to fetch all the rows from the table.
Step 6: Read Operation
Once the database connection is established, you can query this database. For example, you can use the fetchone() method to fetch a single record or the fetchall() method to fetch multiple values from a database table.
- fetchone() − It fetches the next row of a query result set. A result set is an object returned when a cursor object is used to query a table.
- fetchall() − It fetches all the rows in a result set. If some rows have already been extracted from the result set, it retrieves the remaining rows from the result set.
-
rowcount − This read-only attribute returns the number of rows affected by an execute() method.
Let’s use cur.fetchall() function to get all the rows.
for row in cur.fetchall():
print(row)
It will print all the rows in the Python console. Now, the last step is to close the connection.
Step 7: Close MySQL connection
Before coming out, it ensures the database connection is closed and resources are released.
db.close()
Here is the complete code:
import pymysql
db = pymysql.connect("localhost","root","root","laravel7crud" )
# prepare a cursor object using cursor() method
cur = db.cursor()
try:
cur.execute('SELECT * FROM coronas')
for row in cur.fetchall():
print(row)
except:
print ("Error: unable to fetch data")
db.close()
Output
(1, 'India', 'Fever', 29000, datetime.datetime(2020, 4, 27, 18, 26, 59),
datetime.datetime(2020, 4, 27, 18, 27, 2))
(2, 'USA', 'Cough', 3000000, datetime.datetime(2020, 4, 27, 19, 15, 5),
datetime.datetime(2020, 4, 27, 19, 15, 9))
That’s it.
D
Very helpful, thanks! I’ve been very confused as to what the *actual* practices are concerning entering credentials to access the server. Nobody ever explains what to do instead of writing the username and password in plaintext in the code itself.