Python connects to the database to add and delete checks and simulate banking operations

What is PyMySQL? PyMySQL is a library used to connect to MySQL servers in the Python3.x version, while mysqlDB is used in Python2. PyMySQL follows the Python database API V2.0 specification and includes the pure-Python MySQL client library.

Querying the database

import pymysql

# There is no semicolon to separate a statement compared to Java
conn=pymysql.connect(host = '127.0.0.1',
                                       port = 3306.# Note that this is not a string
                                       user = 'root',
                                        passwd = '092248',
                                        db = 'python',
                                       charset = 'utf8'
                                     )
Accept java-like entity classes via a cursor object
cursor = conn.cursor();

# Use SQL for query
sql = "select * from user"
cursor.execute(sql)

Fetchone () fetchOne () fetchone() fetchone() fetchone() fetchone() fetchone() fetchone() fetchone() fetchone
# the first
rs =  cursor.fetchone()
print rs
#第2~第4
rs =  cursor.fetchmany(3)
print rs
# Left all
rs =  cursor.fetchall()
2. Get an element of the table like for(object S: collection of objects) print like C
for row in rs :
         print （ "id=%s,name=%s"% row)Close resources every time
cursor.close();
conn.close();
Copy the code

Features of database operation transactions that contain transactions to add, delete, and modify

  • Atomicity: Multiple instructions as a group are either all or none executed
  • Consistency: For example, if A =100, b=100, the total number of transactions between A and B must be 200
  • Isolation: One transaction is not affected by other transactions
  • Persistence: Once a transaction is committed, its changes to the database are permanent
import pymysql

conn = pymysql.connect(
 host = '127.0.0.1',
port = 3306,
user = 'root',
passwd= '092248',
db = 'python',
charset = 'utf8',
)

cursor = conn.cursor()

sql_insert = "insert into user (id, name) values(10, 'wang')"
sql_update = "update user set name='wang' where id='1' "
sql_delete = "delete from user where id='2' "

try:
     cursor.execute(sql_insert)
     print(cursor.rowcount)
     cursor.execute(sql_update)
     print(cursor.rowcount)
     cursor.execute(sql_delete)
     print (cursor.rowcount)
None of the above statements can be executed without committing
     conn.commit()
Select * from '12' where '3' is executed
except Exception as e:
    print (e)
conn.rollback()

# close resources
cursor.close()
conn.close()
Copy the code

Simulated bank operation:

import sys
import pymysql
    Declare a class
class TransferMoney(object) :
# constructor
    def __init__(self, conn) :
        self.conn = conn
# Account availability
    def check_acct_available(self,acctid) :
        cursor=self.conn.cursor()
        try:
            sql = "select * from account where acctid=%s" % acctid
            cursor.execute(sql)
            print ("Check user availability" + sql)
            rs = cursor.fetchall()
            if len(rs) ! =1:
                raise Exception("Account %s does not exist" % acctid)
        finally:
                cursor.close()
    Is the balance sufficient
    def has_enough_money(self, acctid, money) :
        cursor = self.conn.cursor()
    
        try:
            Inject parameters into SQL statements in this way
            sql = "select * from account where acctid=%s and money>%s" % (acctid, money)
            cursor.execute(sql)
            print ("Check user availability" + sql)
            rs = cursor.fetchall()
            if len(rs)! =1:
                    raise Exception("Insufficient balance of %s" % acctid)
        finally:
            cursor.close()
    
    # lose money
    def reduce_money(self, acctid, money) :
        cursor = self.conn.cursor()
        try:
        Inject parameters into SQL statements in this way
            sql = "update account set money=money -%s where acctid=%s" % (money, acctid)
            cursor.execute(sql)
            print ("Lose money" + sql)
            rs = cursor.fetchall()
            ifcursor.rowcount ! =1:
                raise Exception("Failed to reduce %s" % acctid)
        finally:
                cursor.close()
        
        
    # to add money
    def add_money(self, acctid, money) :
        cursor = self.conn.cursor()
        try:
            Inject parameters into SQL statements in this way
            sql = "update account set money=money +%s where acctid=%s" % (money, acctid)
            cursor.execute(sql)
            print ("Add money" + sql)
            rs = cursor.fetchall()
            ifcursor.rowcount ! =1:
                raise Exception("%s failed to add money" % acctid)
        finally:
            cursor.close()
    
        
    # Specific operation method
    def transfer(self, source_acctid, target_acctid, money) :
        try:
            self.check_acct_available(source_acctid)  Check user availability
            self.check_acct_available(target_acctid)
            self.has_enough_money(source_acctid, money)  # A money enough
            self.reduce_money(source_acctid, money)  # A lose money
            self.add_money(target_acctid, money)  # B and money
            self.conn.commit()
    
    
        except Exception as e:
            self.conn.rollback()
            raise e  Rethrow the exception




if __name__=="__main__":
      Sys injects parameters into config at run time
    ''' source_acctid = sys.argv[1] target_acctid = sys.argv[2] money = sys.argv[3] '''
    conn = pymysql.connect(
     host='127.0.0.1',
    port=3306,
    user='root',
    passwd='092248',
    db='python',
    charset='utf8'
    )
    # TransferMoney is a class that implements operations by locking classes. Create a new class object here
    tr_money =TransferMoney(conn)
    
    try :
        tr_money.transfer(1.2.100)
    except Exception as e:
        print ("Problems arising:" + str(e))
    conn.close()
Copy the code