python operation link database

• pymysql module: pip install pymysql

  • Function: You can use the python program to link to the mysql database, and you can directly execute the sql statement in python

• import pymysql
  #1. Create a link object
  conn = pymysql.Connect(
      host='127.0.0.1',#Database server host address
      port=3306, #mysql port number
      user='root', #database username
      password='boboadmin', #database password
      db='AnHui',#The name of the data warehouse
      charset='utf8')
  #Create a cursor object
  cusor = conn.cursor()
  #2. Add record operation
  # sql = 'insert into emp(name,sex,age,dep_id)values("%s","%s",%d,%d)'%('haha','female',20,200)
  # cusor.execute(sql)
  # conn.commit() #After rectifying the data, remember to submit the transaction
  
  #3. Delete records
  # sql = 'delete from emp where name = "%s"'%'haha'
  # print(sql)
  # cusor.execute(sql)
  # conn.commit()
  
  #4. Modify operation
  # new_age = input('enter a new age:')
  # new_age = int(new_age)
  # sql = 'update emp set age = %d where id = 3'%new_age
  # print(sql)
  # cusor.execute(sql)
  # conn.commit()
  
  #query operation
  sql = 'select * from emp where age > 30'
  cusor.execute(sql) #Responsible for executing sql statements
  #What fetchall returns is a tuple, and the tuple element is another element, and the tuple stores a queried record
  # all_data = cusor.fetchall() #Get all the data queried, and return an empty tuple if no data is queried
  # print(all_data)
  
  #fetchone will only return the first piece of data queried
  one_data = cusor.fetchone() #If no data is found, return None
  print(one_data)
  
  #closes an open resource object
  cusor.close()
  conn.close()
  
  

log in Register

import pymysql
conn = pymysql.Connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='boboadmin',
    db='AnHui',
    charset='utf8'
)
cusor = conn.cursor()

def regist():
    username = input('enter username:')
    password = input('enter password:')
    email = input('enter email:')
    phone = input('enter phone:')
    #Determine whether the username is duplicate
    s = 'select * from userData where username="%s"'%username
    cusor.execute(s)
    r = cusor.fetchone()
    #If the result is found, it means that the username exists
    if r == None:
        sql = 'insert into userData(username,passwowrd,email,phone) values("%s","%s","%s","%s")'%(username,password,email,phone)
        cusor.execute(sql)
        conn.commit()
        print('registration success!')
    else:
        print('Registration failed, username already exists!')
def login():
    username = input('enter username:')
    password = input('enter password:')
    sql1 = 'select * from userData where username="%s"'%username
    cusor.execute(sql1)
    r1 = cusor.fetchone()
    if r1 == None: #If fetchone does not query the data, it will return None
        print('Login failed, username does not exist!')
    else:#username exists
        pwd = r1[2]
        if pwd == password:
            print('login successful!')
        else:
            print('Login failed password!')

Transaction processing (general understanding)

• What are things?

  • Mysql things mainly refer to a set of operations that process data with a large amount of operations and high complexity.
  • Transaction is actually a way of processing data in MySQL, which is mainly used in the case of high data integrity and high dependence between data.
  • for example
  • , when Xiao Zhang clicked the button to confirm the transfer, the system suddenly crashed. There will be several incorrect situations as follows:
    • 1. Xiao Zhang's money was sent to Xiao Li's account, but the money in his own account was not deducted.
    • 2. Xiao Zhang's money has not been sent to Xiao Li's account, but the money in his own account has been deducted.
    • Such a business scenario requires MySQL transaction retention, even if the machine fails, the data is still correct.

• import pymysql
  #1. Create a link object
  conn = pymysql.Connect(
      host='127.0.0.1',#Database server host address
      port=3306, #mysql port number
      user='root', #database username
      password='boboadmin', #database password
      db='AnHui',#The name of the data warehouse
      charset='utf8')
  #Create a cursor object
  cusor = conn.cursor()
  
  #Xiao Zhang transferred 200 yuan to Xiao Li's bank card
  sql1 = 'update bankTab set monry -= 200 where name = "Xiao Zhang"'
  sql2 = 'update bankTab set monry += 200 where123 name = "Xiao Li"'
  
  try:
      cusor.execute(sql1) #successfully executed
      cusor.execute(sql2) #Abnormal
      conn.commit() #It means that the transfer is successful, the data will be written to the database, and there is no way to withdraw
  except Exception as e:
      print(e)
      conn.rollback() #Rollback of things: withdraw the successful execution of sql in try
      
  cusor.close()
  conn.close()
  

• characteristics of things

  • atomicity
    • A transaction must be regarded as an indivisible minimum unit of work. All operations in each transaction must either succeed or fail. It is never possible for some operations to fail and some operations to succeed. This is the so-called concept of atomicity.
  • consistency
    • Consistency is like the example above. When an exception occurs, the data is still correct. That is to say, when a transaction fails, the data will not be affected by the exception and will always maintain its correctness.
  • isolation
    • When a transaction has not been committed, each transaction is isolated from each other, and complementarity is affected.
  • Persistence
    • When a transaction is committed, the changes are permanently stored in the database.

• Commit and rollback of things

  • commit
  • rollback