python连接本地mysql_python 3.6连接MySQL

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)