Header Ads

How do I connect to a MySQL Database in Python: Python Mysql Connector

Whether you are Windows user or a Linux User, Connecting MySQL database really becomes a very cramp breaking task, you can't imagine for the first time when tried for connecting MySQL with Python i was really putting my head on the wall for 3 days, then once i resolved that issue & decided to create a Step by Step guide for this because existing tutorials or post on internet are insufficient.




Understanding Python MySQL connector:-

MySQL is one of the most popular database system and if we want to use MySQL with Python then we need to install MySQL connector which is also known as official database driver provided by MySQL community.
In place of MySQL connector, we can also choose other options like : PyMySQL and MySQL-Python, where all of them uses Python API of portable SQL database due to which we can switch from one library to another without changing our existing code or with some minor changes.

So, Which one to choose ??

According to my opinion and previous experience, i will choose MySQL connector as it includes all features of MySQL 5.7 version as well as its very easy to setup.
For setting up the MySQL python connector first we need to create virtual environment and install mysql.connector module using pip command.

Creating Virtual Environment and Installing MySQL-connector :

Let's follow the step by step guide:

Installing virtualenv:

python -m pip install --user virtualenv

Creating a virtualenv:

python -m virtualenv env

Activating a virtualenv:

#in output you shuld get this : 
Using base prefix 'C:\\Python34'
New python executable in C:\Users\KAVINS~1\env\Scripts\python.exe
Installing setuptools, pip, wheel...done.
C:\Users\KAVINS~1\env\Scripts\python.exe & press enter

Installing Package (MySQL connector):

pip install mysql-connector-python

Now, we are done with setting up virtual environment and installing package. Let's see a code which will showcase the example of using MySQL-connector in python.

Let's solve simple question :-


Q.1- Create a database. Create the following tables:
1. Book | 2. Titles | 3. Publishers | 4. Zipcodes | 5. AuthorsTitles | 6. Authors
Refer to the diagram below 


Transcript / Cheat Sheet :

In the code given below, just change User with MySQL username and password with MySQL password which you already setup while installing MySQL.

import mysql.connector as pm
try:
con = pm.connect(host='localhost', database='hackanonsdb', user='root', password='password')
cursor = con.cursor()
query6 = 'Create table Authors(AuthorID int primary key,FirstName varchar(15),MiddleName varchar(15),LastName varchar(15))'
cursor.execute(query6)
query4 = 'Create table Zip_Codes(Zip_Code_ID int primary key,City varchar(15),State varchar(20),Zip_Code int)'
cursor.execute(query4)
query3 = 'Create table Publishers(Publisher_ID int primary key,Name varchar(15),Street_Address varchar(50),\
Suite_Number int,Zip_Code_ID int,foreign key(Zip_Code_ID) references Zip_Codes(Zip_Code_ID))'
cursor.execute(query3)
query2 = 'Create table Titles(TitleId int primary key,Title varchar(35),ISBN int,Publisher_ID int,Publication_Year int,\
foreign key(Publisher_ID) references Publishers(Publisher_ID))'
cursor.execute(query2)
query1 = 'Create table Books(BookId int primary key,TitleId int,Location varchar(15),Genre varchar(10),\
foreign key(TitleId) references Titles(TitleId))'
cursor.execute(query1)
query5 = 'Create table Authors_Titles(Author_Title_ID int primary key,AuthorID int ,TitleId int,\
foreign key(AuthorID) references Authors(AuthorID),foreign key(TitleId) references Titles(TitleId))'
cursor.execute(query5)
print('Table Created')
except pm.DatabaseError as e:
if con:
con.rollback()
print('Problem occured: ', e)
finally:
if cursor:
cursor.close()
if con:
con.close()
print('DONE!!')
That's end up with great tutorial on Python Mysql Connectivty using Mysql Python Connector.



12 comments:

  1. All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.
    Software Testing Training in Chennai
    Software Testing Course in Chennai
    RPA Training in Chennai
    SEO Training in Chennai
    Hadoop Training in Chennai
    Digital Marketing Course in Chennai
    Software Testing Training in OMR

    ReplyDelete
  2. The next time I read a blog, Hopefully it does not disappoint me just as much as this one. After all, Yes, it was my choice to read, however I really believed you'd have something interesting to talk about. All I hear is a bunch of crying about something you could possibly fix if you were not too busy seeking attention.
    Tech info

    ReplyDelete
  3. For this web site, you will see our account, remember to go through this info. buy android app reviews from 5xappreviews

    ReplyDelete

  4. You write this post very carefully I think, which is easily understandable to me. Not only this, but another post is also good. As a newbie, this info is really helpful for me. Thanks to you.
    Tally ERP 9 Training
    tally classes
    Tally Training institute in Chennai
    Tally course in Chennai

    ReplyDelete
  5. Thanks a lot for sharing such a good source with all, i appreciate your efforts taken for the same. I found this worth sharing and must share this with all.


    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery








    ReplyDelete

Powered by Blogger.