Python 3.x database connection example (pymysql mode), pythonpymysql

Python 3.x database connection example (pymysql mode), pythonpymysql

Because the MySQLdb module does not support Python3.x, you need to install the pymysql module if you want to connect to MySQL.

The pymysql module can be installed through pip. If you are using pycharm IDE, you can use project python to install a third-party module.

[File]> [settings]> [Project: python]> [Project Interpreter]> [Install button]

Because Python unifies the database connection interface, pymysql and MySQLdb are used in a similar way:

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): Connection Encoding

Methods supported by the connection object

• Cursor () creates a connection and returns a cursor.
• Commit () commits the current transaction
• Rollback () rolls back the current transaction
• Close () close the connection

Method supported by cursor object

• Execute (op) to execute a database query command
• Fetchone () gets the next row of the result set
• Fetchiterator (size) to obtain the next few rows of the result set
• Fetchall () gets all rows in the result set
• Rowcount () returns the number of data entries or affects the number of rows.
• Close () close the cursor object

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

First, create a transaction table before connecting to the database to test the functions 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 real name', 'account' varchar (11) 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 total expenditure ', 'Welcome' decimal (8, 2) unsigned not null default '0. 00 'comment' account total revenue ', primary key ('id'), unique key 'name _ UNIQUE' ('name ')) ENGINE = InnoDB AUTO_INCREMENT = 2 default charset = utf8; insert into 'trade 'VALUES (1, 'jobs', '123', 18012345678, 0.00, 0.00 );

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

Use a Python script to add, delete, modify, query, and process transactions. The source code is as follows:

Import pymysql. cursors # connect to the database connect = pymysql. connect (host = 'localhost', port = 3310, user = 'woider ', passwd = '000000', db = 'python', charset = 'utf8 ') # obtain the cursor = connect. cursor () # INSERT data SQL = "INSERT INTO trade (name, account, saving) VALUES ('% s',' % s', %. 2f) "data = ('rayjun', '123456', using repeated cursor.exe cute (SQL % data) connect. commit () print ('inserted successfully ', cursor. rowcount, 'data') # modify data SQL = "UPDATE trade SET saving = %. 2f WHERE account = '% S' "data = (8888, '13512345678'your cursor.exe cute (SQL % data) connect. commit () print ('Modified successfully ', cursor. rowcount, 'data') # query data SQL = "SELECT name, saving FROM trade WHERE account = '% S'" data = ('13512345678', using cursor.exe cute (SQL % data) for row in cursor. fetchall (): print ("Name: % s \ tSaving: %. 2f "% row) print ('common lookup files', cursor. rowcount, 'data') # DELETE data SQL = "DELETE FROM trade WHERE account = '% s' LIMIT % d" data = ('20140901 ', 1)cursor.exe cute (SQL % data) connect. commit () print ('deleted successfully ', cursor. rowcount, 'items data ') # transaction processing SQL _1 = "UPDATE trade SET saving = saving + 1000 WHERE account = '000000'" SQL _2 = "UPDATE trade SET expend = expend + 18012345678 WHERE account = '000000'" SQL _3 = "UPDATE trade SET income = income + 2000 WHERE account = '000000'" try: cursor.exe cute (SQL _1) # savings increase by 1000 cursor.exe cute (SQL _2) # expenditure increase by 1000 cursor.exe cute (SQL _3) # revenue increase by 2000 RMB t Exception as e: connect. rollback () # transaction rollback print ('transaction processing failed', e) else: connect. commit () # transaction commit print ('transaction processed successfully', cursor. rowcount) # Close the connection to cursor. close () connect. close ()

=

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.