http://dev.mysql.com/downloads/connector/python/
Select Platform:选择Platform Independent (Architecture Independent), Compressed TAR ,下载源码mysql-connector-python-2.1.3.tar.gz
tar xzvf mysql-connector-python-2.1.3.tar.gz
cd mysql-connector-python-2.1.3/
sudo python3 setup.py install --with-mysql-capi=/opt/mysql(change to you mysqlserver path)
或
sudo python setup.py install --with-mysql-capi=/opt/mysql(change to you mysqlserver path)
一般说数据库服务如果不是安装在系统默认路径下的情况,需要下载源码自己编译驱动时指定对应的路径,本人unixodbc是通过apt-get install来安装的,因此安装odbc的python驱动只需简单的sudo pip3 install pyodbc即可。
测试代码:
import mysql.connector
conn = mysql.connector.connect(host='127.0.0.1', port=3306, user='your-user', passwd='your-passwd',
db='you-db')
cur = conn.cursor()
cur.execute("SET NAMES utf8")
cur.execute("SELECT * FROM you-table")
for r in cur.fetchall():
print(r)
conn.close()
使用python3中文显示正常、python2则显示中文对应的utf-8代码,这是因为python3和python2默认编码格式不一样。
$ python
Python 2.7.6 (default, Jun 22 2015, 17:58:13)
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sys
>>> sys.getdefaultencoding()
'ascii'
>>> quit()
$ python3
Python 3.4.3 (default, Oct 14 2015, 20:28:29)
[GCC 4.8.4] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sys
>>> sys.getdefaultencoding()
'utf-8'
对字符串类型可以正常输出、而对聚合类型(列表、元组 、字典等)不行、如改为以下代码则python2可以正常显示中文了
aa=cur.execute("select * from you-table")
info = cur.fetchmany(aa)
for ii in info:
print ii[0]+' '+ii[1]+' '+ii[2]+' '+ii[3]+......
conn.close()
或者
#获得表中有多少条数据
cur.execute("SET NAMES utf8")
aa=cur.execute("select * from you-table")
print aa
#打印表中的多少数据
info = cur.fetchmany(aa)
for tup in info:
for ii in tup:
print ii
cur.close()
conn.commit()
conn.close()
fetchmany得到的是字符串的元组,总之要转化成字符串以后再print中文就没问题了
下面的例子对python2中文问题作了很好的说明:
$ python
Python 2.7.6 (default, Jun 22 2015, 17:58:13)
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> a = ['你好','哈哈','呵呵']
>>> print a
['\xe4\xbd\xa0\xe5\xa5\xbd', '\xe5\x93\x88\xe5\x93\x88', '\xe5\x91\xb5\xe5\x91\xb5']
>>> for m in a:
... print m
...
你好
哈哈
呵呵
更通用的写法
import mysql.connector
import sys
try:
conn = mysql.connector.connect(host='127.0.0.1', port=3306, user='your-user', passwd='your-passwd',
db='you-db')
cur = conn.cursor()
cur.execute("SELECT * FROM you-table")
data = cur.fetchall()
for row in data:
for col in row:
print col, #python3改为print (col, end=' ')
print #python3改为print()
except mysql.connector.Error AS e:
print ("Error %d: %s" % (e.args[0],e.args[1]))
sys.exit(1)
finally:
if conn:
conn.close()
动态sql例子
建表的sql语句
CREATE TABLE `COURSE` (
`CNO` char(6) NOT NULL,
`CNAME` char(12) NOT NULL,
`TEACHER` char(8) NOT NULL,
`TIME` smallint(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `STUDENT` (
`SNO` char(7) NOT NULL,
`SNAME` char(8) NOT NULL,
`SEX` char(2) NOT NULL,
`BDATE` date DEFAULT NULL,
`DIR` char(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `SC` (
`SNO` char(7) NOT NULL,
`CNO` char(6) NOT NULL,
`GRADE` decimal(6,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `COURSE`
ADD PRIMARY KEY (`CNO`);
ALTER TABLE `STUDENT`
ADD PRIMARY KEY (`SNO`);
ALTER TABLE `SC`
ADD PRIMARY KEY (`SNO`,`CNO`),
ADD KEY `CNO` (`CNO`);
ALTER TABLE `SC`
ADD CONSTRAINT `SC_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `STUDENT` (`SNO`) ON DELETE CASCADE ON UPDATE NO ACTION,
ADD CONSTRAINT `SC_ibfk_2` FOREIGN KEY (`CNO`) REFERENCES `COURSE` (`CNO`) ON UPDATE NO ACTION;
#coding=utf-8
import mysql.connector
conn = mysql.connector.connect(host='127.0.0.1', port=3306, user='your-user', passwd='your-passwd',
db='you-db')
cur = conn.cursor()
#查询成绩大于88对应的学生和课程
sql = "SELECT SNAME,CNAME,GRADE FROM STUDENT \
Inner JOIN SC ON STUDENT.SNO = SC.SNO \
Inner JOIN COURSE ON COURSE.CNO = SC.CNO \
WHERE GRADE > '%d'"
cur.execute(sql % (88))
data = cur.fetchall()
#打印表中的多少数据
for row in data:
for col in row:
print col,
print
conn.close()