Python编程调试、讲解C++程序设计、c/c++程序语言辅导
- 首页 >> Matlab编程 This assignment gives you a brief introduction to connecting to a
database from a program. It will also show you a bit about how You can
write this in either C++, using libpqxx, or in Python, using psycopg2. I
did a solution in Python, which I think is easier.
The assignment:
1. Write 2 programs, one to populate a very simple movie table and one to query that
table.
2. Start by using pgAdmin or psql to create a database called 431hw6
3. For the population program:
1. To help with grading, please use the same schema as in homeworks 1-3. In
your population program, start by creating the schema. As with creating the
movie table below, use the “IF NOT EXISTS” option of the CREATE
SCHEMA command (PostgreSQL: Documentation: 13: CREATE
SCHEMA) so that you can re-run this file without having to manually
remove the schema/table
2. The database to create only has one table called “schema.movie” with the
following fields:
1. title – a varchar field for the title of the movie
2. yr – an int field for the release year of the movie
3. Use a loop to read in some movies and insert them into the table.
4. To get full credit, you do need to use the features of either libpqxx or
psycopg2 to prevent SQL injection attacks (libpqxx: String
escaping or https://www.psycopg.org/docs/sql.html). For the latter, note that
you will be using the sql.Literal() function here since you are replacing
values in the INSERT statement. There is no need to use this feature for the
CREATE statements since those do not include any user input.
5. If you run the program in a CMD/Terminal window, it should look like this:
C:\Users\villa\Downloads\431\sp21>python hw6soln.py
Populating movie database
1
Enter movie title: Nomadland
Enter year of release: 2020
Are you done? (y/n)n
Enter movie title: Da 5 Bloods
Enter year of release: 2020
Are you done? (y/n)n
Enter movie title: First Cow
Enter year of release: 2019
Are you done? (y/n)y
4. For the query program:
1. Use a loop to retrieve results from the table, either searching by title or by
year.
2. You should assume you may get multiple results for either kind of query
3. Gracefully handle the case where you get no results
4. Again, you should protect the query from SQL injection
5. Within the same loop, get a count of the number of movies in the table [We
will use the behavior of these two programs for a discussion of Postgresql’s
concurrency control, and the number of movies might help you understand
the examples better.]
6. Here is sample output if run from a CMD/Terminal window:
C:\Users\villa\Downloads\431\sp21>python hw6bsoln.py
Searching for movies by title or by year
Search for title? (y/n) y
Enter movie title: First Cow
title yr
--------------------
First Cow 2019
Number of movies in database = 3
Are you done? (y/n) n
Search for title? (y/n) n
Enter year of release: 2020
title yr
--------------------
Nomadland 2020
Da 5 Bloods 2020
Number of movies in database = 3
2
Are you done? (y/n) n
Search for title? (y/n) n
Enter year of release: 2021
No results match
Number of movies in database = 3
Are you done? (y/n) y
5. Note on style: it is ok to hard-code a lot here, like the names of the database and user.
You do have to input the values to insert, pick decent variable names and provide some
useful comments.
6. Submit the two programs to Blackboard, either as two .cpp files or as two .py files
Grading:
40% for population program
10% create table
30% insert records from user input
40% for query program
30% searching by title and by year
10% getting number of movies in table
10% properly guard against SQL injection attacks
10% style
3
database from a program. It will also show you a bit about how You can
write this in either C++, using libpqxx, or in Python, using psycopg2. I
did a solution in Python, which I think is easier.
The assignment:
1. Write 2 programs, one to populate a very simple movie table and one to query that
table.
2. Start by using pgAdmin or psql to create a database called 431hw6
3. For the population program:
1. To help with grading, please use the same schema as in homeworks 1-3. In
your population program, start by creating the schema. As with creating the
movie table below, use the “IF NOT EXISTS” option of the CREATE
SCHEMA command (PostgreSQL: Documentation: 13: CREATE
SCHEMA) so that you can re-run this file without having to manually
remove the schema/table
2. The database to create only has one table called “schema.movie” with the
following fields:
1. title – a varchar field for the title of the movie
2. yr – an int field for the release year of the movie
3. Use a loop to read in some movies and insert them into the table.
4. To get full credit, you do need to use the features of either libpqxx or
psycopg2 to prevent SQL injection attacks (libpqxx: String
escaping or https://www.psycopg.org/docs/sql.html). For the latter, note that
you will be using the sql.Literal() function here since you are replacing
values in the INSERT statement. There is no need to use this feature for the
CREATE statements since those do not include any user input.
5. If you run the program in a CMD/Terminal window, it should look like this:
C:\Users\villa\Downloads\431\sp21>python hw6soln.py
Populating movie database
1
Enter movie title: Nomadland
Enter year of release: 2020
Are you done? (y/n)n
Enter movie title: Da 5 Bloods
Enter year of release: 2020
Are you done? (y/n)n
Enter movie title: First Cow
Enter year of release: 2019
Are you done? (y/n)y
4. For the query program:
1. Use a loop to retrieve results from the table, either searching by title or by
year.
2. You should assume you may get multiple results for either kind of query
3. Gracefully handle the case where you get no results
4. Again, you should protect the query from SQL injection
5. Within the same loop, get a count of the number of movies in the table [We
will use the behavior of these two programs for a discussion of Postgresql’s
concurrency control, and the number of movies might help you understand
the examples better.]
6. Here is sample output if run from a CMD/Terminal window:
C:\Users\villa\Downloads\431\sp21>python hw6bsoln.py
Searching for movies by title or by year
Search for title? (y/n) y
Enter movie title: First Cow
title yr
--------------------
First Cow 2019
Number of movies in database = 3
Are you done? (y/n) n
Search for title? (y/n) n
Enter year of release: 2020
title yr
--------------------
Nomadland 2020
Da 5 Bloods 2020
Number of movies in database = 3
2
Are you done? (y/n) n
Search for title? (y/n) n
Enter year of release: 2021
No results match
Number of movies in database = 3
Are you done? (y/n) y
5. Note on style: it is ok to hard-code a lot here, like the names of the database and user.
You do have to input the values to insert, pick decent variable names and provide some
useful comments.
6. Submit the two programs to Blackboard, either as two .cpp files or as two .py files
Grading:
40% for population program
10% create table
30% insert records from user input
40% for query program
30% searching by title and by year
10% getting number of movies in table
10% properly guard against SQL injection attacks
10% style
3