python 3.6连接MySQL

python3.6不支持 import MySQLdb 改用为 import pymysql 模块，需要自行安装模块pymysql。

1. python3.6安装模块pymysql

管理员命令行安装: pip install pymysql

2：python3.6连接mysql数据库

# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(
  user="root",
  password="xxxxxx",
  port=3306,
  host="127.0.0.1",   #本地数据库  等同于localhost
  db="MYSQL",      #数据库名
  charset="utf8"
)
cur = conn.cursor()      #获取对应的操作游标
query = 'select count(*) from tableName'
cur.execute(query)
cur.close()
conn.commit()
conn.close()

请注意连接数据库时要保证数据库已经开启，否则连接失败。

2. 获取SQL语句得到的结果给Python
   使用 fetchone() 方法获取一条数据，返回一个tuple

query = "select * from consume where studentId='1111111111' and Jdate = '2014-11-23';"
cur.execute(query)
data = cur.fetchone()
print(data)

结果：
('1111111111', '111122', '良乡开水机 ', datetime.date(2014, 11, 23), datetime.timedelta(0, 2118), -4)
使用 fetchall() 方法获取多条数据，返回一个tuple，每条数据又是一个tuple

query = "select * from consume where studentId='1120131007' and Jdate = '2014-11-23';"
cur.execute(query)
data = cur.fetchall()
print(data)

结果如下：

(('1120131007', '159722', '良乡         ', 
datetime.date(2014, 11, 23), datetime.timedelta(0, 2118), -4), 
('1120131007', '159722', '良乡         ', 
datetime.date(2014, 11, 23), datetime.timedelta(0, 38805), -4), 
('1120131007', '159722', '良乡          ', 
datetime.date(2014, 11, 23), datetime.timedelta(0, 46505), -1180), 
('1120131007', '159722', '后勤             ',
 datetime.date(2014, 11, 23), datetime.timedelta(0, 44232), -1), 
('1120131007', '159722', '良乡            ', 
datetime.date(2014, 11, 23), datetime.timedelta(0, 67821), -180), 
('1120131007', '159722', '良乡       ', 
datetime.date(2014, 11, 23), datetime.timedelta(0, 67796), -200), 
('1120131007', '159722', '良乡            ',
 datetime.date(2014, 11, 23), datetime.timedelta(0, 67770), -44))

3. 参数传递
   以下代码使用变量向SQL语句中传递参数:

user_id = "test123"
password = "password"

con.execute('insert into Login values("%s", "%s")' % \
             (user_id, password))

或者：

sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
cursor.execute(sql)

在或者：

query = 'insert into consume (studentId, cardId, address, Jdate, Jtime, money) values (%s, %s, %s, %s, %s, %s)'\
studentId = '1111111'
cardId ='1111'
address = "xxxx"
Jdate = "xxxx"
Jtime = "xxxx"
money = "xxxx"
values = (studentId, cardId, address, Jdate, Jtime, money)
cur.execute(query, values)