How to connect and work with MySQL Database in Python

Python is fast when working and analysing large size of database. To work with MySQL in Python, first you need to install MySQL Driver for Python. In this article we will use mysql-connector driver to work with Python3. pip is the package installer for Python. You can use pip to install packages from the Python Package Index. We recommend you use pip to install Python packages.

To install MySQL driver, run the below command in Terminal.

python3 -m pip install mysql-connector-python

Now in python file, first import mysql connector.

import mysql.connector

Now create a connection to the database.

mysql_database = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    password = 'root',
    database = 'hackthestuff_database',
    autocommit = True
)

mysql_cursor = mysql_database.cursor()

That's it. Your MySQL database is connected with Python. Now you can run MySQL query in Python.

query = "SELECT * FROM users WHERE country = 'US'"
mysql_cursor.execute(query)
users = mysql_cursor.fetchall()

for x in users:
    print(x)

Same way you can use all MySQL query in Python. If the query changes anything in the table you need to commit() the query to make changes in database or you can add autocommit = True in your connection code.

query = "INSERT INTO users (first_name, last_name, address) VALUES ('John', 'Doe', 'Freemount Street')"
mysql_cursor.execute(query)
mysql_database.commit()

Here is index.py file with full code:

#!/usr/bin/python
import mysql.connector

# connect with database
mysql_database = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    password = 'root',
    database = 'neo',
    autocommit = True
)

# run query
query = "INSERT INTO users (first_name, last_name, address) VALUES ('John', 'Doe', 'Freemount Street')"
mysql_cursor.execute(query)
mysql_database.commit()

# close connection with database
mysql_database.close()

I hope this will help you in your day-to-day work. In next article, we will see on how to connect MongoDB with Python.

Tags:

Was this article helpful?

0 out of 0 person found this article helpful.

Leave a comment

Or

No Comment