Scribd
Upload
10 views9 pages

Interface With Python and SQL Programs

SQL program

Uploaded by

madeshpvi987
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
10 views9 pages

Interface With Python and SQL Programs

SQL program

Uploaded by

madeshpvi987
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

#show list of databases from sql through python

import [Link] as sqlconn


myconn=[Link](host='localhost',user='root',passwd='root')
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
[Link]("show databases")
data=[Link]()
print("List of databases")
print("-----------------")
for i in data:
print(i[0])

#create database:
import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root')
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
[Link]("create database GradeXII")
[Link];
[Link]("show databases")
data=[Link]()
print("List of databases")
print("-----------------")
for i in data:
print(i[0])

#create table:
import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
[Link]("create table redadmirals(sno int primary key,regno
char(5) unique,sname varchar(20),gender char(7),age int,dob date,address
varchar(50),mobileno int,aadharno int)")

print("Table created")
[Link];
[Link]("show tables")
data=[Link]()
print("List of tables")
print("-----------------")
for i in data:
print(i[0])

#show table schema:


import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
[Link]("desc redadmirals")
data=[Link]()
#print(data) # it returns the values as list of tuples[(),(),()]
for i in data:
print(i)

#Insert values into table:


import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
q1="insert into
redadmirals(sno,regno,sname,gender,age,dob,address,mobileno,aadharno)val
ues({},'{}','{}','{}',{},'{}','{}',{},
{})".format(104,'AIS04','disha','feMale',16,'2008-05-
06','thanjavur',76541230,23134)
[Link](q1)
[Link]()
print("Record inserted sucessfully")
q2="select * redadmirals"
[Link](q2)

data=[Link]()
#print(data) it returns the values as list of tuples[(),(),()]
for i in data:
print(i)

#Insert values into tables get from user:


import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
sno=int(input("Enter StudentNo: "))
regno=input("Enter RegNo: ")
sname=input("Enter StudentName: ")
gender=input("Enter Gender: ")
age=int(input("Enter age: "))
dob=input("Enter DOB(YYYY-MM-DD): ")
address=input("Enter Address: ")
mobileno=int(input("Enter mobilenumber: "))
aadharno=int(input("Enter AadharNo: "))
q1="insert into redadmirals
(sno,regno,sname,gender,age,dob,address,mobileno,aadharno) values
({},'{}','{}','{}',{},'{}','{}',{},
{})".format(sno,regno,sname,gender,age,dob,address,mobileno,aadharno)
[Link](q1)
[Link]();
print("Record inserted sucessfully")
[Link]("Select * from redadmirals")
data=[Link]()
#print(data) # it returns the values as list of tuples[(),(),()]
for i in data:
print(i)

#Show records from table:


import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
q1="Select * from redadmirals"
[Link](q1)
data=[Link]()
#print(data) # it returns the values as list of tuples[(),(),()]
print("\t\t\tStudent details\t\t\t")
print("\t\t\t****************\t\t\t\n")
print("SNo \t RegNo \t SName \t Gender Age \t DOB \t\t Address \t
MobileNo \t AadhaarNo")

print("--------------------------------------------------------------------------------------------------")
for i in data:
print(i[0],'\t',i[1],'\t',i[2],'\t',i[3],i[4],'\t',i[5],'\t',i[6],'\t',i[7],'\t',i[8])

#show records from table using where clause:


import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
q1="Select * from redadmirals where age=16"
[Link](q1)
data=[Link]()
#print(data) # it returns the values as list of tuples[(),(),()]
print("\t\t\tStudent details\t\t\t")
print("\t\t\t****************\t\t\t\n")
print("SNo \t RegNo \t SName \t Gender Age \t DOB \t\t Address \t
MobileNo \t AadhaarNo")

print("--------------------------------------------------------------------------------------------------")
for i in data:
print(i[0],'\t',i[1],'\t',i[2],'\t',i[3],i[4],'\t',i[5],'\t',i[6],'\t',i[7],'\t',i[8])
#show records from table using where clause(get from user)-
parameterized query :
import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
age=int(input("Enter age of student you want to search: "))
q1="Select * from redadmirals where age={}".format(age)
[Link](q1)
data=[Link]()
#print(data) # it returns the values as list of tuples[(),(),()]
print("\t\t\tStudent details\t\t\t")
print("\t\t\t****************\t\t\t\n")
print("SNo \t RegNo \t SName \t Gender Age \t DOB \t\t Address \t
MobileNo \t AadhaarNo")

print("--------------------------------------------------------------------------------------------------")
for i in data:
print(i[0],'\t',i[1],'\t',i[2],'\t',i[3],i[4],'\t',i[5],'\t',i[6],'\t',i[7],'\t',i[8],'\n')
gender=input("Enter the gender you want to search: ")
q1="Select * from redadmirals where gender='{}'".format(gender)
[Link](q1)
data=[Link]()
print("\t\t\tStudent details\t\t\t")
print("\t\t\t****************\t\t\t\n")
print("SNo \t RegNo \t SName \t Gender Age \t DOB \t\t Address \t
MobileNo \t AadhaarNo")

print("--------------------------------------------------------------------------------------------------")
for i in data:
print(i[0],'\t',i[1],'\t',i[2],'\t',i[3],i[4],'\t',i[5],'\t',i[6],'\t',i[7],'\t',i[8])

#Update values into tables:


import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
q1="update redadmirals set Address='thanjavur' where age=16"
[Link](q1)
[Link]()
q1="Select * from redadmirals"
[Link](q1)
data=[Link]()
print("\t\t\tStudent details\t\t\t")
print("\t\t\t****************\t\t\t\n")
print("SNo \t RegNo \t SName \t Gender Age \t DOB \t\t Address \t
MobileNo \t AadhaarNo")

print("--------------------------------------------------------------------------------------------------")
for i in data:
print(i[0],'\t',i[1],'\t',i[2],'\t',i[3],i[4],'\t',i[5],'\t',i[6],'\t',i[7],'\t',i[8])

#Update values into tables using format method:


import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
q1="update redadmirals set Address='{}' where age={}" .format
('Trichy',16)
[Link](q1)
[Link]()
q1="Select * from redadmirals"
[Link](q1)
data=[Link]()
print("\t\t\tStudent details\t\t\t")
print("\t\t\t****************\t\t\t\n")
print("SNo \t RegNo \t SName \t Gender Age \t DOB \t\t Address \t
MobileNo \t AadhaarNo")

print("--------------------------------------------------------------------------------------------------")
for i in data:
print(i[0],'\t',i[1],'\t',i[2],'\t',i[3],i[4],'\t',i[5],'\t',i[6],'\t',i[7],'\t',i[8])'''
#delete table records:
import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
q1="delete from redadmirals where sno=105"
[Link](q1)
[Link]()
q1="Select * from redadmirals"
[Link](q1)
data=[Link]()
print("\t\t\tStudent details\t\t\t")
print("\t\t\t****************\t\t\t\n")
print("SNo \t RegNo \t SName \t Gender Age \t DOB \t\t Address \t
MobileNo \t AadhaarNo")

print("--------------------------------------------------------------------------------------------------")
for i in data:
print(i[0],'\t',i[1],'\t',i[2],'\t',i[3],i[4],'\t',i[5],'\t',i[6],'\t',i[7],'\t',i[8])

#Delete table records using format method(parameterized query):

import [Link] as sqlconn


myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
q1="delete from redadmirals where address='{}'".format('Coimbatore')
[Link](q1)
[Link]()
q1="Select * from redadmirals"
[Link](q1)
data=[Link]()
print("\t\t\tStudent details\t\t\t")
print("\t\t\t****************\t\t\t\n")
print("SNo \t RegNo \t SName \t Gender Age \t DOB \t\t Address \t
MobileNo \t AadhaarNo")

print("--------------------------------------------------------------------------------------------------")
for i in data:
print(i[0],'\t',i[1],'\t',i[2],'\t',i[3],i[4],'\t',i[5],'\t',i[6],'\t',i[7],'\t',i[8])

#Truncate the table values:


import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
q1="Select * from redadmirals"
[Link](q1)
data=[Link]()
count=[Link]
print("Number of records in RedAdmirals table is:",count,'\n')
if count>0:
print("\t\t\tStudent details\t\t\t")
print("\t\t\t****************\t\t\t\n")
print("SNo \t RegNo \t SName \t Gender Age \t DOB \t\t Address \t
MobileNo \t AadhaarNo")

print("--------------------------------------------------------------------------------------------------")
for i in data:
print(i[0],'\t',i[1],'\t',i[2],'\t',i[3],i[4],'\t',i[5],'\t',i[6],'\t',i[7],'\t',i[8])
q1="truncate table redadmirals"
[Link](q1)
[Link]()
q1="Select * from redadmirals"
[Link](q1)
data=[Link]()
print("\t\t\tStudent details\t\t\t")
print("\t\t\t****************\t\t\t\n")
print("SNo \t RegNo \t SName \t Gender Age \t DOB \t\t Address \t
MobileNo \t AadhaarNo")
print("--------------------------------------------------------------------------------------------------")

for i in data:
print(i[0],'\t',i[1],'\t',i[2],'\t',i[3],i[4],'\t',i[5],'\t',i[6],'\t',i[7],'\t',i[8])

#Drop the table:


import [Link] as sqlconn
myconn=[Link](host='localhost',user='root',passwd='root',databas
e="gradexii")
if myconn.is_connected():
print("Connection established\n")
mycursor=[Link]()
q1="drop table redadmirals"
[Link](q1)
[Link]()
q1="show tables"
[Link](q1)
data=[Link]()
count=[Link]
print("Number of records in demo table is:",count,'\n')
if count>0:
print("List of tables")
print("-----------------")
for i in data:
print(i[0])
else:
print("No tables found!!!")

You might also like