import pymysql
class MySQLDB:
def __init__(self, host, username, password, database, port=3306):
self.host = host
self.username = username
self.password = password
self.database = database
self.port = port
self.connection = None
def connect(self):
if not self.connection:
self.connection = pymysql.connect(
host=self.host,
user=self.username,
passwd=self.password,
database=self.database,
port=self.port,
)
# print("MySQL Database Connected Successfully")
return self.connection
def close(self):
if self.connection:
self.connection.close()
print("MySQL Database Connection Closed")
def execute_query(self, query):
cursor = self.connect().cursor()
try:
cursor.execute(query)
last_id = cursor.lastrowid
self.connection.commit()
return last_id
# print("Query executed successfully")
except pymysql.Error as error:
print(f"Failed to execute query: {error}")
finally:
if cursor:
cursor.close()
def fetch_data(self, query, params=None):
cursor = self.connect().cursor()
try:
cursor.execute(query, params)
result = cursor.fetchall()
return result
except pymysql.Error as error:
print(f"Failed to fetch data: {error}")
finally:
if cursor:
cursor.close()
def insert_data(self, table_name, column_values_dict):
columns = ""
values = ""
for key in column_values_dict.keys():
columns += f"{key}, "
values += f"'{column_values_dict[key]}', "
columns = columns[:-2]
values = values[:-2]
query = f"INSERT INTO {table_name} ({columns}) VALUES ({values})"
return self.execute_query(query)
def update_data(self, table_name, column_values_dict, where_condition):
set_statement = ""
for key in column_values_dict.keys():
set_statement += f"{key}='{column_values_dict[key]}', "
set_statement = set_statement[:-2]
query = f"UPDATE {table_name} SET {set_statement} WHERE {where_condition}"
return self.execute_query(query)
def delete_data(self, table_name, where_condition):
query = f"DELETE FROM {table_name} WHERE {where_condition}"
return self.execute_query(query)
#
#def mysqltest():
# dbname = 'tourism_test'
# # 连接数据库
# db = MySQLDB(dbhost, dbuser, dbpwd, dbname)
# db.connect()
# # Fetch data
# result = db.fetch_data('SELECT title FROM article limit 2')
# print(result)
# # Fetch data params
# params = ('%攻略%',)
# result = db.fetch_data('SELECT title FROM article WHERE title LIKE %s limit 2',params)
# print(result)
# # Insert data
# data = {'name': 'John Doe', 'email': 'xxxx@example.com'}
# db.insert_data('users', data)
# # Update data
# data = {'name': 'Jane Doe', 'email': 'xxxx@example.com'}
# where_condition = 'id=1'
# db.update_data('users', data, where_condition)
# # Delete data
# where_condition = 'id=1'
# db.delete_data('users', where_condition)```
网友评论