# Population Homework Solution in Python

July 11, 2024
Dr. David
🇬🇧 United Kingdom
Python
Dr. David Anderson holds a Ph.D. in Computer Science from Harvard University and has completed over 800 Python programming test assignments with exceptional accuracy and efficiency. His expertise encompasses a wide range of Python topics, including data structures, algorithms, web development, and machine learning.
Key Topics
• Population Database
Tip of the day
News

## 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) ```

## Related Samples

Explore our free Python assignment samples to see how we tackle various programming challenges. These samples demonstrate our expertise in Python programming and can help you understand our approach to solving assignments effectively.