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.
- Some persons are students. Students can optionally have one major (a reference to a department).
- Some persons are professors. They are affiliated with one department.
- Write a python homework query that shows all students with a Texas home address.
- 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)