ACIT 3910代做、代做SQL编程语言、代写Database Administration、SQL代写代写

日期：2019-10-02 10:07

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