Python database connection | How to connect python to Mysql database

Python database connection | How to connect python to Mysql database

Python database connection is one of the most requested tutorial.In this tutorial, I’m going to teach you, how to connect mysql database to python and do simple queries using python. This is not one of the tutorials from my python beginner series. You do need to have bit of knowledge about python and mysql database to follow up this tutorial. If you would like to learn python from the beginning, feel free to use my python for beginners tutorial set. I will try to simplify everything as much as i can. Feel free to comment under this post if you have anything to clarify. Lets get started then.

To do this task, we need to have working mysql db in your computer. If you are a windows user you can install wamp server to your computer to run mysql. I will make a separate tutorial about installing mysql after this tutorial. You can always google about installing mysql to your computer.

Install MySQL Driver

First of all, we need to install python mysql driver to connect python to mysql. Because of that, We need to install python driver first. We are going to use “pip” , python package manager to to install it. When you install python to your computer, You will get pip automatically.  If you have an issue, Please comment under this and i will help you. Lets install python mysql connector using pip.

pip install mysql-connector-python

After the installation, you can use mysql database with python. Lets make the connection between python and database using below simple code. Then we will check weather the mysql is successfully connected to python script or not.

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="YourUserName",
  password="YourPassword"
)

print(db)

You have to replace “YourUserName” with your mysql username and “YourPassword” with your mysql password. Then you will get following result if everything is OK.

<mysql.connector.connection.MySQLConnection object at 0x7fc2b756d290>

Lets create a new database using python now

create a new python file or edit the existing one as the code shown below.

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="YourUserName",
  password="YourPassword"
)

cursor = db.cursor()

cursor.execute("CREATE DATABASE MyDatabaseName")

In here, I am importing mysql.connector to my python script first and making the database connection variable. Then Im making a  cursor to execute my mysql query. Then I’m making and informing mysql to make a new database using “CREATE DATABASE MyDatabaseName” query. This is really simple to do.

Lets use SELECT query to get data from existing database.

Create a new python script file first. After that lets type following code.

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="YourUserName",
  password="YourPassword",
  database="YourDatabaseName"
)
//creating a cursor
cursor = db.cursor()

//executing select query
cursor.execute("SELECT * FROM Users")

//getting result and storing them inside myresult 
myresult = cursor.fetchall()

//iterating myresult and getting everything stored inside myresult variable. 
for x in myresult:
  print(x)

As you can see here, I have edited “db” variable and added a database name to it. Then I have executed a select all query to get everything inside “Users” table. After that I wrote a for loop to get everything inside myresult variable.

This is how you can connect python to mysql and make simple queries. Fell free to comment, If you have any issues with this, Please comment under this and i will assist you. See you soon with another tutorial.

Leave a Reply

Your email address will not be published. Required fields are marked *