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:UsersKAVINS~1envScriptspython.exe
Installing setuptools, pip, wheel...done.
C:UsersKAVINS~1envScriptspython.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.



Categorized in:

HowTo'sWindows