/
database_test.py
117 lines (90 loc) · 4.39 KB
/
database_test.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
import database_queries # Contains functions for interacting with a mysql database
INPUT = False # Allows you to select whether database configuration variables are hardcoded or entered in the console
VERBOSE = True # Prints additional debug statements on the success/failure of calls to the database_queries.py file
print '\n********************************************'
print 'TEST SCRIPT FOR DATABASE FUNCTIONS'
print '********************************************\n'
if INPUT: # If set to True, variables collected here
print '--------------------------------------------'
print 'Collect Variables Required for Connection...'
print '--------------------------------------------'
username = raw_input('Enter a username: ')
database = raw_input('Enter a database: ')
password = raw_input('Enter a password: ')
addy = raw_input('Enter a host address: ')
else: # If set to False, variables are hardcoded here
print '--------------------------------------------'
print 'Variables Set for Connection'
print '--------------------------------------------'
username = 'root'
database = 'employees'
password = 'detachment'
addy = 'localhost'
print 'Username:\t' + username
print 'Database:\t' + database
print 'Password:\t' + password
print 'Address:\t' + addy
print '\n'
print '\n--------------------------------------------'
print 'Create Connection with Database'
print '--------------------------------------------'
print 'Attempting to connect...',
con = database_queries.dbConnect(username, password, database, addy) # Attempt connection and save session
if con['state'] == 0: # If connection flag is set - good!
print 'Connected!',
elif con['state'] == 1: # If connection flag is not set - fail!
print 'Failed',
#if VERBOSE: # If set prints the failure message received in initial call to connect
#print '[' + con['msg'] + ']'
print '\n'
print '\n--------------------------------------------'
print 'Get Tables from the Database'
print '--------------------------------------------'
print 'Attempt to query for table list...',
con = database_queries.dbTables(con) # Attempt to get tables in database
if con['msg'] == 'Tables retreived': # Check return msg for success
print 'Passed!',
if VERBOSE: # Print associated status message associated with call
print '[' + con['msg'] + ']\n'
for idx, table in enumerate(con['tables']): # Print out each table name line by line
print 'Table ' + str(idx) + ': ' + table[0] + '\n'
else: # Call to retrieve table(s) must have failed.
print 'Failed',
if VERBOSE:
print '[' + con['msg'] + ']' # Print associated message related to call failure
print '\n--------------------------------------------'
print 'Send Query (SELECT/DELETE/INSERT) to the Database'
print '--------------------------------------------'
user_query = raw_input('Enter a proper SQL query: ') # Intended to collect a mysql query from the user
print '\nYou entered: ' + user_query + '\n\n'
# attempt to get tables in database
print 'Attempting to send query to the database...',
con = database_queries.dbQuery(con, user_query) # Attempt to get tables in database
response = con['msg'].split() # Parse response message to determine if query worked or not
if response[0] == 'Successful':
print 'Passed!',
if VERBOSE:
print '[' + con['msg'] + ']' # Print associated success message
if response[1] == 'SELECT': # Print rows of a table if SELECT was chosen
# print each row
print '\nROWS IN SELECT:'
for idx,row in enumerate(con['rows']):
print 'row ' + str(idx) + ': ' + str(row)
# print row count of table
print 'Row Count: ' + str(con['row_cnt']) # Print the number of rows in the SELECT call
else: # Query failed
print 'Failed!',
if VERBOSE:
print '[' + con['msg'] + ']' # Print asssociated failure message
print '\n--------------------------------------------'
print 'Close Connection with Database'
print '--------------------------------------------'
print 'Attempt to disconnect...',
con = database_queries.dbClose(con) # Attempt to close connection
if con['state'] == 1: # Detect if connection state is closed
print 'Disconnected!',
elif con['state'] == 0: # Connection still exists (or atleast the state indicates that!)
print 'Failed',
if VERBOSE:
print '[' + con['msg'] + ']' # Print associated debug message
print '\n'