Embedded Files
Q1. Count ‘india’ word from text file abc.txt
f=open('abc.txt')
c=0
s=f.read()
w=s.split()
for i in w:
if(i=="india"):
c=c+1
print(c)
f.close()
Q2. Count lines starts from ‘s’ from text file abc.txt
f=open('abc.txt')
c=0
list=f.readlines()
for i in list:
if(i[0]=='s'): # match each item of list starts with s or not
c=c+1
print(c)
f.close()
Q3. Count A or a and M or m characters from text file abc.txt
f=open('abc.txt’)
c1=0
c2=0
s=f.read()
w=s.split()
for i in w: # traverse every item in list
for c in i: # traverse every character in item
if(c==“A” or c==“a”):
c1=c1+1
if(c==“M” or c==“m”):
c2=c2+1
print(“A or a”,c1)
print(“M or m”,c1)
Q4. Count all characters from text file abc.txt
f=open('abc.txt')
count=0
s=f.read()
w=s.split()
for i in w: # traverse every item in list
for c in i: # traverse every character in item
count=count+1
print(count)
Q5. Count number of digits present in text file abc.txt
f=open('abc.txt')
count=0
s=f.read()
w=s.split()
for i in w:
for c in i:
if(i.isdigit()): # find the given character is digit or not
count=count+1
print(count)
Q6
Q7
Q8. Write only digits from one text file to another to another text file
fr=open('read.txt',"r")
fw=open("write.txt",'w')
s=fr.read()
w=s.split()
for i in w:
for c in i:
if c.isdigit(): # check character for digit
fw.write(c)
fr.close()
fw.close()
Q9.Writing upper, lower case characters and other characters input by keyboard in different files
f1=open('lower.txt',"w")
f2=open('upper.txt',"w")
f3=open('other.txt',"w")
n=int(input("How many characters you want to enter "))
for i in range(n):
ch=input("Enter a character")
if ch.islower():
f1.write(ch)
elif ch.isupper():
f2.write(ch)
else:
f3.write(ch)
f1.close()
f2.close()
f3.close()
Q10. Reading lines from one file and write into another file starting from 's'
r=open('read.txt',"r") # open file in read mode
w=open("write.txt",'w') # open file in write mode
lines=r.readlines()
for line in lines:
if(line[0]=='s'): # write all lines start from 's' from read.txt to write.txt
w.write(line)
r.close()
w.close()
Q11. Writing and reading integers from binary file
import pickle
f=open('numbers.dat','wb')
n=int(input("how many number you want to enter?"))
for i in range(n):
x=int(input("enter number\n"))
pickle.dump(x,f)
f.close()
f=open('numbers.dat','rb')
for i in range(n):
y=pickle.load(f) #Read object from a pickle file or binary file
print(y)
f.close()
Q12. Write employee name and salary into binary file emp.dat
import pickle
f=open('emp.dat','wb')
n=int(input("Enter no of employees\n"))
d={}
i=1
while i<=n:
name=input("Enter name")
salary=int(input("Enter salary"))
d[name]=salary
i=i+1
pickle.dump(d,f) # dump d into f
f.close()
Q13. Display name of employees where salary greater then 10000 from binary file emp.dat
import pickle
f=open("emp.dat","rb")
rd=pickle.load(f)
for key,value in rd.items():
if(value>4000):
print(key)
Q14. read data from f1.csv and write in f2.csv File(Copy data from one file to another)
import csv
r=open("f1.csv",'r')
w=open("f1.csv",'w')
rf = csv.reader(r)
wf = csv.writer(w)
for row in rf:
wf.writerow(row)
Q16. Write a program to delete a record from student.csv by giving the name of student
import csv
record = list()
sname= input("Please enter student name to delete:")
with open('student.csv', 'r') as f:
data = csv.reader(f)
for row in data:
record.append(row)
for field in row:
if field == sname:
record.remove(row)
with open('student.csv', 'w') as f:
writer = csv.writer(f)
writer.writerows(record)
Q17. Write a program to read abc.csv file and display all its records
import csv
f=open("abc.csv",'r')
r=csv.reader(f) # canvert file object into reader object
for row in r:
print(row)
SQL Queries
Creating table:
CREATE TABLE employee
(ECODE int , ENAME varchar(20) , GENDER char(1) , GRADE char(2) , GROSS int ) ;
Inserting record:
INSERT INTO employee VALUES(1001 , ‘Ravi’ , ‘M’ , ‘E4’ , 50000);
To select only ENAME, GRADE and GROSS column from student table
SELECT ENAME , GRADE , GROSS FROM EMPLOYEE ;
to retrieve everything (all columns) from a table student/Select all records from tabl :
SELECT * FROM student ;
To display details of employee whose name ends with ‘y’.
SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘%y’ ;
To display names of employee whose name starts with R in EMPLOYEE table, the command is :
SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE ‘R%’ ;
To display details of employee whose second character in name is ‘e’
SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘_e%’ ;
To display details of employee whose grade value is A1 or A2 :
SELECT * FROM EMPLOYEE WHERE GRADE IN (‘A1’ , ‘A2’);
To display ECODE, ENAME and GRADE of those employees whose salary is between 40000 and 50000, command is:
SELECT ECODE , ENAME ,GRADE FROM EMPLOYEE WHERE GROSS BETWEEN 40000 AND 50000 ;
To display the details of employees in EMPLOYEE table in alphabetical order, we use command :
SELECT * FROM EMPLOYEE ORDER BY ENAME ;
Display list of employee in descending alphabetical order whose salary is greater than 40000.
SELECT ENAME FROM EMPLOYEE
WHERE GROSS > 40000 ORDER BY ENAME desc ;
Change the salary of employee in EMPLOYEE table having employee code 1009 to 55000.
UPDATE EMPLOYEE SET GROSS = 55000 WHERE ECODE = 1009 ;
Update the salary to 58000 and grade to B2 for those employee have employee code is 1001.
UPDATE EMPLOYEE SET GROSS = 58000, GRADE=’B2’ WHERE ECODE = 1009 ;
Double the salary of employees having grade as ‘A1’ or ‘A2’ .
UPDATE EMPLOYEE SET GROSS = GROSS * 2 WHERE GRADE=’A1’ OR GRADE=’A2’ ;
To remove the details of those employee from EMPLOYEE table whose grade is A1.
DELETE FROM EMPLOYEE WHERE GRADE =’A1’ ;
To delete all records from employee table :
DELETE FROM EMPLOYEE ;
To add a new column ADDRESS to the EMPLOYEE table, we can write command as :
ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR(50);
In table EMPLOYEE , change the datatype of GRADE column from CHAR(2) to VARCHAR(2).
ALTER TABLE EMPLOYEE MODIFY GRADE VARCHAR(2);
In table EMPLOYEE , change the column ENAME to EM_NAME and data type from VARCHAR(20) to VARCHAR(30).
ALTER TABLE EMPLOYEE CHANGE ENAME EM_NAME VARCHAR(30);
To delete column GRADE from table EMPLOYEE, we will write :
ALTER TABLE EMPLOYEE DROP GRADE ;
Q8. Write only digits from one text file to another to another text file
fr=open('read.txt',"r")
fw=open("write.txt",'w')
s=fr.read()
w=s.split()
for i in w:
for c in i:
if c.isdigit(): # check character for digit
fw.write(c)
fr.close()
fw.close()
Q9.Writing upper, lower case characters and other characters input by keyboard in different files
f1=open('lower.txt',"w")
f2=open('upper.txt',"w")
f3=open('other.txt',"w")
n=int(input("How many characters you want to enter "))
for i in range(n):
ch=input("Enter a character")
if ch.islower():
f1.write(ch)
elif ch.isupper():
f2.write(ch)
else:
f3.write(ch)
f1.close()
f2.close()
f3.close()
Q10. Reading lines from one file and write into another file starting from 's'
r=open('read.txt',"r") # open file in read mode
w=open("write.txt",'w') # open file in write mode
lines=r.readlines()
for line in lines:
if(line[0]=='s'): # write all lines start from 's' from read.txt to write.txt
w.write(line)
r.close()
w.close()
Q11. Writing and reading integers from binary file
import pickle
f=open('numbers.dat','wb')
n=int(input("how many number you want to enter?"))
for i in range(n):
x=int(input("enter number\n"))
pickle.dump(x,f)
f.close()
f=open('numbers.dat','rb')
for i in range(n):
y=pickle.load(f) #Read object from a pickle file or binary file
print(y)
f.close()
Q12. Write employee name and salary into binary file emp.dat
import pickle
f=open('emp.dat','wb')
n=int(input("Enter no of employees\n"))
d={}
i=1
while i<=n:
name=input("Enter name")
salary=int(input("Enter salary"))
d[name]=salary
i=i+1
pickle.dump(d,f) # dump d into f
f.close()
Q13. Display name of employees where salary greater then 10000 from binary file emp.dat
import pickle
f=open("emp.dat","rb")
rd=pickle.load(f)
for key,value in rd.items():
if(value>4000):
print(key)
Q14. read data from f1.csv and write in f2.csv File(Copy data from one file to another)
import csv
r=open("f1.csv",'r')
w=open("f1.csv",'w')
rf = csv.reader(r)
wf = csv.writer(w)
for row in rf:
wf.writerow(row)
Q16. Write a program to delete a record from student.csv by giving the name of student
import csv
record = list()
sname= input("Please enter student name to delete:")
with open('student.csv', 'r') as f:
data = csv.reader(f)
for row in data:
record.append(row)
for field in row:
if field == sname:
record.remove(row)
with open('student.csv', 'w') as f:
writer = csv.writer(f)
writer.writerows(record)
Q17. Write a program to read abc.csv file and display all its records
import csv
f=open("abc.csv",'r')
r=csv.reader(f) # canvert file object into reader object
for row in r:
print(row)
SQL Queries
Creating table:
CREATE TABLE employee
(ECODE int , ENAME varchar(20) , GENDER char(1) , GRADE char(2) , GROSS int ) ;
Inserting record:
INSERT INTO employee VALUES(1001 , ‘Ravi’ , ‘M’ , ‘E4’ , 50000);
To select only ENAME, GRADE and GROSS column from student table
SELECT ENAME , GRADE , GROSS FROM EMPLOYEE ;
to retrieve everything (all columns) from a table student/Select all records from tabl :
SELECT * FROM student ;
To display details of employee whose name ends with ‘y’.
SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘%y’ ;
To display names of employee whose name starts with R in EMPLOYEE table, the command is :
SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE ‘R%’ ;
To display details of employee whose second character in name is ‘e’
SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘_e%’ ;
To display details of employee whose grade value is A1 or A2 :
SELECT * FROM EMPLOYEE WHERE GRADE IN (‘A1’ , ‘A2’);
To display ECODE, ENAME and GRADE of those employees whose salary is between 40000 and 50000, command is:
SELECT ECODE , ENAME ,GRADE FROM EMPLOYEE WHERE GROSS BETWEEN 40000 AND 50000 ;
To display the details of employees in EMPLOYEE table in alphabetical order, we use command :
SELECT * FROM EMPLOYEE ORDER BY ENAME ;
Display list of employee in descending alphabetical order whose salary is greater than 40000.
SELECT ENAME FROM EMPLOYEE
WHERE GROSS > 40000 ORDER BY ENAME desc ;
Change the salary of employee in EMPLOYEE table having employee code 1009 to 55000.
UPDATE EMPLOYEE SET GROSS = 55000 WHERE ECODE = 1009 ;
Update the salary to 58000 and grade to B2 for those employee have employee code is 1001.
UPDATE EMPLOYEE SET GROSS = 58000, GRADE=’B2’ WHERE ECODE = 1009 ;
Double the salary of employees having grade as ‘A1’ or ‘A2’ .
UPDATE EMPLOYEE SET GROSS = GROSS * 2 WHERE GRADE=’A1’ OR GRADE=’A2’ ;
To remove the details of those employee from EMPLOYEE table whose grade is A1.
DELETE FROM EMPLOYEE WHERE GRADE =’A1’ ;
To delete all records from employee table :
DELETE FROM EMPLOYEE ;
To add a new column ADDRESS to the EMPLOYEE table, we can write command as :
ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR(50);
In table EMPLOYEE , change the datatype of GRADE column from CHAR(2) to VARCHAR(2).
ALTER TABLE EMPLOYEE MODIFY GRADE VARCHAR(2);
In table EMPLOYEE , change the column ENAME to EM_NAME and data type from VARCHAR(20) to VARCHAR(30).
ALTER TABLE EMPLOYEE CHANGE ENAME EM_NAME VARCHAR(30);
To delete column GRADE from table EMPLOYEE, we will write :
ALTER TABLE EMPLOYEE DROP GRADE ;
Python Database Connectivity
Basically the process of transfer data between python programs and MySQL database is known as Python Database Connectivity. There few steps you have to follow to perform Python Database Connectivity. These steps are as follow:
1.Import the required packages
2.Establish a connection
3.Execute SQL command
4.Perform operations as per the requirements
Import the required packages
To perform the Python MySQL Database Connectivity you need to install mysql-connector-python or pymysql package using pip command. First of all check it is installed or not using pip list command.
pip install mysql connector python
After installation just write the import statement to import the package in python code.
import mysql.connector as msql
Establish a connection
To establish a connection you need to create a connection object in Python. Take a variable as a connection object and use connect() function with MySQL database specification like host name, username, passoword or passwd and database itself. For example cn. Observe the code:
import mysql.connector as msql
cn=msql.connect(host='localhost',user='root',passwd='root',database=' jnvchandigarh') ')
Execute SQL command and fetch rows
The next step after the successful connection is to write SQL command and fetch rows. The SQL commands are used to perform DML operations and fetch rows read data from table.
You have to create a cursor object for executing SQL command and fetch rows. Cursor object is a special kind of structure that processes the data row by row in database. You can create cursor object in the following manner.
c=con.cursor()
Performing DML operations (insert, update and delete)
To perform the DML operations like insert, update or delete follow these steps:
Create a cursor object
Write command as parameters for execute() function
Use commit() function to save the changes and reflect the data in the table.
insert command
import mysql.connector as msql
cn=msql.connect(host='localhost',user='root',passwd=‘ ',database=‘jnvchandigarh')
cur=cn.cursor()
cur.execute("insert into students values(1111,'Asmita',78.50,'B1')
cn.commit()
Select Command
As you know the select command is used retrieve records from the database. The result is available in the resultset or dataset. You can store the select the command in cursor object in python. Then for resultset you can use the fetch…() function. These are:
1.fetchall(): It will retrieve all data from a database table in form of record or tuple or a row.
2.fetchone(): It will retrieve one record from the resultset as a tuple or a list. It returns the records in a specific order like first record, the next time next record and so on. If records are not available then it will return None.
3.fetchmany(): It will retrieve n number of records from the database. If records are not available then it will return an empty tuple.
4.rowcount: It is one of the properties of cursor object that return number of rows fetched from the cursor object.
Using fetchall()
import mysql.connector as msql
cn=msql.connect(host='localhost',user='root',passwd=‘ ',database=‘jnvchandigarh')
cur=cn.cursor()
cur.execute("select * from students")
d=cursor.fetchall()
for r in d:
print(r)
Database Connectivity commands:
import mysql.connector as sql mydb=sql.connect(host="localhost",user="root",passwd="") mycursor=mydb.cursor()
qry=“show databases"
mycursor.execute(qry)
SELECT QUERY
qry="select * from student" mycursor.execute() records=mycursor.fetchall()
for i in records:
print(i)
INSER QUERY
qry="insert into student values(2,'VIJAY',17,'CHANDIGARH') "
mycursor.execute(qry)
mydb.commit()
print(mycursor.rowcount,"record inserted")
UPDATE QUERY
qry="update student set age=25 where rollno=2 "
mycursor.execute(qry)
mydb.commit()
print(mycursor.rowcount,"Record updated")
DELETE QUERY
qry="delete from student where name='vinay' "
mycursor.execute()
mydb.commit()
print(mycursor.rowcount,"Record deleted")
INSERT QUERY
rollno=int(input("enter your rollno"))
nm=input("enter name of student")
age=int(input("enter age"))
cty=input("enter your city")
data=(rollno,nm,age,cty)
qry="insert into student values(%s,%s,%s,%s)“
mycursor.execute(qry,data)
mydb.commit()
print(mycursor.rowcount,"Record updated")
UPDATE QUERY
roll=int(input("enter ROLLNO of student"))
age=int(input("enter new age")) data=(age,roll)
qry="update student set age=%s where rollno=%s"
mycursor.execute(qry,data)
mydb.commit()
print(mycursor.rowcount,"Record updated")
DELETE QUERY
roll=input("enter rollno of student") data=(roll,)
qry="delete from student where rollno=%s"
mycursor.execute(qry,data)
mydb.commit()
print(mycursor.rowcount,"Record deleted")
SELECT QUERY
roll=input("enter rollno of student") data=(roll,)
qry="SELECT * FROM student WHERE rollno=%s"
mycursor.execute(qry,data)
records=mycursor.fetchall()
for i in records:
print(i)
mydb.commit()
print(mycursor.rowcount,"Records displayed")
Page updated
Google Sites
Report abuse