Sunday, 7 February 2016

Python3.x database connectivity with mysql using pymysql

Python3.x with pymysql
Python3.x with pymysql
This post is written with respect to python 3 only.
Let's get direct into this;

You can get the code on GitHub or download ZIP
Prerequisites:
  • MySQL sever and client
  • Python3.x installed
first install "pymysql"  package using pip or pip3 in our python, so open Command prompt and type the following command.

pip3 install pymysql

now check if it is installed successfully or not

pip freeze

Now open your MySQL  client/CLI and work as follows:

mysql> USE test;
Database changed


Now now create a sample named "STUDENT"  table in it.

mysql> CREATE TABLE STUDENT(rollno int primary key auto_increment, name varchar(20));
Query OK, 0 rows affected (0.16 sec)


Now check the table description

mysql> DESC student;
+--------+-------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra             |
+--------+-------------+------+-----+---------+----------------+
| rollno  | int(11)       | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                           |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)


Now we are ready to work with our this database
now open any of your python console and follow the commands:

Check whether  the connection is working or not:

import pymysql
try:
     con=pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test')
     print("Connection Successful :-)")
except:
          print("Connection Unsuccessful :-(")

If the output is "Connection Successful :-)" then everything is working fine and we can proceed.

Insert values in our database:

import pymysql
con=pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test')
cursor=con.cursor()
sql='INSERT INTO `student`(`name`) VALUES(%s)'
cursor.execute(sql,('Azhar Khan'))
con.commit()
con.close()

You can check the whether is it worked or not from MySQL CLI

mysql> SELECT * FROM student;
+--------+------------+
| rollno  | name        |
+--------+------------+
|          1 | Azhar Khan |
+--------+------------+
1 row in set (0.00 sec)

Retrieving values from database:

import pymysql
con=pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test')
cursor=con.cursor()
sql="SELECT * FROM `student`"
cursor.execute(sql)
results=cursor.fetchall()
for i in results:
print(I)

 I hope that the post was useful, lazy coders can get code from GitHub:



or download the zip file from here