Shulou(Shulou.com)06/02 Report--

This article mainly introduces the "basic operation of Python MySQL database and project example analysis". In the daily operation, I believe many people have doubts about the basic operation of Python MySQL database and project sample analysis. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "basic operation of Python MySQL database and project sample analysis". Next, please follow the editor to study!

I. basic usage of database

To configure environment variables first, and then cmd installation: pip install pymysql

1. Connect to MySQL and create a wzg library

# introduce decimal module import pymysql# to connect to database db=pymysql.connect (host='localhost',user='root',password='1234',charset='utf8') # create a cursor object (equivalent to pointer) cursor=db.cursor () # execute database creation statement cursor.execute ('create schema wzg default charset=utf8;') cursor.execute (' show databases) ') # fetchone gets a piece of data (tuple type) print (cursor.fetchone ()) # now pointer to the position of [1] # fetchall gets all data (string type) all=cursor.fetchall () for i in all: print (I [0]) # close cursor and database connection cursor.close () db.close ()

2. Create the student table and insert the data

Import pymysql# connects to the database and opens the wzg database (database created) db=pymysql.connect (host='localhost',user='root',password='1234',charset='utf8',db='wzg') # create cursor object cursor=db.cursor () try: # create student table And execute sql='''create table student (SNO char (10), SNAME varchar (20) NOT NULL, SSEX varchar (1), primary key (SNO)) default charset=utf8 '' cursor.execute (sql) # inserts a piece of data and executes insert_sql=''' insert into student values ('200303016,' Wang Zhigang', 'male'), ('20030001'' Xiaoming', 'male')''cursor.execute (insert_sql) # to submit the data to the database (add data) Db.commit () # execute query statement cursor.execute ('select * from student') # print all data all=cursor.fetchall () for i inall: print (I) # print error reason except Exception as e: print (e) # execute finally: cursor.close () db.close () regardless of whether you report an error or not

The difference between char and varchar in the database:

The length of the char type is fixed and the length of the varchar is variable.

For example, the storage string 'abc', uses char (10), which means that the stored characters will account for 10 bytes (including 7 empty characters)

Using varchar (10), which means only 3 bytes, 10 is the maximum, and when the stored character is less than 10:00, it is stored according to the actual length.

II. Project: bank management system

Complete function: 1. Query 2. Withdraw money 3. Save money 4. Quit

Exercise: create an information table and match it

1. Create the database (bank) and the account information table (account).

Account_id (varchar (20)) Account_passwd (char (6)) Money (decimal (10Magazine 2)) 0011234561000.000024567895000.00

2. Expand: match account and password

Please enter account number: 001

Please enter your password: 123456

Select * from account where account_id=001 and Account_passwd=123456if cursor.fetchall (): login succeeded else: login failed import pymysql# connection database db = pymysql.connect (host='localhost', user='root', password='1234', charset='utf8') cursor = db.cursor () # create bank library cursor.execute ('create database bank charset utf8;') cursor.execute (' use bank ') try: # # create table # sql =' 'create table account (# account_id varchar (20) NOT NULL, # account_passwd char (6) NOT NULL, # money decimal (10 account_id 2), # primary key (account_id) #) '' # cursor.execute (sql) # # insert data # insert_sql =''# insert into account values (1000.00 for 123456), (456789 for 002) 5000.00) #''# cursor.execute (insert_sql) # db.commit () # # query all data # cursor.execute ('select * from account') # all = cursor.fetchall () # for i in all: # print (I) # enter account and password z=input ("Please enter account:") m=input ("Please enter password:") # match account and password cursor.execute ('select * from account where account_id=%s and account_passwd=%s') from the account table (zPerm)) # if you find Login successful if cursor.fetchall (): print ('login successful') else: print ('login failed') except Exception as e: print (e) finally: cursor.close () db.close () 1, initialize import pymysql# to create bank library CREATE_SCHEMA_SQL=''' create schema bank charset utf8 Create account table CREATE_TABLE_SQL =''create table account (account_id varchar (20) NOT NULL, account_passwd char (6) NOT NULL, # decimal) the type used to hold exact numbers, decimal (10 account_id 2) indicates that the total number of digits is up to 12 digits, including integer 10 places, decimal 2 places money decimal (10 account_id 2), primary key (account_id)) default charset=utf8 '' # create a bank account CREATE_ACCOUNT_SQL = 'insert into account values (001) (123456) (1000.00), (256789) (5000.00) Initialize 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 ('initialization successful') except Exception ase: print ('initialization failed' E) finally: cursor1.close () cursor2.close () DB.close () # do not let others call if _ _ name__ = = "_ _ main__": init () 2, login check And select the operation import pymysql# to define the global variable as empty DB=None# to create the Account class class Account (): # pass in the parameter def _ _ init__ (self,account_id) Account_passwd): self.account_id=account_id self.account_passwd=account_passwd # login check def check_account (self): cursor=DB.cursor () try: # match the input account and password (self.) SQL= "select * from account where account_id=%s and account_passwd=%s"% (self.account_id Self.account_passwd) cursor.execute (SQL) # returns True after a successful match Failure returns False if cursor.fetchall (): return True else: return False except Exception as e: print ("error reason:" E) finally: cursor.close () # query balance # def query_money # withdraw # def reduce_money # Save # def add_money def main (): # define global variable global DB # Connect bank library DB=pymysql.connect (host= "localhost", user= "root", passwd= "1234" Database= "bank") cursor=DB.cursor () # enter account number and password from_account_id=input ("Please enter account number:") from_account_passwd=input ("Please enter password:") # input parameters to Account class And create account object account=Account (from_account_id,from_account_passwd) # call check_account method to check login if account.check_account (): choose=input ("Please enter operation:\ N1, query balance\ N2, withdraw money\ n3, deposit\ n4, pick up card\ n") # execute when input is not equal to 4 If it equals 4, exit while chooseholders = "4": # query if choose== "1": print ("111") # withdraw money elif choose==" 2 ": print (" 222") # Save elif choose== "3": Print ("333") # after the above operation is completed Continue to enter other operations choose = input ("Please enter action:\ N1, query balance\ N2, withdraw money\ n3, deposit\ n4, withdraw card\ n") else: print ("Thank you for using!") Else: print ("wrong account or password") DB.close () main () 3. Add query function.

The money in the bank may generate interest, so you need to consider the problem that the balance is a decimal and use the decimal library.

Import pymysql# introduces decimal module import decimalDB=Noneclass Account (): def _ _ init__ (self,account_id,account_passwd): self.account_id=account_id self.account_passwd=account_passwd # login check 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 ("error" E) finally: cursor.close () # query balance def query_money (self): cursor=DB.cursor () try: # match account password And return 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] # if the account has money, return the amount; if there is no money, return 0.00 if money: # the return value is decimal, and the quantize function is rounded '0.00' means to retain two decimal places return str (money.quantize (decimal.Decimal ('0.00')) else: return 0.00 except Exception as e: print ("cause of error", 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 ("Please enter account number:") from_account_passwd=input ("Please enter password:") account=Account (from_account_id From_account_passwd) if account.check_account (): choose=input ("Please enter action:\ N1, query balance\ N2, withdraw money\ n3, deposit\ n4, Pick up the card\ n ") while chooseholders =" 4 ": # query if choose==" 1 ": # call the query_money method print (" your balance is% s yuan "% account.query_money ()) # withdraw elif choose==" 2 ": print (" Elif choose== "3": print ("333") choose= input ("Please enter actions:\ N1, Query balance\ N2, withdraw money\ n3, deposit money\ n4, withdraw card\ n ") else: print (" Thank you for using ") else: print (" wrong account or password ") DB.close () main () 4. Add the withdrawal function.

To withdraw money and save money, we should use update to execute the database, and we should also pay attention to the question of whether the balance is sufficient.

Import pymysqlimport decimalDB=Noneclass Account (): def _ _ init__ (self,account_id,account_passwd): self.account_id=account_id self.account_passwd=account_passwd # login check 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 ("error" E) finally: cursor.close () # query balance 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 ("cause of error" E) finally: cursor.close () # withdraw money (note the money parameter) def reduce_money (self,money): cursor = DB.cursor () try: # call the query_money method first Query balance has_money=self.query_money () # if the amount withdrawn is less than the balance, execute (note type conversion) if decimal.Decimal (money)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.