PyMySQL is a library used to connect MySQL servers in Python 3.x, and mysqldb is used in Python 2.
Use examples
Connect the database as follows:
importpymysql.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)#!/usr/bin/env python
# --coding = utf-8
# Author Allen Lee
import pymysql
#Creating Link Objects
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='Allen')
#Create a Cursor
cursor = conn.cursor()
#Execute sql, update single data, and return the number of affected rows
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
#Insert multiple entries and return the affected function
effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)",[("1.0.0.1",1,),("10.0.0.3",2)])
#Get the latest self-increasing ID
new_id = cursor.lastrowid
#Query data
cursor.execute("select * from hosts")
#Get a row
row_1 = cursor.fetchone()
#Get multiple (3) rows
row_2 = cursor.fetchmany(3)
#Get all
row_3 = cursor.fetchall()
#Reset the cursor type to the dictionary type
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#Submit, save new or modified data
conn.commit()
#Close the cursor
cursor.close()
#Close the connection
conn.close()
The dictionary can also be used to manage the connection parameters, which I think is more elegant.
importpymysql.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:
Before executing sql statements, you need to get cursor. Because the configuration defaults to autocommit, you need to commit actively after executing sql statements. Finally, don't forget to close the connection:
fromdatetime importdate, 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 time for tomorrow
tomorrow =datetime.now().date()+ timedelta(days=1)
# Execute sql statements
try:
with connection.cursor()as cursor:
# Execute sql statements and insert records
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)));
# There is no default automatic submission set. Active submission is required to save the executed statements.
connection.commit()
finally:
connection.close();
Execute queries:
importdatetime
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 date of employment
hire_start =datetime.date(1999,1, 1)
hire_end= datetime.date(2016,12, 31)
# Execute sql statements
try:
withconnection.cursor()as cursor:
# Execute sql statement and 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)
# There is no default automatic submission set. Active submission is required to save the executed statements.
connection.commit()
finally:
connection.close();
The query here draws a query result, which is returned in the form of a dictionary:
To obtain a specified number of records from the result set, you can use the fetchman method:
result= cursor.fetchmany(2)
However, this is not recommended. It is better to set the total number of records for queries in sql statements.
fetchall method can be used to obtain all result sets:
result= cursor.fetchall()
Because there are only two records, the results of these two queries 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'}]importpymysql pymysql.install_as_MySQLdb()
Use in django
Using it in django was my original goal. At present, the database backend supporting Python 3.4 and django 1.8 is not easy to find. This is the best one I've found so far.
There is no difference between setting up DATABASES and the officially recommended MySQLdb settings:
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 _init_.py file:
importpymysql pymysql.install_as_MySQLdb()