Python uses pymysql for MySQL operation

Pymsql is a module that operates MySQL in Python and is used almost the same way as MySQLdb. However, currently Pymysql supports python3.x and the 3.x version is not supported by the latter.

Applicable environment

Python version >=2.6 or 3.3

MySQL version >=4.1

Installation

You can either use the PIP installation or you can manually download the installation.

Using the PIP installation, execute the following command at the command line:

Pip Install Pymysql

To install manually, please download it first. Download Address: Https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X.

The x.x is the version (the latest version available is 0.6.6).

Unzip the package after downloading. Enter the extracted directory at the command line and execute the following command:

Python setup.py Install

It is recommended to use PIP installation.

Using the 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, which I think 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 get the cursor before executing the SQL statement, because the default auto-commit is configured, so you need to commit the SQL statement after executing it, and finally do not forget to close the connection:

From datetime import Date, datetime, Timedeltaimport pymysql.cursors  #连接配置信息config = {     ' host ': ' 127.0.0.1 ',     ' Port ': 3306,     ' user ': ' Root ',     ' password ': ' zhyea.com ',     ' db ': ' Employees ',     ' charset ': ' Utf8mb4 ' ,     ' Cursorclass ':p ymysql.cursors.DictCursor,     }# Create connection connection = Pymysql.connect (**config)  # Get tomorrow's Time tomorrow = DateTime.Now (). Date () + Timedelta (Days=1)  # Execute SQL statement try: with  connection.cursor () as cursor  :    # Execute SQL statement, insert record    sql = ' INSERT INTO employees (first_name, last_name, hire_date, Gender, Birth_date) VALUES (%s, %s,%s,%s,%s) '    cursor.execute (sql, (' Robin ', ' Zhyea ', Tomorrow, ' M ', Date (1989, 6, +)));  # no default auto-commit is set, you need to commit voluntarily to save the executed statement  Connection.commit ()  finally:  connection.close ();

To execute a query:

Import Datetimeimport pymysql.cursors  #连接配置信息config = {     ' host ': ' 127.0.0.1 ',     ' Port ': 3306,     ' user ': ' Root ',     ' password ': ' zhyea.com ',     ' db ': ' Employees ',     ' charset ': ' Utf8mb4 ',     ' cursorclass ': Pymysql.cursors.DictCursor,     }# Create connection connection = Pymysql.connect (**config)  # get Hire Date Hire_start = datetime.date (1999, 1, 1) hire_end = datetime.date (+)  # Execute SQL statement try: with  connection.cursor () as cursor:    # Execute S QL statement, query    sql = ' SELECT first_name, last_name, hire_date from Employees WHERE hire_date between%s and%s '    curso R.execute (SQL, (Hire_start, Hire_end))    # Get query results result    = Cursor.fetchone ()    print (Result)  # The default autocommit is not set and requires an active commit to save the executed statement  Connection.commit ()  finally:  connection.close ();

The query here draws a query result, and the query results are returned as a dictionary:

To get a specified number of records from the result set, you can use the Fetchmany method:

result = Cursor.fetchmany (2)

It is not recommended, however, to set the total number of records for the query in the SQL statement.

You can use the Fetchall method to get all the result sets:

result = Cursor.fetchall ()

Because there are only two records, the two query methods mentioned above have the same result:

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

Use in Django

Using Django is the original purpose of my finding this. At the same time support python3.4, django1.8 database backend is not easy to find. This is the best thing I've found at the moment.

setting databases and the official recommended MYSQLDB settings is no different:

DATABASES = {
' Default ': {
' ENGINE ': ' Django.db.backends.mysql ',
' NAME ': ' MyTest ',
' USER ': ' Root ',
' PASSWORD ': ' zhyea.com ',
' HOST ': ' 127.0.0.1 ',
' PORT ': ' 3306 ',
}
}

The key is here: We also need to add the following content to the site's __init__.py file:

Import Pymysql
Pymysql.install_as_mysqldb ()

Finally to everyone attached pymysql implementation of the code to implement additions and deletions, I hope you can like

#!/usr/bin/python#coding:gbkimport pymysqlfrom builtins Import int #将MysqlHelper的几个函数写出来 def conndb (): #连接数据  Library Conn=pymysql.connect (host= "localhost", user= "root", passwd= "zx69728537", db= "student");  Cur=conn.cursor (); return (conn,cur);  def exeupdate (conn,cur,sql): #更新或插入操作 sta=cur.execute (SQL);  Conn.commit (); return (STA);  def exedelete (conn,cur,ids): #删除操作 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): #查找操作 cur.execute (SQL);   return (cur);  def connclose (conn,cur): #关闭连接, releasing Resources cur.close (); Conn.close (); Result=true;print ("Please select the above four actions: 1, modify record, 2, add record, 3, query record, 4, delete record."    Press Q for exit); Conn,cur=conndb (); Number=input (); while (result): if (number== ' Q '): print ("End operation");  Break    elif (int (number) ==1): Sql=input ("Please enter an UPDATE statement:");      Try:exeupdate (conn, cur, SQL);    Print ("Update succeeded");     Except Exception:print ("update failed"); Raise      elif (int (number) ==2): Sql=input ("Please enter new statement:");        Try:exeupdate (conn, cur, SQL);      Print ("new success");        Except Exception:print ("New Failure");  Raise    elif (int (number) ==3): Sql=input ("Please enter query statement:");      Try:cur=exequery (cur, SQL);    For item in Cur:print ("id=" +str (item[0]) + "name=" +item[1]);      Except Exception:print ("Error in Query");  Raise    elif (int (number) ==4): Ids=input ("Please enter an ID, separated by a space");      TRY:EXEDELETE (conn, cur, Ids);    Print ("delete succeeded");      Except Exception:print ("delete failed");  Raise    Else:print ("Illegal input, will end operation!");    Result=false;  Break Print ("Please select the above four actions: 1, modify record, 2, add record, 3, query record, 4, delete record.")  Press Q for Exit) "); Number=input ("Please select operation");