Python3 working with MySQL database

This is the basic MySQL operation under Python3 . Other types of database usage are basically the same. Is that the name of the library is different. Because the python authorities have long ago set up a database of third-party libraries as an excuse to avoid the clutter of the API.

Installation and preparation

This is the Python3 library, so the installation under Windows will not be like python2 as a variety of exotic VC errors. is a more convenient fool to install.

• Under Windows platform:py -3 -m pip install PyMySQL
• Linux:python3 pip install PyMySQL

Of course, when introduced:import pymysql

Database Connection object Connection

Function	Description
Connection	Create a Connection object
Cursor ()	Use this link to create a + return cursor
Commit ()	Commit the current transaction
Rollback ()	Roll Back Current XV
Close ()	Close connection

Explain the parameters of connection

1. Host MySQL server address
2. Port Numeric Type ports
3. User username
4. passwd Password
5. DB database name
6. CharSet connection encoding, you need to explicitly indicate the encoding method

The previous code example:

conn = pymysql.Connect(host=‘127.0.0.1‘,port=3306,user=‘root‘,passwd=‘dyx240030‘,db=‘imooc‘,charset=‘utf8‘)cursor = conn.cursor()print(conn)print(cursor)cursor.close()conn.close()

OUT:<pymysql.connections.Connection object at 0x00000051C15BFDA0><pymysql.cursors.Cursor object at 0x00000051C15BFD68>

Database Cursor Object cursor

Function	Description
Execute (Op[,args])	Execute a database Query and command
Fetchone ()	Get the next row in the result set
Fetchmany (size)	Get result set size row
Fetchall ()	Get all rows left in the result set
RowCount	Last execute returns the number of rows of data or affects the number of rows
Close ()	Close cursor

Code implementation:

conn = pymysql.Connect(host=‘127.0.0.1‘,port=3306,user=‘root‘,passwd=‘dyx240030‘,db=‘imooc‘,charset=‘utf8‘)cursor = conn.cursor()sql = "select * from user"cursor.execute(sql)print("cursor.excute:",cursor.rowcount)rs = cursor.fetchone()print("rs:",rs)for each in cursor.fetchmany(2):    print(each)print()for each in cursor.fetchall():    print(each)

OUT:cursor.excute: 4rs: (‘1‘, ‘name1‘)(‘2‘, ‘name2‘)(‘3‘, ‘name3‘)(‘4‘, ‘name4‘)

Update Database Insert/update/delete

Unlike the select operation, these three operations modify the contents of the database, so a commit ()is required, or the database does not make the appropriate changes, but it does not error.

According to the general idea, the following routines are generally:

1. Turn off automatic commit:conn.autocommit (False)
2. Appears: Conn.rowback () rollback
3. Does not appear: Conn.commit ()

The following script implements the Insert/update/delete operation. In fact, this error-checking mode is not, here only to do simple raise, there are better methods behind.

conn = pymysql.Connect(host=‘127.0.0.1‘,port=3306,user=‘root‘,passwd=‘dyx240030‘,db=‘imooc‘,charset=‘utf8‘)conn.autocommit(False)cursor = conn.cursor()sqlInsert = "insert into user(userid,username) values(‘6‘,‘name6‘)"sqlUpdate = "update user set username=‘name41‘ where userd=‘4‘"sqlDelete = "delete from user where userid=‘1‘"try:    cursor.execute(sqlInsert)    print(cursor.rowcount)    cursor.execute(sqlUpdate)    print(cursor.rowcount)    cursor.execute(sqlDelete)    print(cursor.rowcount)        conn.commit()except Exception as e:    print("Reason:",e)    conn.rollback()    cursor.close()cursor.close()

[OUT]:1Reason: (1054, "Unknown column ‘userd‘ in ‘where clause‘")

Instance Bank transfer

You can take a look at the SQL operations of class thinking, where the advanced error pattern mentioned earlier uses a function that seems useless before rowcount , and checks for errors by looking at the effect of the operation on the database.

Import Osimport sysimport Pymysqlclass TransferMoney (object): Def __init__ (self,conn): Self.conn = conn def t        Ransfer (Self,sourceid,targetid,money): # Other functions are detected if they are wrong and throw an exception. Try:self.checkIdAvailable (SourceID) self.checkidavailable (Targetid) Self.ifenoughmoney (s Ourceid,money) Self.reducemoney (Sourceid,money) Self.addmoney (Targetid,money) self.conn.c         Ommit () except Exception as E:self.conn.rollback () Raise e def checkidavailable (self,id): cursor = Self.conn.cursor () try:sql = "SELECT * from account where id =%d"% ID #select语句判断可以用 Len (RS) cursor.execute (SQL) rs= Cursor.fetchall () If Len (rs)! = 1:# Database class thought error mode, check operation logarithm Entries that are affected by the library. Failed to reach target, throw exception raise Exception ("account%d does not exist"%id) Finally:cursor.close () def Ifenou  Ghmoney (Self,id,money): cursor = Self.conn.cursor ()      Try:sql = "SELECT * from account where id =%d and money>=%d"% (Id,money) Cursor.execute ( SQL) rs= Cursor.fetchall () If Len (rs)! = 1:raise Exception ("account%d does not exist%d Yuan"% (ID, Money)) Finally:cursor.close () def Reducemoney (Self,id,money): cursor = Self.conn.cursor (             ) Try:sql = "Update account set money = money-%d where id=%d"% (money,id) cursor.execute (SQL)        If cursor.rowcount! = 1:raise Exception ("Lost Money") finally:cursor.close () def Addmoney (Self,id,money): cursor = self.conn.cursor () try:sql = "Update account set Money"  = money+%d where id=%d "% (money,id) cursor.execute (SQL) if cursor.rowcount! = 1:raise        Exception ("failure bonus") Finally:cursor.close () if __name__== "__main__": If Len (SYS.ARGV) >=2: SourceID = Int (sys.argv[1]) Targetid = Int (sys.argv[2]) money = Int (sys.argv[3]) conn = Pymysql. Connect (host= ' 127.0.0.1 ', port=3306,user= ' root ', passwd= ' dyx240030 ', db= ' Imooc ', charset= ' utf8 ') Trmoney =            TransferMoney (conn) Try:trMoney.transfer (Sourceid,targetid,money) except Exception as E: Print ("Problem" +str (e)) Finally:conn.close ()

The pit I've stepped on ... (Here is Python3 oh) ' Nonetype ' object has no attribute ' encoding ', previously indicated CharSet must be "UTF8", not "utf-8"/"UTF-8" after the MySQL statement must have '; ', Otherwise it will not be an error, and it is difficult to discover that the database Insert/update/delete operation requires commit () when constructing the command, be careful to "wrap it up because the SQL statement string needs to be wrapped." So, "it's easier to avoid ambiguity."

Python3 working with MySQL database