python3.6不支持 import MySQLdb 改用为 import pymysql 模块，需要自行安装模块pymysql。
- 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()
请注意连接数据库时要保证数据库已经开启，否则连接失败。
- 获取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))
- 参数传递
以下代码使用变量向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)
