python USES pymysql to implement the operation mysql

pymsql is the module in Python that operates MySQL, and it is used in almost the same way as MySQLdb. Currently, pymysql supports python3.x, while the latter does not support the 3.x version.

Apply to the environment

python version > = 2.6 or 3.3

mysql version > =4.1

The installation

You can install it using pip or download it manually.

Using the pip installation, execute the following command on the command line:

pip install PyMySQL

Manual installation, please download first. Download address: https: / / github com PyMySQL/PyMySQL tarball/pymysql - X. X.

X.X is the version (currently available at 0.6.6).

Unzip the zip after downloading. Enter the extracted directory from the command line and execute the following instructions:

python setup.py install

The pip installation is recommended.

Use 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 sql statement, because the configuration is automatically committed by default, you need to take the initiative to commit after executing sql statement, and don't forget to close the connection at last:

from datetime import date, datetime, timedelta
import pymysql.cursors
 
# Connection configuration information 
config = {
     'host':'127.0.0.1',
     'port':3306,
     'user':'root',
     'password':'zhyea.com',
     'db':'employees',
     'charset':'utf8mb4',
     'cursorclass':pymysql.cursors.DictCursor,
     }
#  Create a connection 
connection = pymysql.connect(**config)
 
#  Get the time for tomorrow 
tomorrow = datetime.now().date() + timedelta(days=1)
 
#  perform sql statements 
try:
  with connection.cursor() as cursor:
    #  perform sql Statement to insert the 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, 14)));
  #  The default automatic commit is not set, and the active commit is required to save the executed statement 
  connection.commit()
 
finally:
  connection.close();

Execute query:

import datetime
import pymysql.cursors
 
# Connection configuration information 
config = {
     'host':'127.0.0.1',
     'port':3306,
     'user':'root',
     'password':'zhyea.com',
     'db':'employees',
     'charset':'utf8mb4',
     'cursorclass':pymysql.cursors.DictCursor,
     }
#  Create a connection 
connection = pymysql.connect(**config)
 
#  Acquisition date of employment 
hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(2016, 12, 31)
 
#  perform sql statements 
try:
  with connection.cursor() as cursor:
    #  perform sql Statement to query 
    sql = 'SELECT first_name, last_name, hire_date FROM employees WHERE hire_date BETWEEN %s AND %s'
    cursor.execute(sql, (hire_start, hire_end))
    #  Get query results 
    result = cursor.fetchone()
    print(result)
  #  The default automatic commit is not set, and the active commit is required to save the executed statement 
  connection.commit()
 
finally:
  connection.close();

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

Get a specified number of records from the result set using 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 the full result set, you can use the fetchall method:

result = cursor.fetchall()

Since 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'}]

Used in django

Using it in django was the original purpose of my search for this. backend, which supports python3.4 and django1.8 at the same time, is not easy to find. This is the best I've found so far.

Setting DATABASES is no different from the official recommended setting of MySQLdb:

DATABASES = {
    'default': {
              'ENGINE': 'django.db.backends.mysql',
              'NAME': 'mytest',
              'USER': 'root',
              'PASSWORD': 'zhyea.com',
              'HOST': '127.0.0.1',
              'PORT': '3306',
      }
}
 

Here's the key: we also need to add the following to the site's s s 140en__.py file:

import pymysql
pymysql.install_as_MySQLdb()


At last, I attach the code of pymysql to you. I hope you like it

#!/usr/bin/python
#coding:gbk
import pymysql
from builtins import int

# will MysqlHelper Let me write down some of the functions 

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 operation 
  sta=cur.execute(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):           # Find operations 
  cur.execute(sql);
  return (cur);
  
def connClose(conn,cur):          # Close the connection and release the resource 
  cur.close();
  conn.close();

result=True;
print(" Please select the above 4 Operation: 1 , modify the record, 2 Add records, 3 , query the record, 4 Delete records .( According to the q To quit )");
conn,cur=connDB();
number=input();
while(result):
  if(number=='q'):
    print(" End of operation ");
    break;
  elif(int(number)==1):
    sql=input(" Please enter update statement: ");
    try:
      exeUpdate(conn, cur, sql);
      print(" The update is successful ");
    except Exception:
      print(" Update failed ");
      raise;
  elif(int(number)==2):
      sql=input(" Please enter a new statement :");
      try:
        exeUpdate(conn, cur, sql);
        print(" New success ");
      except Exception:
        print(" The new failure ");
        raise;
  elif(int(number)==3):
    sql=input(" Please enter the 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 the Id , separated by a space ");
    try:
      exeDelete(conn, cur, Ids);
      print(" Delete the success ");
    except Exception:
      print(" Delete failed ");
      raise;
  else:
    print(" Illegal input will terminate the operation !");
    result=False;
    break;
  print(" Please select the above 4 Operation: 1 , modify the record, 2 Add records, 3 , query the record, 4 Delete records .( According to the q To quit )");
  number=input(" Please select operation ");