Python 3.x Connection Database example (Pymysql mode)

Since the MySQLdb module does not yet support python3.x, python3.x need to install the Pymysql module if you want to connect to MySQL.

The Pymysql module can be installed via PIP. But if you're using the Pycharm IDE, you can use Project Python to install third-party modules.

[File] >> [Settings] >> [Project:python] >> [Project interpreter] >> [Install button]

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): username

• passwd (str): password

• DB (str): Database name

• CharSet (str): Connection encoding

Methods supported by the Connection object

• Cursor () Use this connection to create and return cursors

• Commit () commits the current transaction

• Rollback () Rollback of the current transaction

• Close () Closes the connection

Methods supported by the cursor object

• Execute (OP) executes a query command for a database

• Fetchone () Gets the next row of the result set

• Fetchmany (size) Gets the next few rows of the result set

• Fetchall () Gets all rows in the result set

• 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 ', ' account  ' varchar (one) NOT null COMMENT ' bank savings account ', ' Saving ' decimal (8,2) unsigned not null DEFAULT ' 0.00 ' COMMENT ' Account savings amount ', ' expend ' Decimal (8,2) unsigned NOT NULL default ' 0.00 ' COMMENT ' account expenditure total ', ' Income ' decimal (8,2) unsigned NOT NULL default ' 0.00 ' CO Mment ' account revenue total ', PRIMARY key (' id '), UNIQUE key ' Name_unique ' (' name ')) Engine=innodb auto_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 cursors cursor = Conne  Ct.cursor () # Insert Data sql = "INSERT into trade (name, account, saving) VALUES ('%s ', '%s ',%.2f)" data = (' Lei June ', ' 13512345678 ', 10000) cursor.execute (sql% data) Connect.commit () print (' Insert successfully ', Cursor.rowcount, ' data ') # Modify Data sql = "UPDATE trade SET Savin g =%.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 ',) cursor.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) Connect.commit () print (' successful delete ', Cursor.rowcount, ' data ') # transaction Sql_1 = "UPDATE trade SET saving = saving + = ' 18012345678 ' "sql_2 =" Update trade SET expend = expend + $ WHERE account = ' 18012345678 ' "sql_3 =" Update trade SE T income = income + WHERE account = ' 18012345678 ' "Try:cursor.execute (sql_1) # Savings increased by $ cursor.execute (sql_2) # expense Increased Cursor.execute (sql_3) # Revenue increased 2000except Exception as E:connect.rollback () # Transaction rollback print (' transaction failed ', E) Else:connec T.commit () # Transaction commit print (' transaction success ', Cursor.rowcount) # Close Connection cursor.close () Connect.close ()

================== Test Results ===================

The above is the whole content of this article, I hope that everyone's learning has helped, but also hope that we support topic.alibabacloud.com.