Pymysql operation database and pymysql Database

Source: Internet
Author: User

Pymysql operation database and pymysql Database

I. Introduction

Pymsql is a module used to operate MySQL in Python. Its usage is almost the same as that of MySQLdb. However, pymysql currently supports python3.x, while the latter does not support version 3.x.

The execution statement is similar to the SQL source code.

Ii. Use

1. Install

Pip install pymysql

2. Operation

First, let's look at a complete connection and basic operations.

Import pymysql # create a connection conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '000000', db = 't1') # create a cursor = conn. cursor () # execute the SQL statement and return the number of rows affected by the receipt. Export t_row = cursor.exe cute ("update hosts set host = '1. 1.1.2 '") # execute the SQL statement and return the affected number of rows # effect_row = cursor.exe cute (" update hosts set host = '1. 1.1.2 'where nid> % s ", (1,) # Run the SQL statement and return the number of affected rows # effect_row = cursor.exe cute.pdf (" insert into hosts (host, color_id) values (% s, % s) ", [(" 1.1.1.11 ", 1), (" 1.1.1.11 ", 2)]) # submit, otherwise, the new or modified data conn cannot be saved. commit () # Close the cursor. close () # close the connection conn. close ()

Insert data to the database and use the try statement to actively roll back when an exception occurs.

#! /Usr/bin/python3import pymysql # Open the database connection db = pymysql. connect ("localhost", "testuser", "test123", "TESTDB") # Use the cursor () method to obtain the operation cursor = db. cursor () # SQL insert statement SQL = "INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('mac', 'mohan', 20, 'M', 2000) "" try: # execute the SQL statement cursor.exe cute (SQL) # submit it to the database to execute the db. commit () commit T: # roll back the database if an error occurs. rollback () # disable database connection to db. close ()

3. insert multiple data entries into the data table. Use the executemany method to insert multiple data entries into the production environment. After obtaining the data in the background, input statements in the form of a list ([('v1 ', 'v2'), ('v3', 'v4 ')])

# Create a connection conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '000000', db = 't1') # create cursor cur = conn. cursor () if request. method = "POST": title = request. POST. get ("title") title_en = request. POST. get ("title_en") content = request. POST. get ("content") content_en = request. POST. get ("content_en") notification_type = request. POST. get ("icationication_type "). strip () user_list = request. POST. get ("user_list") updated_datetime = datetime. now () created_datetime = datetime. now () values_list = [] for user in user_id_list: temp = updated_datetime, created_datetime, title, title_en, content, content_en, icationication_type, user ['id'] values_list.append (temp ))
Try: cur.exe cute.pdf (''' insert into app_notification (updated_datetime, created_datetime, title, title_en, content, content_en, icationication_type, is_read, recipient_id) values (% s, % s, % s, 0, % s) ''', values_list) conn. commit () conn. close ()
     
    except Exception as err:
    conn.rollback()
    logging.error(err)
    logging.error(traceback.format_exc())
    conn.close()
# Obtain the latest auto-increment ID   new_id =  cursor .lastrowid

4. database query operations

For Python query, Mysql uses the fetchone () method to obtain a single piece of data and the fetchall () method to obtain multiple pieces of data.

  • Fetchone ():This method gets the next query result set. The result set is an object.
  • Fetchall ():Receives all returned results.
  • Rowcount:This is a read-only attribute and returns the number of rows affected by execution of the execute () method.
Import pymysql conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '000000', db = 't1') cursor = conn.cursor()cursor.exe cute ("select * from hosts ") # obtain the first row of Data row_1 = cursor. fetchone () # obtain the first n rows of data # row_2 = cursor. fetchiterator (3) # obtain all data # row_3 = cursor. fetchall () conn. commit () cursor. close () conn. close ()

Note: When fetch data is performed in order, you can use cursor. scroll (num, mode) to move the cursor position, for example:

  • Cursor. scroll (1, mode = 'relative ') # Move relative to the current position
  • Cursor. scroll (2, mode = 'absolute ') # Move relative to absolute position

5. Fetch Data Type

The data obtained by default is of the Ancestor Type. If you want data of the dictionary type, that is:

#! /Usr/bin/env python #-*-coding: UTF-8-*-import pymysql conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '000000', db = 't1') # Set the cursor to the dictionary type cursor = conn. cursor (cursor = pymysql. cursors. dictCursor) r = cursor.exe cute ("call p1 ()") result = cursor. fetchone () conn. commit () cursor. close () conn. close ()
Error Handling

Db api defines database operation errors and exceptions. The following table lists these errors and exceptions:

Exception Description
Warning Triggered when a severe warning occurs, for example, data insertion is truncated. Must be a subclass of StandardError.
Error Warning. Must be a subclass of StandardError.
InterfaceError This is triggered when an error occurs in the database interface module rather than in the database. Must be a subclass of Error.
DatabaseError Database-related errors are triggered. Must be a subclass of Error.
DataError This function is triggered when an error occurs during data processing, for example, Division by zero error or data out of range. Must be a subclass of DatabaseError.
OperationalError An error occurs when operating the database instead of being controlled by the user. For example, database operations such as accidental disconnection, database name not found, transaction processing failure, and memory allocation error occur. Must be a subclass of DatabaseError.
IntegrityError Integrity-related errors, such as foreign key check failure. Must be a subclass of DatabaseError.
InternalError Internal database errors, such as cursor failures and transaction synchronization failures. Must be a subclass of DatabaseError.
ProgrammingError Program errors, such as data table not found or already exists, SQL statement syntax errors, parameter quantity errors, and so on. Must be a subclass of DatabaseError.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.