Python 3.5 Connect to MySQL database (pymysql mode)

Since the MySQLdb module does not yet support python3.x, the official MySQL connection pack only supports up to 3.4, so Python3.5 need to install the Pymysql module if you want to connect to MySQL.

The Pymysql module can be installed via PIP.

Because Python unifies the interface of the database connection, Pymysql and MySQLdb are similar in the way they are used:

Pymysql. Connect () parameter description Host (str):      MySQL server address port (int):      MySQL server port number user (str):      User name passwd (str):    password db (str):        Database name CharSet (str):   The method supported by the connection encoding connection object cursor ()        uses the connection to create and return a cursor commit () to        commit the current transaction rollback ()      Rolls back the current transaction close ()         closes the connection cursor object supported methods Execute (OP)     executes a database query command Fetchone ()      gets the next line of the result set Fetchmany (size) Gets the next few rows of the result set Fetchall ()      gets all the rows in the result sets rowcount ()      returns the number of data bars or affects the number of rows close ()         closes the cursor object

==================MySQL===================

First, before connecting to the database, create a trading table that is easy to test the Pymysql function:

DROP TABLE IF EXISTS ' trade '; CREATE TABLE ' Trade ' (  ' id ' int (4) unsigned not NULL auto_increment,  ' name ' varchar (6) NOT null COMMENT ' user real name ', c7/> ' account ' varchar (one) NOT null COMMENT ' bank savings accounts ',  ' Saving ' decimal (8,2) unsigned not null DEFAULT ' 0.00 ' COMMENT ' accounts Savings amount ',  ' expend ' decimal (8,2) unsigned not NULL DEFAULT ' 0.00 ' COMMENT ' account spending total ',  ' income ' decimal (8,2) unsigned Not NULL DEFAULT ' 0.00 ' COMMENT ' account revenue total ',  PRIMARY key (' id '),  UNIQUE key ' Name_unique ' (' name ')) Engine=innodb A uto_increment=2 DEFAULT Charset=utf8;insert into ' Trade ' VALUES (1, ' Jobs ', ' 18012345678 ', 0.00,0.00,0.00);

==================Python===================

Use Python script to implement additions and deletions and transaction processing, the source code is as follows:

Import pymysql.cursors# connect to database connect = Pymysql. Connect (host= ' localhost ', port=3310, user= ' Woider ', passwd= ' 3243 ', db= ' python ', charset= ' UTF8 ') # get cursor CU Rsor = Connect.cursor () # Insert Data sql = "INSERT into trade (name, account, saving) VALUES ('%s ', '%s ',%.2f)" data = (' Lei June ', ' 1 3512345678 ', 10000) cursor.execute (sql% data) Connect.commit () print (' Insert successfully ', Cursor.rowcount, ' data ') # Modify Data sql = "UPDATE Trade SET saving =%.2f WHERE account = '%s ' "data = (8888, ' 13512345678 ') cursor.execute (sql% data) Connect.commit () print ( ' Successfully modified ', Cursor.rowcount, ' bar data ') # query data sql = "Select name,saving from trade WHERE account = '%s '" data = (' 13512345678 ',) cur Sor.execute (sql% data) for row in Cursor.fetchall (): Print ("name:%s\tsaving:%.2f"% row) print (' co-find ', Cursor.rowcount, ' Data ') # delete Data sql = "Delete from trade WHERE account = '%s ' LIMIT%d" data = (' 13512345678 ', 1) cursor.execute (SQL% data) conn Ect.commit () print (' Successful delete ', Cursor.rowcount, ' data ') # transaction Sql_1 = "UPDATE trade SET saving = saving + 1000 where account = ' 18012345678 ' "sql_2 =" UPDATE trade SET expend = expend + $ WHERE account = ' 18012345678 ' "sql_3 =" U PDATE Trade SET Income = income + a WHERE account = ' 18012345678 ' "Try:cursor.execute (sql_1) # Saving increases the cursor. Execute (sql_2) # Expenditure increased by Cursor.execute (Sql_3) # Revenue increased by 2000except Exception as E:connect.rollback () # transaction rollback PRI NT (' transaction failed ', E) else:connect.commit () # Transaction commit print (' transaction success ', Cursor.rowcount) # Close Connection cursor.close () Connect.close ()

Python 3.5 Connect to MySQL database (pymysql mode)