python3.6不支持 import MySQLdb 改用為 import pymysql 模塊，需要自行安裝模塊pymysql。
- python3.6安裝模塊pymysql
管理員命令行安裝: pip install pymysql
2：python3.6連接mysql數(shù)據(jù)庫
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(
user="root",
password="xxxxxx",
port=3306,
host="127.0.0.1", #本地數(shù)據(jù)庫 等同于localhost
db="MYSQL", #數(shù)據(jù)庫名
charset="utf8"
)
cur = conn.cursor() #獲取對應(yīng)的操作游標
query = 'select count(*) from tableName'
cur.execute(query)
cur.close()
conn.commit()
conn.close()
請注意連接數(shù)據(jù)庫時要保證數(shù)據(jù)庫已經(jīng)開啟，否則連接失敗。
- 獲取SQL語句得到的結(jié)果給Python
使用 fetchone() 方法獲取一條數(shù)據(jù)，返回一個tuple
query = "select * from consume where studentId='1111111111' and Jdate = '2014-11-23';"
cur.execute(query)
data = cur.fetchone()
print(data)
結(jié)果：
('1111111111', '111122', '良鄉(xiāng)開水機 ', datetime.date(2014, 11, 23), datetime.timedelta(0, 2118), -4)
使用 fetchall() 方法獲取多條數(shù)據(jù)，返回一個tuple，每條數(shù)據(jù)又是一個tuple
query = "select * from consume where studentId='1120131007' and Jdate = '2014-11-23';"
cur.execute(query)
data = cur.fetchall()
print(data)
結(jié)果如下：
(('1120131007', '159722', '良鄉(xiāng) ',
datetime.date(2014, 11, 23), datetime.timedelta(0, 2118), -4),
('1120131007', '159722', '良鄉(xiāng) ',
datetime.date(2014, 11, 23), datetime.timedelta(0, 38805), -4),
('1120131007', '159722', '良鄉(xiāng) ',
datetime.date(2014, 11, 23), datetime.timedelta(0, 46505), -1180),
('1120131007', '159722', '后勤 ',
datetime.date(2014, 11, 23), datetime.timedelta(0, 44232), -1),
('1120131007', '159722', '良鄉(xiāng) ',
datetime.date(2014, 11, 23), datetime.timedelta(0, 67821), -180),
('1120131007', '159722', '良鄉(xiāng) ',
datetime.date(2014, 11, 23), datetime.timedelta(0, 67796), -200),
('1120131007', '159722', '良鄉(xiāng) ',
datetime.date(2014, 11, 23), datetime.timedelta(0, 67770), -44))
- 參數(shù)傳遞
以下代碼使用變量向SQL語句中傳遞參數(shù):
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)
