Extracting Data in a Database Using Python

Extracting data in a database using python.

Using Python to extract data in a MySQL table


first we need to pip install pymysql

1. Create table along with connection to the mysql server using python

import pymysql

connection=pymysql.connect(host='host',port=port,user='user',password='password',db='schema')

#creating cursor

cursor= connection.cursor()

#query for create Table

TABLES={}

TABLES['employees'] = (
"CREATE TABLE employees ("
"PersonID int,"
"LastName varchar(255),"
"FirstName varchar(255),"
"Address varchar(255),"
"City varchar(255)"
")")

for name, ddl in TABLES.items():
cursor.execute(ddl)
connection.commit()
connection.close()

Insert Data into the MySQL Table using Python
import pymysql

# Data set to insert

insert_people=[("8","Perera","L.D","123","CMB"),
("9","Rathnayake","S.S","123","HMB"),
("10","Guptha","C.K","123","MUM"),]
connection=pymysql.connect(host='host',port=port,user='user',password='password',db='schema')
cursor= connection.cursor()

#Insert each employee loop

for person in insert_people:
for element in person:
data=element.strip().split(',')
print (data[0])
insert_query="INSERT INTO employees(PersonId,LastName,FirstName,Address,City)VALUES('{0}','{1}','{2}','{3}','{4}');".format(data[0],data[1],data[2],data[3],data[4])

cursor.execute(insert_query,insert_people)
connection.commit()
connection.close()

Retrieve data which we insert

import pymysql

connection=pymysql.connect(host='host',port=port,user='user',password='password',db='schema')
cursor=connection.cursor()
query="SELECT * FROM employees"
cursor.execute(query)
result=cursor.fetchall()

#fetch data into a for loop and print them
for row in result:
print(row)
connection.close()