MySql Connection with Python

MySQL is a freely available open source Relational Database Management System (RDBMS)
that uses Structured Query Language (SQL). SQL is the most popular language for adding,
accessing and managing content in a database. It is most noted for its quick processing,
proven reliability, ease and flexibility of use.

INSTALLING MySQL 5.5 Server

After installing MySQL install MySQL connector using command:

pip install mysql-connector

Now you are ready to integrate MySQL with python. You can check whether the MySQL
Command line Client has successfully installed and working or not. Type MySQL in start
menu and check it.
Open MySQL and type status to see your connection id.

What is connection id ?
Whenever a user does a successful login to the sql client the sql will automatically allocate a
new and unique connection id to identify each user uniquely in the session.

Whenever a user perform any operation using Mysql console or through any other
application the connection id always reach to the main operation section through which Sql
get to know that which user has send that particular query.

GETTING INTO PYTHON TO MAKE A SUCCESFULL CONNECTION

 Open your python in script mode and write the following command.

What we have done here is: we have first imported the [Link]
Is it necessary to import the [Link] ?

Yes it is very much necessary to import the connector. The mysql connector is acting like a
bridge connection between python and mysql server.

When you import the [Link] to python then automatically python get smart
enough to understand all the queries related to mysql server.

Secondly we have created a variable named as db. You can make your own with some other
name.

In this db variable I have use a function called: [Link]()

The function [Link]() contains three basic parameter named:

1. Host
2. User
3. Password

Host: Your hostname defines the location of your MySQL database server. As we are
working on the same system where both python and MySQL is installed so we have to
write: ‘localhost’. If we work on different system where python and Mysql are not installed
on same computer than in that case you have to put the ip address of that particular system
where you have installed MySQL.

User : root is the default user . so always use root .

Password: while installing MySQL server somewhere you have been asked to input a
password. So that particular password you need to use while making the connection
through [Link]()

Question: How will you know that you have made a successful connection or not?

Trick: Try to print the connection id . If you get the connection id then you are ready to
write the codes for your program. Printing connection id is optional it’s not necessary. It’s
just for your assurance that you have written everything correctly like hostname, user and
password.
Make sure to give a underscore between connection & id .

Creating Database using Python Script mode

Before creating a database let’s check for the existing database in MySQL .
We will directly open the MySQL command line and enter our password and will type:

Show databases; (Not case sensitive)

So by default we have 4 databases in our MySQL server.

Note: now we will create a database from python application to MySQL server. How
amazing it must be!!
Before creating database let us understand about a cursor() function.

We will use this function every time we execute a command or quires.

Cursors are created by the connection. cursor() method: they are bound to the connection
for the entire lifetime and all the commands are executed in the context of the database
session wrapped by the connection.

We can say cursor() function is a vehicle which will load all the commands as a passenger
and will use the bridge connection that is [Link]() to send the passenger
from python to MySQL server.

Let understands it using a picture.

[Link]()
MySQL
Sql Commands

Cursor()
[Link]()
MySQL
Continuation with code for creating database using python script mode

Note: Press f5 or run it . However the result windows will not display anything.

Again open your MySQL Command line Client then enter password and type:

Show databases;

And check whether school has been added in the database list or not.

Now you can see that the database named school has been added successfully. While
executing a command use: execute() function. It’s very simple have a look once again to
the snapshots and your doubt will be cleared out.
CREATING TABLE
Scenario:

Why we have created a database?

A database is typically designed so that it is easy to store and access information.

Information is stored in the form of records. And records are stored in the form of table.
The table consists of Rows and Columns.

So without database we cannot create a table. And once the table is created we can start
storing our records.

So before creating database our [Link]() have 3 parameters now we will

include one more that is database.

Why so ?

If we don’t include the database name in our connection then how would MySQL knows
that where to store the table or tables. So add the parameter named: database followed by
database name you already created before.

Now follow the same procedure as above:

SQL code to create a table is:

CREATE TABLE table_name (column_name column_type);

So we will create a table name data with field name: Name, Roll, class and sec.
OUTPUT:

INSERTING A RECORD
Hey just wait !!!

Have you seen commit() function at last line.

Why is this function used for?

The commit() is use to give an assurance to MySQL that the record that are being send
through python application is purely valid and is being executed intentionally. So commit()
function is a request made to SQL server to save the record to its database.

Why we haven’t used commit() function while creating database and creating table ?

You must have an idea about DDL and DML command in SQL to understand this. The DDL
command don’t need commit() function. But while entering a records or doing any updates
we always need to use commit() function.

INSERTING A RECORD BY USER INPUT

Note: We have passed the values directly into insert function in our above code.
Which is actually not the correct way. Because the record must be entered by the
user not by the programmer. So let see how we can do that.

Point 1 : Keep in mind that values field should have %s . Python is smart enough to replace
%s with the values provided by the user as input.
Point 2: in execute() we have passed two parameters one is the SQL command and
other is the tuple that we have created by taking input from the user.

OUTPUT

so it is always necessary to convert the input to tuples before using execute() ?

No, not so necessary. If you want you can even convert the inputs to list. See the below
snapshot and output.
INSERTING MULTIPLE VALUES
We already have an idea about using loops. Just use the loop for taking multiple inputs.
Check the snapshot with output. It’s very easy and simple.

So you can see how easy it is to insert multiple values using loop. You can modify the code
according to your own choice. You can even use user define function to call the insert
statement. Keep practicing with your own logic and see how amazing the coding is.
DISPLAYING CONTENT OF MYSQL TO PYTHON SHELL (Select Statement)
Now it’s time to see the records directly into python shell. So we can say displaying
information means somewhere we are fetching the information from MySQL server.

So now another new function will be added so called fetchall ().

To print the output in row wise you have to use for loop. See the code once again to clear
your concept.

We use the fetchall() method, which fetches all rows from the last executed statement.
[Link]() fetches all the rows of a query result. It returns all the rows as a list of
tuples. An empty list is returned if there is no record to fetch.

Now the question arise :

I don’t want to see all the rows. For example, only 3 rows I want to see from the table. So
what to do ?

[Link](size) returns the number of rows specified by size argument. When

called repeatedly, this method fetches the next set of rows of a query result and returns a
list of tuples. If no more rows are available, it returns an empty list.

Let see an example:

[Link]() method returns a single record or None if no more rows are available.
UPDATING
Updating means modifying. Update is only possible when the record is already available in
the table. You can update individual rows, all the rows in a table, or a subset of all rows.

Example:

Let see all the entries that I have in my school database inside data table.

So this is the records I already have in my database . Now I want to update Roll number of
Kunal to 20.
Use commit() otherwise the record will not be updated. You already have an idea when to
use commit statement.

Now let see the output.

Updating all records at a time:

to update all the records at a time you have to use your logic.
Like: I want to increment all roll number by 2 so let see how I write the code.

Please check the difference in the Roll column and tally it from the above two snapshot. You
will see the difference.
Deleting a Record
Deleting a record means removing information from the table. Deleting can be performed
on a single record, multiple record or you can even delete the whole table.

Let’s go through the deleting option with an example. You already have an idea from the
previous snapshot that what fields and record we are already having in our table named :
data.

Deleting a single record

Always keep in mind that

whenever you are making
changes in the table always
use commit(). Otherwise,
you will se that the codes
are running fine but the
table is not updating.

So be careful in this thing.

Deleting multiple record

Whenever you are deleting multiple record be sure that you use correct condition
otherwise you may delete the whole table unintentionally.
Deleting table
Deleting a table means there will be neither any record nor the table structure will be
there. Everything will gets deleted. So we will use Drop command and we don’t need to use
commit() because when everything will get deleted then what commitment will we make.

See the error message