您好,登錄后才能下訂單哦!
這篇文章主要介紹“Python MySQL數(shù)據(jù)庫(kù)基本操作及項(xiàng)目示例分析”,在日常操作中,相信很多人在Python MySQL數(shù)據(jù)庫(kù)基本操作及項(xiàng)目示例分析問題上存在疑惑,小編查閱了各式資料,整理出簡(jiǎn)單好用的操作方法,希望對(duì)大家解答”Python MySQL數(shù)據(jù)庫(kù)基本操作及項(xiàng)目示例分析”的疑惑有所幫助!接下來,請(qǐng)跟著小編一起來學(xué)習(xí)吧!
要先配置環(huán)境變量,然后cmd安裝:pip install pymysql
1、連接MySQL,并創(chuàng)建wzg庫(kù)
#引入decimal模塊 import pymysql #連接數(shù)據(jù)庫(kù) db=pymysql.connect(host='localhost',user='root',password='1234',charset='utf8') #創(chuàng)建一個(gè)游標(biāo)對(duì)象(相當(dāng)于指針) cursor=db.cursor() #執(zhí)行創(chuàng)建數(shù)據(jù)庫(kù)語句 cursor.execute('create schema wzg default charset=utf8;') cursor.execute('show databases;') #fetchone獲取一條數(shù)據(jù)(元組類型) print(cursor.fetchone()) #現(xiàn)在指針到了[1]的位置 #fetchall獲取全部數(shù)據(jù)(字符串類型) all=cursor.fetchall() for i in all: print(i[0]) #關(guān)閉游標(biāo)和數(shù)據(jù)庫(kù)連接 cursor.close() db.close()
2、創(chuàng)建student表,并插入數(shù)據(jù)
import pymysql #連接數(shù)據(jù)庫(kù),并打開wzg數(shù)據(jù)庫(kù)(數(shù)據(jù)庫(kù)已創(chuàng)建) db=pymysql.connect(host='localhost',user='root',password='1234',charset='utf8',db='wzg') #創(chuàng)建游標(biāo)對(duì)象 cursor=db.cursor() try: #創(chuàng)建student表,并執(zhí)行 sql='''create table student( SNO char(10), SNAME varchar(20) NOT NULL, SSEX varchar(1), primary key(SNO) )default charset=utf8;''' cursor.execute(sql) #插入一條數(shù)據(jù),并執(zhí)行 insert_sql=''' insert into student values('200303016','王智剛','男'),('20030001','小明','男') ''' cursor.execute(insert_sql) #將數(shù)據(jù)提交給數(shù)據(jù)庫(kù)(加入數(shù)據(jù),修改數(shù)據(jù)要先提交) db.commit() #執(zhí)行查詢語句 cursor.execute('select * from student') #打印全部數(shù)據(jù) all=cursor.fetchall() for i in all: print(i) #發(fā)生錯(cuò)誤時(shí),打印報(bào)錯(cuò)原因 except Exception as e: print(e) #無論是否報(bào)錯(cuò)都執(zhí)行 finally: cursor.close() db.close()
數(shù)據(jù)庫(kù)中char和varchar的區(qū)別:
char類型的長(zhǎng)度是固定的,varchar的長(zhǎng)度是可變的。
例如:存儲(chǔ)字符串'abc',使用char(10),表示存儲(chǔ)的字符將占10個(gè)字節(jié)(包括7個(gè)空字符),
使用varchar(10),表示只占3個(gè)字節(jié),10是最大值,當(dāng)存儲(chǔ)的字符小于10時(shí),按照實(shí)際的長(zhǎng)度存儲(chǔ)。
完成功能:1.查詢 2.取錢 3.存錢 4.退出
練習(xí):創(chuàng)建信息表,并進(jìn)行匹配
1、創(chuàng)建數(shù)據(jù)庫(kù)為(bank),賬戶信息表為(account)
account_id(varchar(20)) | Account_passwd(char(6)) | Money(decimal(10,2)) |
---|---|---|
001 | 123456 | 1000.00 |
002 | 456789 | 5000.00 |
2、拓展:進(jìn)行賬號(hào)和密碼的匹配
請(qǐng)輸入賬號(hào):001
請(qǐng)輸入密碼:123456
select * from account where account_id=001 and Account_passwd=123456 if cursor.fetchall(): 登錄成功 else: 登錄失敗
import pymysql # 連接數(shù)據(jù)庫(kù) db = pymysql.connect(host='localhost', user='root', password='1234', charset='utf8') cursor = db.cursor() # 創(chuàng)建bank庫(kù) cursor.execute('create database bank charset utf8;') cursor.execute('use bank;') try: # # 創(chuàng)建表 # sql = '''create table account( # account_id varchar(20) NOT NULL, # account_passwd char(6) NOT NULL, # money decimal(10,2), # primary key(account_id) # );''' # cursor.execute(sql) # # 插入數(shù)據(jù) # insert_sql = ''' # insert into account values('001','123456',1000.00),('002','456789',5000.00) # ''' # cursor.execute(insert_sql) # db.commit() # # 查詢所有數(shù)據(jù) # cursor.execute('select * from account') # all = cursor.fetchall() # for i in all: # print(i) # 輸入賬號(hào)和密碼 z=input("請(qǐng)輸入賬號(hào):") m=input("請(qǐng)輸入密碼:") # 從account表中進(jìn)行賬號(hào)和密碼的匹配 cursor.execute('select * from account where account_id=%s and account_passwd=%s',(z,m)) # 如果找到,則登錄成功 if cursor.fetchall(): print('登錄成功') else: print('登錄失敗') except Exception as e: print(e) finally: cursor.close() db.close()
import pymysql # 創(chuàng)建bank庫(kù) CREATE_SCHEMA_SQL=''' create schema bank charset utf8; ''' # 創(chuàng)建account表 CREATE_TABLE_SQL = ''' create table account( account_id varchar(20) NOT NULL, account_passwd char(6) NOT NULL, # decimal用于保存精確數(shù)字的類型,decimal(10,2)表示總位數(shù)最大為12位,其中整數(shù)10位,小數(shù)2位 money decimal(10,2), primary key(account_id) ) default charset=utf8; ''' # 創(chuàng)建銀行賬戶 CREATE_ACCOUNT_SQL = ''' insert into account values('001','123456',1000.00),('002','456789',5000.00); ''' # 初始化 def init(): try: DB = pymysql.connect(host='localhost',user='root',password='1234',charset='utf8') cursor1 = DB.cursor() cursor1.execute(CREATE_SCHEMA_SQL) DB = pymysql.connect(host='localhost',user='root',password='1234',charset='utf8',database='bank') cursor2 = DB.cursor() cursor2.execute(CREATE_TABLE_SQL) cursor2.execute(CREATE_ACCOUNT_SQL) DB.commit() print('初始化成功') except Exception as e: print('初始化失敗',e) finally: cursor1.close() cursor2.close() DB.close() # 不讓別人調(diào)用 if __name__ == "__main__": init()
import pymysql # 定義全局變量為空 DB=None # 創(chuàng)建Account類 class Account(): # 傳入?yún)?shù) def __init__(self,account_id,account_passwd): self.account_id=account_id self.account_passwd=account_passwd # 登錄檢查 def check_account(self): cursor=DB.cursor() try: # 把輸入賬號(hào)和密碼進(jìn)行匹配(函數(shù)體內(nèi)部傳入?yún)?shù)用self.) SQL="select * from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd) cursor.execute(SQL) # 匹配成功返回True,失敗返回False if cursor.fetchall(): return True else: return False except Exception as e: print("錯(cuò)誤原因:",e) finally: cursor.close() # 查詢余額 # def query_money # 取錢 # def reduce_money # 存錢 # def add_money def main(): # 定義全局變量 global DB # 連接bank庫(kù) DB=pymysql.connect(host="localhost",user="root",passwd="1234",database="bank") cursor=DB.cursor() # 輸入賬號(hào)和密碼 from_account_id=input("請(qǐng)輸入賬號(hào):") from_account_passwd=input("請(qǐng)輸入密碼:") # 輸入的參數(shù)傳入給Account類,并創(chuàng)建account對(duì)象 account=Account(from_account_id,from_account_passwd) # 調(diào)用check_account方法,進(jìn)行登錄檢查 if account.check_account(): choose=input("請(qǐng)輸入操作:\n1、查詢余額\n2、取錢\n3、存錢\n4、取卡\n") # 當(dāng)輸入不等于4的時(shí)候執(zhí)行,等于4則退出 while choose!="4": # 查詢 if choose=="1": print("111") # 取錢 elif choose=="2": print("222") # 存錢 elif choose=="3": print("333") # 上面操作完成之后,繼續(xù)輸入其他操作 choose = input("請(qǐng)輸入操作:\n1、查詢余額\n2、取錢\n3、存錢\n4、取卡\n") else: print("謝謝使用!") else: print("賬號(hào)或密碼錯(cuò)誤") DB.close() main()
存在銀行里的錢可能會(huì)產(chǎn)生利息,所以需要考慮余額為小數(shù)的問題,需要用到decimal庫(kù)
import pymysql # 引入decimal模塊 import decimal DB=None class Account(): def __init__(self,account_id,account_passwd): self.account_id=account_id self.account_passwd=account_passwd # 登錄檢查 def check_account(self): cursor=DB.cursor() try: SQL="select * from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd) cursor.execute(SQL) if cursor.fetchall(): return True else: return False except Exception as e: print("錯(cuò)誤",e) finally: cursor.close() # 查詢余額 def query_money(self): cursor=DB.cursor() try: # 匹配賬號(hào)密碼,并返回money SQL="select money from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd) cursor.execute(SQL) money=cursor.fetchone()[0] # 如果賬戶有錢就返回金額,沒錢返回0.00 if money: # 返回值為decimal類型,quantize函數(shù)進(jìn)行四舍五入,'0.00'表示保留兩位小數(shù) return str(money.quantize(decimal.Decimal('0.00'))) else: return 0.00 except Exception as e: print("錯(cuò)誤原因",e) finally: cursor.close() def main(): global DB DB=pymysql.connect(host="localhost",user="root",passwd="1234",charset="utf8",database="bank") cursor=DB.cursor() from_account_id=input("請(qǐng)輸入賬號(hào):") from_account_passwd=input("請(qǐng)輸入密碼:") account=Account(from_account_id,from_account_passwd) if account.check_account(): choose=input("請(qǐng)輸入操作:\n1、查詢余額\n2、取錢\n3、存錢\n4、取卡\n") while choose!="4": # 查詢 if choose=="1": # 調(diào)用query_money方法 print("您的余額是%s元" % account.query_money()) # 取錢 elif choose=="2": print("222") # 存錢 elif choose=="3": print("333") choose = input("請(qǐng)輸入操作:\n1、查詢余額\n2、取錢\n3、存錢\n4、取卡\n") else: print("謝謝使用") else: print("賬號(hào)或密碼錯(cuò)誤") DB.close() main()
取錢存錢要用update來執(zhí)行數(shù)據(jù)庫(kù),還要注意取錢需要考慮余額是否充足的問題
import pymysql import decimal DB=None class Account(): def __init__(self,account_id,account_passwd): self.account_id=account_id self.account_passwd=account_passwd # 登錄檢查 def check_account(self): cursor=DB.cursor() try: SQL="select * from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd) cursor.execute(SQL) if cursor.fetchall(): return True else: return False except Exception as e: print("錯(cuò)誤",e) finally: cursor.close() # 查詢余額 def query_money(self): cursor=DB.cursor() try: SQL="select money from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd) cursor.execute(SQL) money=cursor.fetchone()[0] if money: return str(money.quantize(decimal.Decimal('0.00'))) else: return 0.00 except Exception as e: print("錯(cuò)誤原因",e) finally: cursor.close() # 取錢(注意傳入money參數(shù)) def reduce_money(self,money): cursor = DB.cursor() try: # 先調(diào)用query_money方法,查詢余額 has_money=self.query_money() # 所取金額小于余額則執(zhí)行(注意類型轉(zhuǎn)換) if decimal.Decimal(money) <= decimal.Decimal(has_money): # 進(jìn)行數(shù)據(jù)更新操作 SQL="update account set money=money-%s where account_id=%s and account_passwd=%s" %(money,self.account_id,self.account_passwd) cursor.execute(SQL) # rowcount進(jìn)行行計(jì)數(shù),行數(shù)為1則將數(shù)據(jù)提交給數(shù)據(jù)庫(kù) if cursor.rowcount==1: DB.commit() return True else: # rollback數(shù)據(jù)庫(kù)回滾,行數(shù)不為1則不執(zhí)行 DB.rollback() return False else: print("余額不足") except Exception as e: print("錯(cuò)誤原因",e) finally: cursor.close() # 存錢 # def add_money def main(): global DB DB=pymysql.connect(host="localhost",user="root",passwd="1234",charset="utf8",database="bank") cursor=DB.cursor() from_account_id=input("請(qǐng)輸入賬號(hào):") from_account_passwd=input("請(qǐng)輸入密碼:") account=Account(from_account_id,from_account_passwd) if account.check_account(): choose=input("請(qǐng)輸入操作:\n1、查詢余額\n2、取錢\n3、存錢\n4、取卡\n") while choose!="4": # 查詢 if choose=="1": print("您的余額是%s元" % account.query_money()) # 取錢 elif choose=="2": # 先查詢余額,再輸入取款金額,防止取款金額大于余額 money=input("您的余額是%s元,請(qǐng)輸入取款金額" % account.query_money()) # 調(diào)用reduce_money方法,money不為空則取款成功 if account.reduce_money(money): print("取款成功,您的余額還有%s元" % account.query_money()) else: print("取款失??!") # 存錢 elif choose=="3": print("333") choose = input("請(qǐng)輸入操作:\n1、查詢余額\n2、取錢\n3、存錢\n4、取卡\n") else: print("謝謝使用!") else: print("賬號(hào)或密碼錯(cuò)誤") DB.close() main()
存錢功能和取錢功能相似,而且不需要考慮余額的問題,至此已完善當(dāng)前所有功能
import pymysql import decimal DB=None class Account(): def __init__(self,account_id,account_passwd): self.account_id=account_id self.account_passwd=account_passwd # 登錄檢查 def check_account(self): cursor=DB.cursor() try: SQL="select * from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd) cursor.execute(SQL) if cursor.fetchall(): return True else: return False except Exception as e: print("錯(cuò)誤",e) finally: cursor.close() # 查詢余額 def query_money(self): cursor=DB.cursor() try: SQL="select money from account where account_id=%s and account_passwd=%s" %(self.account_id,self.account_passwd) cursor.execute(SQL) money=cursor.fetchone()[0] if money: return str(money.quantize(decimal.Decimal('0.00'))) else: return 0.00 except Exception as e: print("錯(cuò)誤原因",e) finally: cursor.close() # 取錢 def reduce_money(self,money): cursor = DB.cursor() try: has_money=self.query_money() if decimal.Decimal(money) <= decimal.Decimal(has_money): SQL="update account set money=money-%s where account_id=%s and account_passwd=%s" %(money,self.account_id,self.account_passwd) cursor.execute(SQL) if cursor.rowcount==1: DB.commit() return True else: DB.rollback() return False else: print("余額不足") except Exception as e: print("錯(cuò)誤原因",e) finally: cursor.close() # 存錢 def add_money(self,money): cursor = DB.cursor() try: SQL="update account set money=money+%s where account_id=%s and account_passwd=%s" %(money,self.account_id,self.account_passwd) cursor.execute(SQL) if cursor.rowcount==1: DB.commit() return True else: DB.rollback() return False except Exception as e: DB.rollback() print("錯(cuò)誤原因",e) finally: cursor.close() def main(): global DB DB=pymysql.connect(host="localhost",user="root",passwd="1234",charset="utf8",database="bank") cursor=DB.cursor() from_account_id=input("請(qǐng)輸入賬號(hào):") from_account_passwd=input("請(qǐng)輸入密碼:") account=Account(from_account_id,from_account_passwd) if account.check_account(): choose=input("請(qǐng)輸入操作:\n1、查詢余額\n2、取錢\n3、存錢\n4、取卡\n") while choose!="4": # 查詢 if choose=="1": print("您的余額是%s元" % account.query_money()) # 取錢 elif choose=="2": money=input("您的余額是%s元,請(qǐng)輸入取款金額" % account.query_money()) if account.reduce_money(money): print("取款成功,您的余額還有%s元" % account.query_money()) else: print("取款失??!") # 存錢 elif choose=="3": money=input("請(qǐng)輸入存款金額:") if account.add_money(money): print("存款成功,您的余額還有%s元,按任意鍵繼續(xù)\n" % (account.query_money())) else: print("存款失敗,按任意鍵繼續(xù)") choose = input("請(qǐng)輸入操作:\n1、查詢余額\n2、取錢\n3、存錢\n4、取卡\n") else: print("謝謝使用!") else: print("賬號(hào)或密碼錯(cuò)誤") DB.close() main()
到此,關(guān)于“Python MySQL數(shù)據(jù)庫(kù)基本操作及項(xiàng)目示例分析”的學(xué)習(xí)就結(jié)束了,希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí),快去試試吧!若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí),請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站,小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。