Shulou(Shulou.com)06/01 Report--

Python 3.x connect to the database (pymysql mode)

= pymysql=

Because the MySQLdb module does not yet support Python3.x, Python3.x needs to install the pymysql module if it wants to connect to MySQL.

The pymysql module can be installed through pip. But if you are using 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 for database connections, pymysql and MySQLdb are similar in usage:

The pymysql.Connect () parameter describes 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 method supported by the connection object cursor () uses this connection to create and return cursor commit () to submit when The former transaction rollback () rolls back the current transaction close () closes the method supported by the connection cursor object execute (op) executes a database query command 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 rows close () closes the cursor object

= MySQL=

First, before connecting to the database, create a transaction table to test the functionality of pymysql:

DROP TABLE IF EXISTS `trade` CREATE TABLE `trade` (`id` int (4) unsigned NOT NULL AUTO_INCREMENT, `name` varchar (6) NOT NULL COMMENT 'user's real name', `account` varchar (11) NOT NULL COMMENT 'bank savings account', `saving`decimal (8) unsigned NOT NULL DEFAULT '0.00' COMMENT' account savings amount', `expend` decimal (8) unsigned NOT NULL DEFAULT '0.00' COMMENT' account expenditure', `income` decimal (8) unsigned NOT NULL DEFAULT '0.00' COMMENT' account income' PRIMARY KEY (`id`), UNIQUE KEY `name_ UNIQUE` (`name`) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 INSERT INTO `trade` VALUES (1pm 'Jobs', '18012345678' mint 0.00PX 0.00)

= Python=

Use Python script to add, delete, modify, query and handle transactions. The source codes are as follows:

Import pymysql.cursors# connection database connect = pymysql.Connect (host='localhost', port=3310, user='woider', passwd='3243', db='python', charset='utf8') # get cursor cursor = connect.cursor () # insert data sql = "INSERT INTO trade (name, account, saving) VALUES ('% sails,'% slots,% .2f)" data = ('Lei Jun', '13512345678' 10000) cursor.execute (sql% data) connect.commit () print ('successfully inserted', 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, '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 (' total found', cursor.rowcount, 'data') # Delete data sql =" DELETE FROM trade WHERE account ='% s' LIMIT% d "data = ('13512345678, 1) cursor.execute (sql% data) connect.commit () print (' deleted 'successfully Cursor.rowcount ) # transaction sql_1 = "UPDATE trade SET saving = saving + 1000 WHERE account = '18012345678'" sql_2 = "UPDATE trade SET expend = expend + 1000 WHERE account = '18012345678'" sql_3 = "UPDATE trade SET income = income + 2000 WHERE account = '18012345678'" try: cursor.execute (sql_1) # savings increase by 1000 cursor.execute (sql_2) # Expenditure increases by 1000 cursor.execute (sql_ 3) # Revenue growth 2000except Exception as e: connect.rollback () # transaction rollback print ('transaction failed' E) else: connect.commit () # transaction commit print ('transaction successful', cursor.rowcount) # close connection cursor.close () connect.close ()

= test results =

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.