-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathlib_mysql.py
165 lines (154 loc) · 6.09 KB
/
lib_mysql.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
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
#!/usr/bin/python
import sys
sys.dont_write_bytecode = True # No '*.pyc' precompiled files
import mysql.connector
from mysql.connector import Error
class MySqlConnector:
'''
Creates a DB connection object.
'''
def __init__(self, database, db_user, db_password, host='localhost'):
self.host = host
self.database = database
self.db_user = db_user
self.db_password = db_password
self.connection = None
self.records = None
self.db_cursor = None
def connect(self):
print('Trying to connect to the db:', self.database)
if self.connection is None:
try:
self.connection = mysql.connector.connect(
host=self.host, database=self.database,
user=self.db_user, password=self.db_password
)
self.db_cursor = self.connection.cursor(buffered=True)
print('The connection to the database', str(self.database), 'was successful.')
except Error as e:
print('Error connecting to the database:', str(self.database), e)
self.connection = None
else:
print('The connection was already active.')
def disconnect(self):
self.db_cursor = None
print('Trying to disconnect from the database...')
if self.connection is not None:
self.connection.close()
self.connection = None
print('Disconnected successfully.')
print('Connection object after:', self.connection)
else:
print('There was no active connection.')
def insert_into_table(self, table, records):
'''
Args:
table:
The table to be updated.
records:
A 2 dimensional iterable (list or tuple)
One element (record) contains values,
that will be inserted into the table's field in one record.
E.g.
records[0] = ['val_aa', 'val_bb', 'val_cc']
records[1] = ['val_dd', 'val_ee', 'val_ff']
Etc...
Return: None
'''
def query_insert_ending(record):
'''
This nested function is used to create the ending part of the SQL statement:
'INSERT INTO'
It overcomes the issue,
that the number of fields to be inserted may always vary.
Params:
record: (list/tuple)
Returns:
query_ending: (str)
It is the ending part of the 'INSERT INTO' query.
This returns a string with the values in brackets. E.g.:
('aa', 'bb', 'cc');
'''
query_ending = ''
query_ending = ''.join((query_ending, '('))
query_ending = ''.join((query_ending, '\'', str(record[0]), '\''))
if len(record) > 1:
for field in record[1:]: # As the [0] element was added above
query_ending = ''.join((query_ending, ', ', '\'', str(field), '\''))
query_ending = ''.join((query_ending, ');'))
return query_ending
for record in records:
try:
sql_command = 'INSERT INTO ' + self.database + '.' + table + ' VALUES ' \
+ query_insert_ending(record)
print('Executing the SQL query:', sql_command)
self.db_cursor.execute(sql_command)
self.connection.commit()
print('A record was inserted.')
except Error as e:
print('Error inserting the record:')
print(e)
def custom_sql_query(self, query):
'''
query (str): A custom SQL query
'''
output_records = None
try:
print('Executing the SQL query:')
print(query)
self.db_cursor.execute(query)
print('Query executed.')
output_records = self.db_cursor.fetchall()
except Error as e:
print('Error selecting the records:')
print(e)
return output_records
def select_from_table(self, table, column='*', condition='', verbose=True):
'''
condition: (str)
An SQL condition
Examples.: 'id=555' 'number>1'
(Do not confuse it with a Python condition statement: 'id == 55').
:return: None
'''
<
5D94
div>
output_records = None
try:
query_ending = '' if condition == '' else ' WHERE ' + str(condition)
sql_command = (
'SELECT ' + column + ' FROM ' + self.database + '.' + table + query_ending + ';'
)
if verbose == True:
print('Executing the SQL query:')
print(sql_command)
self.db_cursor.execute(sql_command)
if verbose == True:
print('Selecting finished.')
output_records = self.db_cursor.fetchall()
except Error as e:
print('Error selecting the records:')
print(e)
return output_records
def delete_from_table(self, table, condition=None):
'''
condition: (str)
An SQL condition, not a Python condition!
Examples.:
'id=555'
'number>1'
Default = ''
:return: None
'''
query_ending = '' if condition in ('', None) else ' WHERE ' + str(condition)
try:
sql_command = 'DELETE FROM ' + self.database + '.' + table + query_ending
print('Executing the SQL query:', sql_command)
self.db_cursor.execute(sql_command)
self.connection.commit()
print('Deletion finished.')
except Error as e:
print('Error deleting the records:')
print(e)
def main():
print('This is a library for DB operations.')
if __name__ == '__main__':
main()