Python3 MySQL Simple operation

One, the DB interface provided by Python

Pymysql

Two basic objects: connection, cursor

Connection examples

# Connect_demo.pyimport pymysqldb = pymysql.connect (' localhost ', ' root ', ' root ', ' Imooc ', charset= ' UTF8 ') cursor = Db.cursor () print (DB) print (cursor) cursor.close () Db.close ()

Output

Object 0x7f1e2a852278 Object 0x7f1e2880e668>

Manipulating cursors

# Cursor_demo.pyimport pymysqldb = pymysql.connect (' localhost ', ' root ', ' root ', ' Imooc ', charset= ' UTF8 ') cursor = Db.cursor () sql = ' select * ' from user ' Cursor.execute (SQL) print (cursor.rowcount) result = Cursor.fetchall () print (result) For row in result:   print (row[0], ': ', row[1]) cursor.close () Db.close ()

Output

((1'Lisi'), (2'zhangsan'), (3 ' Liuqi '), (4'white'))1  : Lisi 2 : Zhangsan 3 : Liuqi 4 : White

Second, increase and revise the search

# Curd_demo.pyimport pymysqldb = pymysql.connect (' localhost ', ' root ', ' root ', ' Imooc ', charset= ' UTF8 ') cursor = Db.cursor () Sql_select = "SELECT * from user" Sql_insert = "INSERT INTO user (UserID, username) VALUES (DEFAULT, ' Wuliu ')" sql_update = "Update user set Username= ' Liuqi ' where userid=3" sql_delete = "Delete from user where userid>4" Cursor.execute (Sql_sele CT) result = Cursor.fetchall () print (Result) Try:    Cursor.execute (sql_insert)    result = Cursor.rowcount    Print (Result)    Cursor.execute (sql_update)    result = Cursor.rowcount    print (Result)    Cursor.execute ( Sql_delete)    result = Cursor.rowcount    print (Result)    db.commit () except Exception as E:    print (e)    Db.rollback () cursor.close () Db.close ()

Output

((1'Lisi'), (2'zhangsan'), (3 ' Liuqi '), (4'white'))101 

Iii. Small examples of transactions

# bank_demo.py# Coding:utf8import sysimport pymysqlclass TransferMoney (object): Def __init__ (self, conn): SELF.C ONN = conn def check_acct_available (self, acctid): cursor = self.conn.cursor () Try:sql = "sel ECT * from account where acctid=%s "% acctid cursor.execute (SQL) print (" check_acct_available: "+ SQL ) rs = Cursor.fetchall () If Len (rs)! = 1:raise Exception ("account does not exist"% Acctid) fi Nally:cursor.close () def has_enough_money (self, Acctid, money): cursor = self.conn.cursor () t Ry:sql = "SELECT * from account where acctid=%s and money>%s"% (Acctid, Money) Cursor.execute (s                QL) Print ("Chas_enough_mone:" + sql) rs = Cursor.fetchall () If Len (rs)! = 1: Raise Exception ("account%s insufficient balance"% Acctid) finally:cursor.close () def reduce_money (self, Acctid, money) : cursor = SELF.COnn.cursor () Try:sql = "Update account set Money=money –%s where acctid=%s"% (money, Acctid) Cursor.execute (SQL) print ("Reduce_money:" + sql) if Cursor.rowcount! = 1:raise Ex Ception ("account%s reduced failure"% Acctid) finally:cursor.close () def add_money (self, Acctid, money): Curso R = self.conn.cursor () try:sql = "Update account set Money=money +%s where acctid=%s"% (money, Acctid ) cursor.execute (SQL) print ("Rad_money:" + sql) if Cursor.rowcount! = 1:r Aise Exception ("account%s bonus failed"% Acctid) finally:cursor.close () def transfer (self, source_acctid, target_ Acctid, Money): try:self.check_acct_available (Source_acctid) self.check_acct_available (targe            T_acctid) Self.has_enough_money (Source_acctid, Money) Self.reduce_money (Source_acctid, Money) Self.add_money (TargEt_acctid, Money) self.conn.commit () except Exception as E:self.conn.rollback () r Aise EIF __name__ = = "__main__": source_acctid= sys.argv[1] target_acctid= sys.argv[2] money = sys.argv[3] Con        n = pymysql.connect (' localhost ', ' root ', ' root ', ' Imooc ', charset= ' utf8 ') Tr_money = TransferMoney (conn) Try: Tr_money.transfer (Source_acctid, Target_acctid, money) except Exception as E:print ("Problematic:", str (e)) finally : Conn.close ()

Command line Run

Python bank_demo.py 3 1 1000

Output

Check_acct_available:Select * from account where acctid=3check_acct_available:Select * from account where acctid=1chas_enough_mone:Select * from account where acctid=3< /c7> and money>reduce_money:update account set money, where acctid=3  rad_money:update Account set money, where acctid=1

Python3 MySQL Simple operation