Python uses pymysql for mysql operations

This article explains how to use pymysql to operate mysql in python. it is very simple and practical. if you need it, you can refer to pymsql as a module for operating 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.

Applicable environment

Python version> = 2.6 or 3.3

Mysql version> = 4.1

Install

You can use pip or manually download and install the SDK.

Run the following command on the command line to install pip:

Pip install PyMySQL

Install it manually. download it first. : Https://github.com/pymysql/pymysql/tarball/pymysql-x.x.

X. X is the version (the latest version is 0.6.6 ).

Decompress the package. Enter the decompressed directory in the command line and execute the following command:

Python setup. py install

Pip is recommended for installation.

Example

Connect to the database as follows:

import pymysql.cursors  # Connect to the databaseconnection = pymysql.connect(host='127.0.0.1',               port=3306,               user='root',               password='zhyea.com',               db='employees',               charset='utf8mb4',               cursorclass=pymysql.cursors.DictCursor)

You can also use a dictionary to manage connection parameters. I think this is more elegant:

import pymysql.cursors  config = {     'host':'127.0.0.1',     'port':3306,     'user':'root',     'password':'zhyea.com',     'db':'employees',     'charset':'utf8mb4',     'cursorclass':pymysql.cursors.DictCursor,     }  # Connect to the databaseconnection = pymysql.connect(**config)

Insert data:

You need to obtain the cursor before executing the SQL statement. because the configuration is automatically submitted by default, you must take the initiative to commit the SQL statement after it is executed. do not forget to close the connection:

From datetime import date, datetime, timedeltaimport pymysql. cursors # connection configuration information config = {'host': '2017. 0.0.1 ', 'port': 3306, 'user': 'root', 'password': 'zhyea. com ', 'DB': 'ployees', 'charset': 'utf8mb4', 'cursorclass': pymysql. cursors. dictCursor,} # Create a connection = pymysql. connect (** config) # obtain tomorrow's time tomorrow = datetime. now (). date () + timedelta (days = 1) # run the SQL statement try: with connection. cursor () as cursor: # execute the SQL statement and INSERT the record SQL = 'Insert INTO employees (first_name, last_name, hire_date, gender, birth_date) VALUES (% s, % s, % s, % s, % s) 'cursor.exe cute (SQL, ('Robin ', 'zhyea', tomorrow, 'M', date (1989, 6, 14); # No default automatic submission is set. you must submit the statement to save the executed connection. commit () finally: connection. close ();

Run the query:

Import datetimeimport pymysql. cursors # connection configuration information config = {'host': '2017. 0.0.1 ', 'port': 3306, 'user': 'root', 'password': 'zhyea. com ', 'DB': 'ployees', 'charset': 'utf8mb4', 'cursorclass': pymysql. cursors. dictCursor,} # Create a connection = pymysql. connect (** config) # obtain the employment date hire_start = datetime. date (1999, 1, 1) hire_end = datetime. date (2016, 12, 31) # execute the SQL statement try: with connection. cursor () as cursor: # execute an SQL statement to query SQL = 'SELECT first_name, last_name, hire_date FROM employees WHERE hire_date BETWEEN % s AND % s' cursor.exe cute (SQL, (hire_start, hire_end) # obtain the query result = cursor. fetchone () print (result) # automatic submission is not set by default. you need to submit the statement to save the executed connection. commit () finally: connection. close ();

The query here outputs a query result, which is returned in the form of a dictionary:

You can use the fetchmany method to obtain a specified number of records from the result set:

Result = cursor. fetchmany (2)

However, it is not recommended that you set the total number of records to be queried in SQL statements.

You can use the fetchall method to obtain all result sets:

Result = cursor. fetchall ()

Because there are only two records, the results of the two query methods mentioned above are the same:

[{'last_name': 'Vanderkelen', 'hire_date': datetime.date(2015, 8, 12), 'first_name': 'Geert'}, {'last_name': 'Zhyea', 'hire_date': datetime.date(2015, 8, 21), 'first_name': 'Robin'}]

Use in django

Using in django is my initial goal. Currently, database backend that supports both python3.4 and django1.8 is not easy to find. This is the best method I have found.

Setting DATABASES is no different from setting MySQLdb officially recommended:

DATABASES = {
'Default ':{
'Engine': 'Django. db. backends. mysql ',
'Name': 'mytest ',
'User': 'root ',
'Password': 'zhyea. com ',
'Host': '2017. 0.0.1 ',
'Port': '123 ',
}
}

The key is here: we also need to add the following content to the _ init _. py file of the site:

Import pymysql
Pymysql. install_as_MySQLdb ()

Finally, I will attach the code for adding, deleting, modifying, and querying pymysql. I hope you will like it.

#! /Usr/bin/python # coding: gbkimport pymysqlfrom builtins import int # write several functions of MysqlHelper into def connDB (): # connect to database conn = pymysql. connect (host = "localhost", user = "root", passwd = "zx69728537", db = "student"); cur = conn. cursor (); return (conn, cur); def exeUpdate (conn, cur, SQL): # Update or insert operations sta=cur.exe cute (SQL); conn. commit (); return (sta); def exeDelete (conn, cur, IDs): # DELETE operation sta = 0; for eachID in IDs. split (''): sta + = cur. Execute ("delete from students where Id = % d" % (int (eachID); conn. commit (); return (sta); def exeQuery (cur, SQL): # Query operation cur.exe cute (SQL); return (cur); def connClose (conn, cur ): # close the connection and release the resource cur. close (); conn. close (); result = True; print ("Select the above four operations: 1. modify record; 2. add record; 3. query record; 4. delete record. (Exit by q) "); conn, cur = connDB (); number = input (); while (result): if (number = 'Q '): print ("end operation"); break; elif (int (number) = 1): SQL = input ("Please Enter the update statement: "); try: exeUpdate (conn, cur, SQL); print (" update successful "); failed t Exception: print (" update failed "); raise; elif (int (number) = 2): SQL = input ("Enter the new statement:"); try: exeUpdate (conn, cur, SQL ); print ("added successfully"); failed t Exception: print ("failed to add"); raise; elif (int (number) = 3 ): SQL = input ("Enter the query statement:"); try: cur = exeQuery (cur, SQL); for item in cur: print ("Id =" + str (item [0]) + "name =" + item [1]); parse T Exception: print ("query error"); ra Ise; elif (int (number) = 4): Ids = input ("Enter the Id and separate it with spaces"); try: exeDelete (conn, cur, Ids ); print ("deleted successfully"); failed t Exception: print ("failed to delete"); raise; else: print ("illegal input, the operation will end! "); Result = False; break; print (" Select the above four operations: 1. modify record; 2. add record; 3. query record; 4. delete record. (Exit by q) "); number = input (" Select operation ");

For more articles about how to use pymysql to operate mysql in python, refer to PHP!