如何实现mysql数据库交汇

Question

我电脑1写了个Python操作mysql数据库的代码，封装为exe，发给另一台电脑2。我想把电脑1作为类似服务器的角色，那么应该怎么做，才能电脑2运行exe进行数据库操作后，我这边的电脑1的数据库也会发生改变呢？代码如下。

import pymysql
from tkinter import *

# 创建数据库连接
db = pymysql.connect(host='localhost', user='root', password='1996woaini147A', database='mydatabase')
# 创建游标对象。游标对象用于执行SQL命令和获取结果。
cursor = db.cursor()

def select_all():
    cursor.execute("SELECT * FROM mytable")
    results = cursor.fetchall()
    for row in results:
        print(row)

def open_insert_window():
    insert_window = Toplevel(root)
    Label(insert_window, text="Name").grid(row=0)
    Label(insert_window, text="Age").grid(row=1)
    Label(insert_window, text="Major").grid(row=2)

    name_entry = Entry(insert_window)
    age_entry = Entry(insert_window)
    major_entry = Entry(insert_window)

    name_entry.grid(row=0, column=1)
    age_entry.grid(row=1, column=1)
    major_entry.grid(row=2, column=1)

    Button(insert_window, text='Insert', command=lambda: insert_data(name_entry.get(), int(age_entry.get()), major_entry.get())).grid(row=3, column=1)

def insert_data(name, age, major):
    sql = "INSERT INTO mytable (name, age, major) VALUES (%s, %s, %s)"
    values = (name, age, major)
    cursor.execute(sql, values)
    db.commit()

def update_data():
    sql = "UPDATE mytable SET major = %s WHERE name = %s"
    values = ('Data Science', 'Tom')
    cursor.execute(sql, values)
    db.commit()

def delete_data():
    sql = "DELETE FROM mytable WHERE age > %s"
    values = (22, )
    cursor.execute(sql, values)
    db.commit()

# 创建Tkinter窗口
root = Tk()
# 设置窗口标题
root.title("myfirsttry")

# 创建按钮
select_button = Button(root, text="Select All", command=select_all)
insert_button = Button(root, text="Insert Data", command=insert_data)
update_button = Button(root, text="Update Data", command=update_data)
delete_button = Button(root, text="Delete Data", command=delete_data)
open_insert_window_button = Button(root, text="open_insert_window", command=open_insert_window)

# 将按钮添加到窗口
select_button.grid(row=0, column=1)
insert_button.grid(row=0, column=2)
update_button.grid(row=0, column=3)
delete_button.grid(row=0, column=4)
open_insert_window_button.grid(row=0, column=5)
# 运行Tkinter事件循环。如果没有这行代码，窗口会立即打开然后关闭，因为程序没有进入事件循环，所以无法响应用户的操作。
root.mainloop()

# 关闭游标对象
cursor.close()
# 关闭数据库连接
db.close()

Answer 1

上午好☀️☀️☀️️
本答案参考ChatGPT-3.5

问题：如何实现mysql数据库交汇？

解决方案：

1. 将第一台电脑的数据库作为服务器，第二台电脑通过网络连接到该服务器。

2. 在服务器电脑上安装mysql服务，配置好相关环境参数，建立数据库连接，等待客户端的请求。

3. 在客户端电脑上，使用Python的pymysql库进行数据库连接，在代码中指定服务器的IP地址和端口号，这样就可以连接到服务器了。

4. 在客户端电脑上进行的任何数据库操作，都会发送到服务器上进行处理，服务器的数据库也会相应地发生改变。

5. 如果要实现双向交互，也就是客户端电脑和服务器电脑之间的数据交互，可以在代码中使用socket进行通信，客户端电脑和服务器电脑之间相互发送数据。

修改后的代码：

服务器端：

import pymysql
import socket

# 创建数据库连接
db = pymysql.connect(host='localhost', user='root', password='1996woaini147A', database='mydatabase')
# 创建游标对象。游标对象用于执行SQL命令和获取结果。
cursor = db.cursor()

# 创建socket
server_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
# 绑定IP地址和端口号
server_socket.bind(('localhost', 9999))
# 监听端口
server_socket.listen(5)

while True:
    # 等待客户端连接
    client_socket, addr = server_socket.accept()
    print(f"Connection from {addr} has been established!")
    
    # 接收客户端请求，并根据请求执行相应的操作
    data = client_socket.recv(1024).decode()
    if data == "select_all":
        cursor.execute("SELECT * FROM mytable")
        results = cursor.fetchall()
        client_socket.send(str(results).encode())
    elif data == "insert":
        name = client_socket.recv(1024).decode()
        age = int(client_socket.recv(1024).decode())
        major = client_socket.recv(1024).decode()
        sql = "INSERT INTO mytable (name, age, major) VALUES (%s, %s, %s)"
        values = (name, age, major)
        cursor.execute(sql, values)
        db.commit()
        client_socket.send("Insertion successful!".encode())
    elif data == "update":
        major = "Data Science"
        name = "Tom"
        sql = "UPDATE mytable SET major = %s WHERE name = %s"
        values = (major, name)
        cursor.execute(sql, values)
        db.commit()
        client_socket.send("Update successful!".encode())
    elif data == "delete":
        sql = "DELETE FROM mytable WHERE age > %s"
        values = (22, )
        cursor.execute(sql, values)
        db.commit()
        client_socket.send("Deletion successful!".encode())
    
    # 关闭socket
    client_socket.close()

# 关闭游标对象
cursor.close()
# 关闭数据库连接
db.close()

客户端：

import socket

# 创建socket
client_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
# 连接服务器
client_socket.connect(('localhost', 9999))

def select_all():
    client_socket.send('select_all'.encode())
    results = client_socket.recv(1024).decode()
    print(results)

def insert_data():
    name = input("Name: ")
    age = input("Age: ")
    major = input("Major: ")
    client_socket.send('insert'.encode())
    client_socket.send(name.encode())
    client_socket.send(age.encode())
    client_socket.send(major.encode())
    message = client_socket.recv(1024).decode()
    print(message)

def update_data():
    client_socket.send('update'.encode())
    message = client_socket.recv(1024).decode()
    print(message)

def delete_data():
    client_socket.send('delete'.encode())
    message = client_socket.recv(1024).decode()
    print(message)

while True:
    # 显示菜单
    print("1. Select All")
    print("2. Insert Data")
    print("3. Update Data")
    print("4. Delete Data")
    print("5. Quit")
    choice = input("Enter your choice: ")
    
    # 根据选择执行相应的操作
    if choice == '1':
        select_all()
    elif choice == '2':
        insert_data()
    elif choice == '3':
        update_data()
    elif choice == '4':
        delete_data()
    elif choice == '5':
        break
    
# 关闭socket
client_socket.close()