首页 > > 详细

ACIT 3910讲解、讲解SQL编程语言、辅导Database Administration、SQL辅导 解析Haskell程序|解析C/

ACIT 3910 Database Administration and Management
Assignment #1
ACIT 3910 Database Administration and
Management
Assignment #1
ACIT 3910 Database Administration and Management
Assignment #1
Patrick Guichon January 6, 2019 2 of 6
Introduction:
For this assignment you will be using Python to connect to a MySQL database to add to and modify the
existing word dictionary from your lab. This assignment expands upon the Connect Python and the
Import non SQL Data Labs.
Objectives:
Your short Python program will do the following:
1. Prompt the user on the command line for the following connection parameters:
a. Hostname:
Hostname, if you wish to connect to MySQL running on your VM, will be localhost.
b. Port:
The default port for MySQL is 3306.
c. Username:
You can use the root user to connect.
d. Password:
Your password for the root account.
2. With the connection parameters, attempt to connect to MySQL:
If the connection was unsuccessful print “Could not login to host with user/password provided.”
and exit the program.
If the connection was successful, print “Connected Successfully.” and continue to next step.
Hint: You may want to consider using exceptions (try: except:) and the exit() function.
3. Print to the screen the Version of MySQL running on the host and port provided.
4. Prompt the user for a word.
5. Run a query to check to see if the word provided by the user exists in the dictionary.word
table.
The dictionary.word table should already be created and populated with all the words
from the English dictionary as described in the Import non SQL Data instructions.
a. If the word exists in the dictionary.word table, prompt the user to change the
word to a new word. Update the database to change the old word to the new word.
b. If the word doesn’t exist in the dictionary.word table, add the word to the table.
For all queries to the database ensure you are properly escaping your inputs to prevent SQL Injection
Attacks.
Save your Python program in a text file called add_a_word.py.
THOUROUGHLY TEST your program to ensure it does what you expect it to do.
Refer to the Helpful Links section for documentation on Python and MySQL Connection Tips.
ACIT 3910 Database Administration and Management
Assignment #1
Patrick Guichon January 6, 2019 3 of 6
Examples of the output when the program is run:
1. Invalid Host, User/Password combination:
2. Correct Host, User/Password
Word selfie doesn’t exist in the database:
3. Correct Host, User/Password
Word hangry doesn’t exist in the database, either:
ACIT 3910 Database Administration and Management
Assignment #1
Patrick Guichon January 6, 2019 4 of 6
4. Correct Host, User/Password
Word apple exists in the database – prompt to change to apples:
5. Correct Host, User/Password
Change apples back to apple:
ACIT 3910 Database Administration and Management
Assignment #1
Patrick Guichon January 6, 2019 5 of 6
Marking Guide:
Criteria Marks
Prompts for and uses Host, Port, User, Password to connect to database. 2 marks
Prints “Connected Successfully.” on success and Prints “Could not login to host with
user/password provided.” and exits the program on invalid host and/or credentials.
2 marks
Prints the MySQL Version of the host. 2 marks
Properly adds the word prompted for on the console to the database (if it didn’t
exist).
2 marks
Properly changes the word to a new word prompted for on the console to the
database (if the word did exist).
2 marks
Queries are written to prevent SQL Injection Attacks 2 marks
Total: 12 marks
ACIT 3910 Database Administration and Management
Assignment #1
Patrick Guichon January 6, 2019 6 of 6
Helpful Links:
Console Python (print and input):
https://www.dotnetperls.com/console-python
Connect to MySQL from Python:
https://www.w3schools.com/python/python_mysql_getstarted.asp
https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor.html
Python MySQL Connection Exceptions:
https://dev.mysql.com/doc/connector-python/en/connector-python-api-errors-error.html
Python MySQL Selects (Prevent SQL Injection Attacks):
https://www.w3schools.com/python/python_mysql_where.asp
Python MySQL Inserts:
https://www.w3schools.com/python/python_mysql_insert.asp
Python MySQL Updates:
https://www.w3schools.com/python/python_mysql_update.asp
Get number of rows returned from a query in Python:
https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-rowcount.html

联系我们 - QQ: 99515681 微信:codinghelp
程序辅导网!