Python对MySQL进行增删查改
python连接MySQL数据库:pymysql
# 测试操作 import pymysql # 打开数据库 db = pymysql.connect("localhost", "root", "test1234", "pythontest", charset=‘utf8‘ ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # 使用execute执行sql语句 cursor.execute("select * from user") data = cursor.fetchall() print(data) db.close()
数据库user表:
User类:
class User: def __init__(self, id, username, birth_data, money, father, mother): self.id = id self.username = username self.birth_data = birth_data self.money = money self.father = father self.mother = mother def set_username(self, name): self.username = name def get_username(self): return self.username def set_money(self, money): self.money = money def get_money(self): return self.money def print(self): if self.father == None: # print("id:", self.id, " father= NULL mother=NULL") print("id: {} username: {} father= NULL mother= NULL".format(self.id, self.username)) else: # print("id:", self.id, " father=", self.father.username, " mother=", self.mother.username) print("id: {} username: {}".format(self.id, self.username))
增删查改:
# 增删查改 from Practice_Recode.UserTest.User import User import pymysql def openDb(): global db, cursor db = pymysql.connect("localhost", "root", "test1234", "pythontest", charset=‘utf8‘) cursor = db.cursor() def closeDb(): db.close() # 按照用户id查询用户记录(输出相应内容,并返回查到的user对象) def serarchDb(id): openDb() sql = "select * from user where id = " + str(id) rst = cursor.execute(sql) if rst == 0: # print("查找失败") return None else: # print("查找成功") data = cursor.fetchone() # print(data) user1 = User(data[0], data[1], data[2], int(data[3]), data[4], data[5]) return user1 closeDb() # 按照用户id删除用户记录 def deleteDb(id): openDb() sql = "delete from user where id = " + str(id) rst = cursor.execute(sql) if rst == 0: print("删除失败") else: print("删除成功") closeDb() # 新增用户 def insertDb(user1): openDb() sql = "insert into user values(‘%d‘,‘%s‘,‘%s‘,‘%d‘,‘%s‘,‘%s‘)" % ( user1.id, user1.username, user1.birth_data, user1.money, user1.father, user1.mother) # "INSERT INTO mytb(title,keywd) VALUES(‘%s‘,‘%s‘)"%(x,y) cursor.execute(sql) db.commit() closeDb() # 更新用户信息 def updateDb(user1): openDb() sql = "update user set username = ‘%s‘, money=‘%d‘ where id=‘%d‘" % (user1.username, user1.money, user1.id) # update user set username=‘C‘, money=9999 where id=5; rst = cursor.execute(sql) if rst == 0: print("更新失败") else: print("更新成功") closeDb() # 测试数据 # testuser = serarchDb(5) # testuser.set_username(‘C‘) # testuser.set_money(9082) # # print(testuser.id, testuser.username, testuser.money, testuser.father, testuser.mother) # updateDb(testuser) # user1 = User(5, "c", "1111-03-11", 10000, father=‘A‘, mother=‘a‘) # insertDb(user1) # user2 = User(0, "d", "1111-03-11", 10000, ‘A‘, ‘a‘) # 自增键id设置为0,新增时即可实现自增 # insertDb(user2) # user2 = User(1, "A", "1111-03-11", 10000, father=None, mother=None) # user3 = User(2, "a", "1111-03-11", 10000, father=None, mother=None) # user1 = User(3, "B", "1111-03-11", 10000, user2, user3) # user1.dayin() # user1.father.dayin()
查找某个用户的祖辈:
# 查找当前user所有的亲缘关系 # father,monther,father‘s father,fahter‘s mother from Practice_Recode.UserTest.test02 import * import pymysql def openDb(): global db, cursor db = pymysql.connect("localhost", "root", "test1234", "pythontest", charset=‘utf8‘) cursor = db.cursor() def closeDb(): db.close() # 查找所有用户id,并返回ids,users def serarchDbAll(): openDb() ids = [] users = [] sql = "select * from user" rst = cursor.execute(sql) if rst == 0: # print("查找失败") return None else: # print("查找成功") data = cursor.fetchall() for i in range(len(data)): user = User(0, "", "", 0, "", "") user.id = data[i][0] user.username = data[i][1] user.birth_data = data[i][2] user.money = data[i][3] user.father = data[i][4] user.mother = data[i][5] users.append(user) ids.append(data[i][0]) closeDb() return ids, users # 根据名字返回这个人用户对象(未考虑重名问题) def NameSearchUser(name): ids, users = serarchDbAll() for user in users: if user.username == name: return user return None # 找某用户的爸爸用户 def searchFa(user): if user.father != None: fauser = NameSearchUser(user.father) # 根据爸爸的名字返回爸爸用户 if fauser != None: print("他的名字是:", fauser.username) return fauser print("他的名字为空") return None # 找某用户的妈妈用户 def searchMo(user): if user.mother != None: mouser = NameSearchUser(user.mother) # 根据名字返回妈妈用户 if mouser != None: print("她的名字是:", mouser.username) return mouser print("她的名字为空") return None # 查找13号的祖先 currentuser = serarchDb(13) # 得到13号用户本人 print("当前用户是:", currentuser.username) print("当前用户的父亲是:") cur_fa = searchFa(currentuser) # 得到当前用户的父亲 print(cur_fa) print("当前用户的母亲是:") cur_mo = searchMo(currentuser) # 得到当前用户的母亲 print(cur_mo) print("当前用户的爷爷:") cur_fa_fa = searchFa(cur_fa) # 得到当前用户的爷爷 print(cur_fa_fa) print("当前用户的奶奶:") cur_fa_mo = searchMo(cur_fa) # 得到当前用户的奶奶 print(cur_fa_mo) print("当前用户的姥爷:") cur_mo_fa = searchFa(cur_mo) # 得到当前用户的姥爷 print(cur_mo_fa) print("得到当前用户的姥姥:") cur_mo_mo = searchMo(cur_mo) # 得到当前用户的姥姥 print(cur_mo_mo)
相关推荐
YENCSDN 2020-11-17
lsjweiyi 2020-11-17
houmenghu 2020-11-17
Erick 2020-11-17
HeyShHeyou 2020-11-17
以梦为马不负韶华 2020-10-20
lhtzbj 2020-11-17
夜斗不是神 2020-11-17
pythonjw 2020-11-17
dingwun 2020-11-16
lhxxhl 2020-11-16
坚持是一种品质 2020-11-16
染血白衣 2020-11-16
huavhuahua 2020-11-20
meylovezn 2020-11-20
逍遥友 2020-11-20
weiiron 2020-11-16