Population Homework Solution in Python

Population Database

Exercise

Create and populate a university database that has departments, persons. Use faker to create at least 3000 persons. Persons must have a home address.

1. Some persons are students. Students can optionally have one major (a reference to a department).
2. Some persons are professors. They are affiliated with one department.
3. Write a python homework query that shows all students with a Texas home address.
4. Submit the Python code that creates and populates the database.

Solutions:

import pymysql import faker import random creds=dict() creds['host']="db.ust-db.link" creds['user']='velasqya' creds['password']='X!XXj$I9fp' conn = pymysql.connect(host=creds['host'], user=creds['user'], passwd=creds['password']) random.seed(0) Queries=["use hw4_team2", """ CREATE or replace TABLE People ( id int PRIMARY KEY, full_name varchar(255), isStudent boolean, stDepart varchar(255), isProfessor boolean, prDepart varchar(255), home varchar(255) ); """ , """ CREATE or replace TABLE Department ( address varchar(255), university varchar(255), primary key(address,university) ); """ , """ CREATE or replace TABLE build ( address varchar(255), buildingN int, city varchar(255), primary key(address,city) ); """ ] def student(cursor): state=fake.administrative_unit() college=state+" university" address=address=fake.address() if cursor.execute('select * from Department where address = "{}";'.format(address)) ==0: cursor.execute('insert into Department (address,university) values ("{}","{}")'.format(college,address)) return address+college def professor(cursor): state=fake.administrative_unit() college=state+" university" address=address=fake.address() if cursor.execute('select * from Department where address = "{}"'.format(address)) ==0: cursor.execute('insert into Department (university,address) values ("{}","{}")'.format(college,address)) return address+state def build(cursor): state=fake.administrative_unit() address=address=fake.address() state=fake.administrative_unit() if cursor.execute('select * from build where address = "{}" and city ="{}"'.format(address,state)) ==0: cursor.execute('insert into build (city,buildingN,address) values ("{}",{},"{}")'.format(state,fake.building_number(),address)) return address def createPerson(fake,i,cursor): name=fake.name() address=fake.address() isStudent=False isProfessor=False st=None pr=None addr=build(cursor) if random.random()<0.5: isStudent=True st=student(cursor) if random.random()<0.5: isProfessor=True pr=professor(cursor) if isStudent and isProfessor: q='insert into People (id,full_name,isStudent,stDepart,isProfessor,prDepart,home) value ({},"{}",true,"{}",true,"{}","{}")'.format(i+2000,name,st,isProfessor,pr,addr) elif not isStudent and isProfessor: q='insert into People (id,full_name,isStudent,stDepart,isProfessor,prDepart,home) value ({},"{}",false,NULL,true,"{}","{}")'.format(i+2000,name,isProfessor,pr,addr) elif isStudent and not isProfessor: q='insert into People (id,full_name,isStudent,stDepart,isProfessor,prDepart,home) value ({},"{}",true,"{}",false,NULL,"{}")'.format(i+2000,name,st,pr,addr) elif not isStudent and not isProfessor: q='insert into People (id,full_name,isStudent,stDepart,isProfessor,prDepart,home) value ({},"{}",false,NULL,false,NULL,"{}")'.format(i+2000,name,pr,addr) cursor.execute(q) cursor=conn.cursor() for q in Queries: cursor.execute(q) fake= faker.Faker(['en-US'])['en-US'] faker.Faker.seed(0) for i in range(3000): createPerson(fake,i,cursor) print("person {} added".format(i)) cursor.execute('select * from People join build where build.address = People.home and (build.city="Texas" or build.city="texas")') for i in cursor.fetchall(): print(*i)