Using Multiple Table for Querying
Python allows to query more than one table by
joining them. In the following example a new table called “Appointments” which
contain the details of students Rollno, Duty, Age is created. The tables
“student” and “Appointments” are joined and displayed the result with the
column headings.
Example
import sqlite3
connection =
sqlite3.connect("Academy.db")
cursor = connection.cursor()
cursor.execute("""DROP
TABLE Appointment;""")
sql_command = """
CREATE TABLE
Appointment(rollnointprimarkey,Dutyvarchar(10),age int)"""
cursor.execute(sql_command)
sql_command =
"""INSERT INTO Appointment (Rollno,Duty ,age )
VALUES ("1",
"Prefect", "17");"""
cursor.execute(sql_command)
sql_command =
"""INSERT INTO Appointment (Rollno, Duty, age)
VALUES ("2",
"Secretary", "16");"""
cursor.execute(sql_command)
#never forget this, if you want the changes to be
saved:
connection.commit()
cursor.execute("SELECT student.rollno,student.sname,Appointment.
Duty,Appointment.Age FROM student,Appointment where student.
rollno=Appointment.rollno")
#print (cursor.description) to display the field
names of the table
co = [i[0] for i in
cursor.description]
print(co)
#Field informations can be read from
cursor.description.
result = cursor.fetchall()
for r in result:
print(r)
OUTPUT
['Rollno', 'Sname', 'Duty',
'age']
(1, 'Akshay', 'Prefect', 17)
(2, 'Aravind', 'Secretary', 16)
Note
cursor. description contain the details of each
column headings .It will be stored as a tuple and the first one that is 0(zero)
index refers to the column name. Using this command you can display the table’s
Field names.
Related Topics
Privacy Policy, Terms and Conditions, DMCA Policy and Compliant
Copyright © 2018-2024 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.