Python uses Pymysql to implement operations Mysql_python

Pymsql is the module that operates MySQL in Python, and its use is almost the same as MySQLdb. But currently pymysql support python3.x the latter does not support the 3.x version.

Applicable environment

Python version >=2.6 or 3.3

MySQL version >=4.1

Installation

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

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

pip install PyMySQL

Manual installation, please download 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 compressed package after downloading. At the command line, enter the unpacked directory and execute the following instructions:

python setup.py install

It is recommended to use PIP installation.

Using the sample

The connection database is as follows:

Import pymysql.cursors
 
# Connect
to the database connection = 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 database
connection = Pymysql.connect (**config)

Insert data:

You need to get cursor before executing the SQL statement because the configuration defaults to autocommit, so you need to commit after executing the SQL statement, and don't forget to close the connection at the end:

From datetime import Date, datetime, Timedelta
import 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 ', Tomorro W, ' M ', Date (1989, 6,));
  # no default autocommit is set up, and a proactive submission is required to save the executed statement
  Connection.commit ()
 
finally:
  connection.close ();

To execute a query:

Import datetime
Import 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 Hire date
Hire_start = Datetime.date (1999, 1, 1)
hire_end = Datetime.date (2016, to)
 
# Execute SQL statement
try: With
  Connection.cursor () as cursor:
    # Execute SQL statement, query
    sql = ' SELECT first_name, last_name, hire_date from EM Ployees WHERE hire_date BETWEEN%s and%s '
    cursor.execute (SQL, (Hire_start, Hire_end))
    # Get query results result
    = C Ursor.fetchone ()
    print (Result)
  # does not set default autocommit, requires proactive submission to save the executed statement
  Connection.commit ()
 
finally:
  Connection.close ();

The query here draws a query result, and the query results are returned in the form of a dictionary:

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

result = cursor.fetchmany(2)

This is not recommended, however, and it is best to set the total number of records for the query in the SQL statement.

To get all result sets you can use the Fetchall method:

result = cursor.fetchall()

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

Copy Code code as follows:

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

Using in Django

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

Setting the databases and the official recommended use of the 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 to the __init__.py file in the site:

import pymysql
pymysql.install_as_MySQLdb()


Finally, I would like to attach pymysql implementation of the code to check and delete, I hope you can enjoy

#!/usr/bin/python #coding: GBK import pymysql from builtins import int #将MysqlHelper的几个函数写出来 def conndb ():
  #连接数据库 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 Four actions: 1, modify records, 2, add records, 3, Query records, 4, delete records."
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 the 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 a query statement:");
      Try:cur=exequery (cur, SQL);
    For item in Cur:print ("id=" +str (item[0)) + "Name=" +item[1]);
      Except Exception:print ("Query Error");
  Raise
    elif (int (number) ==4): Ids=input ("Please enter ID, separated by 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 Four actions: 1, modify records, 2, add records, 3, Query records, 4, delete records."
  Press Q for Exit) ");
 Number=input ("Please select Action");