sqlite3处理两个不同的表

# Author: Lu Daze
# Creatation date: 2020.3.2
# Last modification date: 2020.3.2
# Function description: Using python to create a database with sqlite3, and merge different tables according to the same data with the method of 'JOIN'
import sqlite3
conn_info=sqlite3.connect('information.db')
# Now we have created A database named infomation.db
c_i=conn_info.cursor()
# The following codes will create a table named STUDENT and AWARD
str_stu='''CREATE TABLE STUDENT(
        Sno CHAR(8),
        Sname CHAR(10),
        Ssex CHAR(2),
        Sage SMALLINT,
        Major CHAR(20)
        );'''
str_awa='''CREATE TABLE AWARD(
        Sno CHAR(8),
        Details VARCHAR(2000)
        );'''
c_i.execute(str_stu)
c_i.execute(str_awa)
# Now, we have created these two table, then we add some data into the table
in_stu='''INSERT INTO STUDENT(Sno,Sname,Ssex,Sage,Major)
          VALUES('20100001','Shi Mingyu','girl',20,'computer')'''
in_awa='''INSERT INTO AWARD(Sno,Details)
          VALUES('20100001','University Scholarships for 2011, national scholarship for 2012')'''
c_i.execute(in_stu)
c_i.execute(in_awa)
print('The database and tables have successfully created!')

# Show the data in these two tables
print('The data in STUDENT is:')
student=c_i.execute('SELECT * FROM STUDENT')
for s in student:
    print('Sno=',s[0])
    print('Sname=',s[1])
    print('Sage=',s[2])
    print('Major=',s[3])
print('The data in AWARD is:')
award=c_i.execute('SELECT * FROM AWARD')
for a in award:
    print('Sno=',a)
    print('Details=',a[1])
# Merge these two tables with the same information 'Sno'
str_mer='''SELECT A.Sno,Sname,Ssex,Sage,Major,Details FROM STUDENT A LEFT JOIN AWARD B ON A.Sno=B.Sno WHERE A.Sno='20100001';'''
c_i.execute(str_mer)
conn_info.commit()