python-数据库编程mysql

发表于 | 分类于

Python — 数据库编程之Mysql

Tags：Python

MySQLDB简介

Python 标准数据库接口为 Python DB-API，Python DB-API为开发人员提供了数据库应用编程接口。

Python 数据库接口支持非常多的数据库，你可以选择适合你项目的数据库：

  • MySQL
  • PostgreSQL
  • Oracle

不同的数据库你需要下载不同的DB API模块，例如你需要访问Oracle数据库和Mysql数据，你需要下载Oracle和MySQL数据库模块。

DB-API 是一个规范. 它定义了一系列必须的对象和数据库存取方式, 以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口 。

Python的DB-API，为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各数据库。

Python DB-API使用流程：

  • 引入 API 模块。
  • 获取与数据库的连接。
  • 执行SQL语句和存储过程。
  • 关闭数据库连接。

MySQLDB安装

下载MySQLdb

MySQLdb 是用于Python链接Mysql数据库的接口，它实现了 Python 数据库 API 规范 V2.0，基于 MySQL C API 上建立的。

实验环境：

  • OS: CentOS release 6.3 x86_64
  • MySQL:
    • MySQL-client-5.6.22-1.linux_glibc2.5.x86_64
    • MySQL-server-5.6.22-1.linux_glibc2.5.x86_64
    • MySQL-devel-5.6.26-1.linux_glibc2.5.x86_64(mysql_config)
    • MySQL-shared-5.6.26-1.linux_glibc2.5.x86_64(libmysqlclient.so.18)

实验步骤：

第一步： 下载MySQLdb安装包
https://pypi.python.org/pypi/MySQL-python/1.2.5
注：安装正确的版本，否则会安装不成功（MySQL-3.23 through 5.5 and Python-2.4 through 2.7 are currently supported. ）

第二步： 运行以下命令，安装MySQLdb

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
[root@mydb1 MySQL-python-1.2.5]# python setup.py build
running build
running build_py
copying MySQLdb/release.py -> build/lib.linux-x86_64-2.6/MySQLdb
running build_ext
building '_mysql' extension
gcc -pthread -fno-strict-aliasing -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -Dversion_info=(1,2,5,'final',1) -D__version__=1.2.5 -I/usr/include/mysql -I/usr/include/python2.6 -c _mysql.c -o build/temp.linux-x86_64-2.6/_mysql.o -g -fstack-protector -m64 -fPIC -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing
In file included from _mysql.c:44:
/usr/include/mysql/my_config.h:442:1: warning: "HAVE_WCSCOLL" redefined
In file included from /usr/include/python2.6/pyconfig.h:6,
                 from /usr/include/python2.6/Python.h:8,
                 from _mysql.c:29:
/usr/include/python2.6/pyconfig-64.h:808:1: warning: this is the location of the previous definition
gcc -pthread -shared build/temp.linux-x86_64-2.6/_mysql.o -L/usr/lib64 -L/usr/lib64 -lmysqlclient -lpthread -lm -lrt -ldl -lpython2.6 -o build/lib.linux-x86_64-2.6/_mysql.so
[root@mydb1 MySQL-python-1.2.5]# python setup.py install
running install
running bdist_egg
running egg_info
writing MySQL_python.egg-info/PKG-INFO
writing top-level names to MySQL_python.egg-info/top_level.txt
writing dependency_links to MySQL_python.egg-info/dependency_links.txt
reading manifest file 'MySQL_python.egg-info/SOURCES.txt'
reading manifest template 'MANIFEST.in'
writing manifest file 'MySQL_python.egg-info/SOURCES.txt'
installing library code to build/bdist.linux-x86_64/egg
running install_lib
running build_py
copying MySQLdb/release.py -> build/lib.linux-x86_64-2.6/MySQLdb
running build_ext
creating build/bdist.linux-x86_64
creating build/bdist.linux-x86_64/egg
creating build/bdist.linux-x86_64/egg/MySQLdb
copying build/lib.linux-x86_64-2.6/MySQLdb/converters.py -> build/bdist.linux-x86_64/egg/MySQLdb
creating build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/REFRESH.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/CLIENT.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/ER.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/__init__.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/FIELD_TYPE.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/CR.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/FLAG.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/connections.py -> build/bdist.linux-x86_64/egg/MySQLdb
copying build/lib.linux-x86_64-2.6/MySQLdb/__init__.py -> build/bdist.linux-x86_64/egg/MySQLdb
copying build/lib.linux-x86_64-2.6/MySQLdb/release.py -> build/bdist.linux-x86_64/egg/MySQLdb
copying build/lib.linux-x86_64-2.6/MySQLdb/cursors.py -> build/bdist.linux-x86_64/egg/MySQLdb
copying build/lib.linux-x86_64-2.6/MySQLdb/times.py -> build/bdist.linux-x86_64/egg/MySQLdb
copying build/lib.linux-x86_64-2.6/_mysql.so -> build/bdist.linux-x86_64/egg
copying build/lib.linux-x86_64-2.6/_mysql_exceptions.py -> build/bdist.linux-x86_64/egg
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/converters.py to converters.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/REFRESH.py to REFRESH.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/CLIENT.py to CLIENT.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/ER.py to ER.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/__init__.py to __init__.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/FIELD_TYPE.py to FIELD_TYPE.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/CR.py to CR.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/FLAG.py to FLAG.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/connections.py to connections.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/__init__.py to __init__.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/release.py to release.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/cursors.py to cursors.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/times.py to times.pyc
byte-compiling build/bdist.linux-x86_64/egg/_mysql_exceptions.py to _mysql_exceptions.pyc
creating stub loader for _mysql.so
byte-compiling build/bdist.linux-x86_64/egg/_mysql.py to _mysql.pyc
creating build/bdist.linux-x86_64/egg/EGG-INFO
copying MySQL_python.egg-info/PKG-INFO -> build/bdist.linux-x86_64/egg/EGG-INFO
copying MySQL_python.egg-info/SOURCES.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
copying MySQL_python.egg-info/dependency_links.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
copying MySQL_python.egg-info/top_level.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
writing build/bdist.linux-x86_64/egg/EGG-INFO/native_libs.txt
zip_safe flag not set; analyzing archive contents...
creating dist
creating 'dist/MySQL_python-1.2.5-py2.6-linux-x86_64.egg' and adding 'build/bdist.linux-x86_64/egg' to it
removing 'build/bdist.linux-x86_64/egg' (and everything under it)
Processing MySQL_python-1.2.5-py2.6-linux-x86_64.egg
creating /usr/lib64/python2.6/site-packages/MySQL_python-1.2.5-py2.6-linux-x86_64.egg
Extracting MySQL_python-1.2.5-py2.6-linux-x86_64.egg to /usr/lib64/python2.6/site-packages
Adding MySQL-python 1.2.5 to easy-install.pth file
Installed /usr/lib64/python2.6/site-packages/MySQL_python-1.2.5-py2.6-linux-x86_64.egg
Processing dependencies for MySQL-python==1.2.5
Finished processing dependencies for MySQL-python==1.2.5

实验验证：

1
2
3
4
5
6
7
8
9
10
11
[root@mydb1 python]# more test.py
import MySQLdb
db = MySQLdb.connect("localhost","root","pass","test")
cursor = db.cursor()
cursor.execute("SELECT VERSION()")
data = cursor.fetchone()
print "Database version: %s " % data
db.close()
[root@mydb1 python]# python test.py
Database version: 5.6.22

实验问题，及解决：

问题一

1
2
3
4
5
6
7
8
9
10
                 from _mysql.c:29:
/usr/include/python2.6/pyconfig-64.h:808:1: warning: this is the location of the previous definition
gcc -pthread -shared build/temp.linux-x86_64-2.6/_mysql.o -L/usr/lib64 -L/usr/lib64 -lmysqlclient -lpthread -lm -lrt -ldl -lpython2.6 -o build/lib.linux-x86_64-2.6/_mysql.so
/usr/bin/ld: cannot find -lmysqlclient
collect2: ld returned 1 exit status
error: command 'gcc' failed with exit status 1
#安装
MySQL-devel-5.6.26-1.linux_glibc2.5.x86_64(mysql_config)
MySQL-shared-5.6.26-1.linux_glibc2.5.x86_64(libmysqlclient.so.18)

安装 MySQLdb

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#安装Mac 安装包
BINBIWAN-M-H7HV:hadoop binbiwan$ /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install
)"
BINBIWAN-M-H7HV:hadoop binbiwan$ brew install mysql
BINBIWAN-M-H7HV:hadoop binbiwan$ export PATH=$PATH:/usr/local/Cellar/mysql/5.7.22/bin/
(venv) BINBIWAN-M-H7HV:DB binbiwan$ pip install MySQL-python
Collecting MySQL-python
  Using cached https://files.pythonhosted.org/packages/a5/e9/51b544da85a36a68debe7a7091f068d802fc515a3a202652828c73453cad/MySQL-python-1.2.5.zip
Building wheels for collected packages: MySQL-python
  Running setup.py bdist_wheel for MySQL-python ... done
  Stored in directory: /Users/binbiwan/Library/Caches/pip/wheels/07/d2/5f/314860e4cb53a44bf0ee0d051d4b34465e4b4fbe9de6d42f42
Successfully built MySQL-python
Installing collected packages: MySQL-python
Successfully installed MySQL-python-1.2.5

MySQLDB操作

连接MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect(host="10.224.xx.xx", user="test",
                     passwd="xxxx", db="test", port=3306, charset="utf8");
# 使用cursor()方法获取操作游标
cur = db.cursor()
# 使用execute方法执行SQL语句
cur.execute("select version()")
# 使用 fetchone() 方法获取一条数据
data = cur.fetchone()
print("Database version : %s" % data)
# 关闭数据库连接
db.close()

创建Table

1
2
3
4
5
6
7
8
9
10
11
12
# 如果数据表已经存在使用 execute() 方法删除表。
cur.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 创建数据表SQL语句
sql = '''CREATE TABLE EMPLOYEE(
         FIRST_NAME CHAR(20) NOT NULL,
         LAST_NAME CHAR(20),
         AGE INT,
         SEX CHAR(1),
         INCOME FLOAT)'''
cur.execute(sql)

Insert data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
sql = '''insert into EMPLOYEE(first_name, last_name, age, sex, income)
                    values('Python', 'King', 1, 'M', 1.1);'''
try:
    for i in range(10000):
        cur.execute(sql)
    # 提交到数据库执行
    db.commit()
except Exception, e:
    # Rollback in case there is any error
    # 显示颜色格式：\033[显示方式;字体色;背景色m......[\033[0m]
    print("\033[1;31;40m insert data failed \033[0m")
    print("\033[1;31;40m %s \033[0m" % e)
    db.rollback()
else:
    print("\033[1;32;40m insert data successfully \033[0m")

Select data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
sql = "select * from EMPLOYEE"
try:
    cur.execute(sql)
    data = cur.fetchall()
    for row in data:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        print("fname=%s, lname=%s, age=%s, sex=%s, income=%s" % \
              (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data!")

Update data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
sql = '''update EMPLOYEE set age = 2 where income > 1.2'''
try:
    cur.execute(sql)
    # 提交到数据库执行
    db.commit()
except Exception, e:
    # Rollback in case there is any error
    # 显示颜色格式：\033[显示方式;字体色;背景色m......[\033[0m]
    print("\033[1;31;40m update data failed \033[0m")
    print("\033[1;31;40m %s \033[0m" % e)
    db.rollback()
else:
    print("\033[1;32;40m update data successfully \033[0m")

delete data

1
2
3
4
5
6
7
8
9
10
11
12
try:
    cur.execute(sql)
    # 提交到数据库执行
    db.commit()
except Exception, e:
    # Rollback in case there is any error
    # 显示颜色格式：\033[显示方式;字体色;背景色m......[\033[0m]
    print("\033[1;31;40m DELETE data failed \033[0m")
    print("\033[1;31;40m %s \033[0m" % e)
    db.rollback()
else:
    print("\033[1;32;40m DELETE data successfully \033[0m")