|
|
import pymysql
|
|
|
|
|
|
# 连接数据库 创建连接对象 con
|
|
|
con = pymysql.connect(
|
|
|
host="localhost", # 主机名，使用默认即可（localhost）
|
|
|
user="root", # 用户名，一般是root
|
|
|
password="924604223", # 密码，登录数据库的密码，这个是自己设置的
|
|
|
database="atm" # 要连接的数据库
|
|
|
)
|
|
|
print("数据库连接成功！！！")
|
|
|
|
|
|
|
|
|
# 使用 cursor() 方法创建一个游标对象 cursor
|
|
|
cursor = con.cursor()
|
|
|
# 创建用户表(users)，包含账户（account）、密码(pwd)、金额（money）、账户记录（record)等
|
|
|
create_table_sql = """
|
|
|
create table if not exists user(
|
|
|
account VARCHAR(20) PRIMARY KEY,
|
|
|
pwd VARCHAR(50) NOT NULL,
|
|
|
money float(10,2),
|
|
|
record VARCHAR(1025),
|
|
|
cardLock INT,
|
|
|
id VARCHAR(20)
|
|
|
)
|
|
|
"""
|
|
|
# 使用execute()方法执行数据库命令
|
|
|
cursor.execute(create_table_sql)
|
|
|
print("用户表创建成功！！！")
|
|
|
|
|
|
|
|
|
def insert_data(account, pwd, id):
|
|
|
try:
|
|
|
# 插入数据的SQL语句
|
|
|
insert_sql = "INSERT INTO user (account, pwd,money,record,cardLock,id) VALUES (%s, %s, %s, %s, %s, %s)"
|
|
|
data = (account, pwd, 0, "", 0, id)
|
|
|
# 执行SQL语句
|
|
|
cursor.execute(insert_sql, data)
|
|
|
# 提交事务
|
|
|
con.commit()
|
|
|
except:
|
|
|
print("该账号已存在")
|
|
|
|
|
|
a =[]
|
|
|
def select_data():
|
|
|
# 查询数据的SQL语句
|
|
|
select_sql = "SELECT * FROM user"
|
|
|
# 执行SQL语句
|
|
|
cursor.execute(select_sql)
|
|
|
# 获取查询结果
|
|
|
results = cursor.fetchall()
|
|
|
for row in results:
|
|
|
a.append(list(row))
|
|
|
print(row)
|
|
|
|
|
|
def update_data(a, p, m, r, c, d):
|
|
|
# 更新数据的SQL语句
|
|
|
update_sql = "UPDATE user SET pwd = %s, money= %s, record = %s, cardLock = %s, id= %s WHERE account = %s"
|
|
|
data = (p, m, r, c, d, a)
|
|
|
# 执行SQL语句
|
|
|
cursor.execute(update_sql, data)
|
|
|
# 提交事务
|
|
|
con.commit()
|
|
|
def test():
|
|
|
print('1.向表user中分别插入（账号1001、密码123）和（账号1002，密码456）')
|
|
|
insert_data("1001", "123", "123")
|
|
|
insert_data("1002", "456", "456")
|
|
|
print("2.查询表user中的数据")
|
|
|
select_data()
|
|
|
print("3.更新1001账号的密码为abc：")
|
|
|
update_data('1001', 'abc', '0', '', '0', "1001")
|
|
|
select_data()
|
|
|
|
|
|
if __name__ == '__main__':
|
|
|
pass
|
|
|
|
