-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathtestingmysql_func.py
61 lines (52 loc) · 2.78 KB
/
testingmysql_func.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
import mysql.connector
def executeQueryAndReturnResult(query, host='localhost', username='root', password='root', port=3306,
database='store'):
"""
Executes a SQL query and returns the result set.
Args:
- query (str): The SQL query to execute.
- host (str): The hostname of the MySQL server. (Default is 'localhost'.)
- username (str): The username to connect to the MySQL server. (Default is 'root'.)
- password (str): The password to connect to the MySQL server. (Default is 'root'.)
- port (int): The port number of the MySQL server. (Default is 3306.)
- database (str): The name of the database to use. (Default is 'doctor_management_system'.)
Returns:
A tuple containing two elements:
- A list of strings representing the column names of the result set.
- A list of tuples, where each tuple contains the values of a single row in the result set.
If the query doesn't return any rows, the second element of the tuple will be an empty list.
If an error occurs during the execution of the query, an exception will be raised.
"""
with mysql.connector.connect(host=host, user=username, password=password, port=port, database=database) as conn:
with conn.cursor() as cursor:
cursor.execute(query)
result_set = cursor.fetchall()
if result_set is not None:
return cursor.column_names, result_set
else:
return None, []
def executeQueryAndCommit(query, host='localhost', username='root', password='root', port=3306, database='store'):
"""
Executes a query on the specified database and commits the changes.
Args:
- query (str): the SQL query to execute.
- host (str): the hostname of the MySQL server (Default is 'localhost').
- username (str): the username to connect to the MySQL server (Default is 'root').
- password (str): the password to connect to the MySQL server (Default is 'root').
- port (int): the port number of the MySQL server (default is 3306).
- database (str): The name of the database to use. (Default is 'doctor_management_system'.)
Returns:
An integer value representing the number of affected rows after executing the query.
"""
with mysql.connector.connect(host=host, user=username, password=password, port=port, database=database) as conn:
with conn.cursor() as cursor:
try:
cursor.execute(query)
except Exception as e:
print(e)
conn.commit()
row_count = cursor.rowcount
if row_count is not None:
return row_count
else:
return 0