python3数据库

关系数据库和非关系数据库

1).什么是关系数据库

关系型数据库，是指采用了关系模型来组织数据的数据库，简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。象银行系统会大量的用关系数据库.比如大家经常用的MySQL就是典型的关系数据库.

优点：

容易理解：二维表结构是非常贴近逻辑世界的一个概念，关系模型相对网状、层次等其他模型来说更容易理解

使用方便：通用的SQL语言使得操作关系型数据库非常方便

易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率

2).什么是非关系数据库

关系数据库虽然很好，但是随着互联网大规模的爆发，弱点也越来越明显，比如事务的一致性，多表联查,高并发等等瓶颈很明显。

于是NoSQL一词横空出世，以键值对存储，且结构不固定，每一个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，这样就不会局限于固定的结构，可以减少一些时间和空间的开销。比如MongoDb就是典型的NoSQL型数据库(键值对大家想到了什么，对json格式).

DB-API，Python DB-API为开发人员提供了数据库应用编程接口，也就是说使用它连接各数据库后，就可以用相同的方式操作各数据库。

Python 3 对MySQL的基本操作，及MySQL的增删改查等常见命令

作者：韭菜学院(youxuancaijing)

1. Mysql的常见命令

显示当前数据库服务器中的数据库列表：

mysql> SHOW DATABASES;

显示当前数据库的数据表：

mysql> SHOW Tables;

显示表结构：

mysql> describe 表名称;

创建数据库:

CREATE DATABASE 数据库名;

例如： create database mydb;

删除数据库：

mysql> DROP DATABASE 库名;

创建数据表 ：

mysql> USE 库名;

mysql> CREATE TABLE 表名 (字段名 VARCHAR(20), 字段名 CHAR(1));

例如：

create table user(

id int(5) NOT NULL auto_increment,

name varchar(20),

passwd varchar(20));

删除数据表:

mysql> DROP TABLE 表名;

往表中插入记录：

mysql> INSERT INTO 表名 VALUES ("jack","123456");

更新表中数据：

mysql-> UPDATE 表名 SET 字段名1='a',字段名2='b' WHERE 字段名3='c';

将表中记录清空：

mysql> DELETE FROM 表名 WHERE 字段名3='c';

查询数据：

mysql> SELECT 字段名1,字段名2 FROM 表名 WHERE 字段名3='c';

2. 用Python对Mysql的增删改查

安装pymysql模块：pip install PyMySQL

你也可以用带界面的工具进行增删改查。

实际项目中，可以对相关函数做封装

前言

恭喜你，非常明智的选择了Django作为你项目开发的基础框架（手动滑稽）！

1.1.Django项目连接mysql数据库

Django项目要操作数据库，首先要和数据库建立连接，才能让程序中的数据和数据库关联起来进行增删改查操作。

Django项目默认使用mysqldb模块进行和mysql数据库之间的交互操作，但是mysqldb模块对于python3.4以上的版本支持还不够完善，所以我们要使用替代方案。

通过pymysql模块完成和数据库之间的交互过程

Django连接mysql数据库的操作，是通过根模块的配置实现的，在项目根模块的配置文件settings.py中，我们可以查询到如下DATABASES的配置信息：

settings.py配置文件

ENGINE:用于特定的数据库引擎的配置，一般如下几种：

django.db.backends.sqlite3django.db.backends.postgresqldjango.db.backends.mysqldjango.db.backends.oracle

其余的参数分别是：

NAME:要连接的数据库名称的配置；

USER:配置连接数据库的用户账号；

PASSWORD:配置连接数据库的登录密码；

HOST:配置数据库所在的主机IP地址；

PORT:配置连接数据库的端口号；

CHARSET:配置连接数据库交互数据编码格式。

如图：

修改DATABASES配置

如此，Django和数据库之间的连接关系就建立了。

1.2.定义创建模型

在Django项目中定义模型数据，其实就是定义class类型，通过类型创建的对象来封装管理数据，一定要在这里明确关联和对应关系。

定义创建模型

有了对应关系之后，我们要创建的模型对象的属性必须和数据库中的字段类型对应起来：

对应关系

每个字段定义时，都会有自己的一些特殊的选项指定：

主键和唯一约束等

在大部分项目中，还会涉及到多表关联操作：

多表关联操作

根据创建的个人博客，创建用户类型和文章类型如下：

mysite/myblog/views.py

创建文章类型和作者类型

2.数据库同步操作

创建好我们需要的模型类之后，需要将创建好的类型添加到数据库中并同时建立关联关系，根据模型类自动生成对应数据库引擎的sql语句。在settings.py中，连接数据库信息中配置了数据库引擎，其实就是已经告诉Django我们使用的是哪个数据库了，Django会根据指定的数据库自动生成sql语句。

python manage.py makemigrations

自动创建指定数据库的sql语句

查看生成的sql语句

通过命令自动生成sql语句之后是存储在文件中的，我们可以通过命令的方式直接查看生成的sql语句：

例：python manage.py sqlmigrate myblog 0001

自动同步到数据库

既然Django可以自动生成sql语句，当然可以自动同步到数据库中，并不需要开发人员再去手工创建各种数据表了：

例：python manage.py migrate

同步数据库命令

此时，再查看你的数据库，好好享受Django框架带来的便捷吧！

缓存+数据库Session

数据库用于做持久化，缓存用于提高效率

配置settings.py

SESSION_ENGINE = 'django.contrib.sessions.backends.cached_db' # 引擎

SESSION_CACHE_ALIAS = 'default' # 使用的缓存别名（默认内存缓存，也可以是memcache），此处别名依赖缓存的设置

SESSION_COOKIE_NAME ＝ "sessionid" # Session的cookie保存在浏览器上时的key，即：sessionid＝随机字符串

SESSION_COOKIE_PATH ＝ "/" # Session的cookie保存的路径

SESSION_COOKIE_DOMAIN = None # Session的cookie保存的域名

SESSION_COOKIE_SECURE = False # 是否Https传输cookie

SESSION_COOKIE_HTTPONLY = True # 是否Session的cookie只支持http传输

SESSION_COOKIE_AGE = 1209600 # Session的cookie失效日期（2周）

SESSION_EXPIRE_AT_BROWSER_CLOSE = False # 是否关闭浏览器使得Session过期

SESSION_SAVE_EVERY_REQUEST = False # 是否每次请求都保存Session，默认修改之后才保存

cookies加密

配置settings.py

SESSION_ENGINE = 'django.contrib.sessions.backends.cached_db' # 引擎

这一篇教程，我们一起来了解一些数据库相关的知识。

这里我们所接触的是关系型数据库。

当前主流的关系型数据库有Oracle、DB2、PostgreSQL、Microsoft SQL Server、Microsoft Access、MySQL。

那么，什么是关系型数据库？

关系型数据库，是指采用了关系模型来组织数据的数据库。关系模型就是指二维表格模型,因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。

这句话不好理解，看起来有些晕。

实际上，你可以把关系型数据库想象成一个Excel工作簿，工作簿的名称就相当于数据库的名称，工作簿中包含的工作表就是二维数据表。同一个工作簿中的工作表之间存在联系，从而形成了工作簿的数据组织。

除了要知道这些内容，我们还要了解一些相关的概念：

关系：可以理解为一张二维数据表，关系名就是数据表的名称。例如班级数据库中的成绩表。

元组：可以理解为二维数据表中的一行，它是数据库中的一条记录。例如成绩表中某一名学生的成绩记录。

属性：可以理解为二维数据表中的一列，它是数据库中的一个字段。例如成绩表中的学号。

域：是指属性的取值范围，也就是数据库中某一列的取值限制。例如某一成绩要在0~100之间。

关键字：是指一组可以唯一标识元组的属性。它是数据库中的主键，主键可以是一个或多个。例如学号是唯一的。

关系模式：是指对关系的描述，也就是数据库中的表结构。格式为：关系名（属性1，属性2，…）。例如：成绩表（学号，姓名，语文，数学）

了解了关系型数据库的一些基本概念，接下来我们来看如何使用关系型数据库。

作为一篇入门教程，我们从一个相对快捷、简单的数据库SQLite来入手。

Python官方文档中对SQLite的介绍如下：

SQLite是一个C语言库，它提供了一个轻量级的基于磁盘的数据库，不需要单独的服务器进程，并且允许使用SQL查询语言的非标准变体来访问数据库。一些应用程序可以使用SQLite进行内部数据存储。还可以使用SQLite对应用程序进行原型化，然后将代码移植到更大的数据库，如PostgreSQL或Oracle。

这个数据库不用单独安装，因为Python3中已经包含这个数据库引擎，并且在Python标准库中包含了对这个数据库进行操作的PySqlite模块（标准库中模块名为sqlite3），所以使用起来非常方便。

对于这个数据库的基本操作，在本篇教程中主要涉及以下内容：

创建/连接数据库

打开游标

创建表结构

操作数据（增、删、改、查）

提交事务

关闭游标链接

一、创建/连接数据库

不管是使用一个已有的数据库（.db文件），还是创建一个新的数据库，我们都需要和数据库进行连接。

然后通过连接对象进行后续操作。

这里我们可以使用connect()函数。

这个函数能够创建数据库连接对象，并且当数据库不存在时，创建新的数据库。

示例代码：（创建数据库student）

import sqlite3

conn = sqlite3.connect('student.db')

二、创建游标

当完成一个新数据库的创建，接下来就是在数据库中添加新的数据表以及表中的数据。

但是，创建表以及对表中数据的操作，我们都需要执行相应的SQL（Structured Query Language：结构化查询语言）语句。

而执行SQL语句，我们需要先打开游标，通过游标执行SQL语句。

示例代码：

cur = conn.cursor()

三、表结构

打开游标之后，我们就可以通过游标的execute()方法执行SQL（Structured Query Language：结构化查询语言）语句了。

这里我们创建成绩表“score”。

如果已存在同名数据表，会无法完成新数据表的创建，所以在创建之前，我们需要先删除已有的数据表，再创建新的数据表。

删除数据表的SQL语句为：DROP TABLE IF EXISTS 表名

创建数据表的SQL语句为：

CREATE TABLE 表名(

字段名 数据类型 PRIMARY KEY[主键，一个或多个字段],

字段名 数据类型 约束[可选],

……

字段名 数据类型

)

创建数据表时，我们需要定义表名，然后定义表中（括号中）的字段名、数据类型以及相关的约束。

关于数据类型，SQLite具有以下五种数据类型：

NULL：空值。

INTEGER：带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。

REAL：浮点数，存储为8-byte IEEE浮点数。

TEXT：字符串文本。

BLOB：二进制对象。

另外还要注意，被指定为主键的列，列值不可有重复。

除了数据类型，我们还可以给字段值添加约束，例如不能为空值、数值必须在某一区间等。

约束的类型包含以下几种：

PRIMARY KEY：主键约束，唯一标识数据库表中的每条记录。

NOT NULL：非空约束，确保某列不能有 NULL 值。

DEFAULT：默认约束，当某列没有指定值时，为该列提供默认值。

UNIQUE：唯一约束，确保某列中所有的值互不相同。

CHECK：检查约束，确保某列中所有的值满足指定的条件。

那么，参照着上方的SQL语句知识，我们将删除表以及创建表结构的语句添加到代码中。

示例代码：（删除/创建表score）

cur.execute('DROP TABLE IF EXISTS score')

cur.execute('''

CREATE TABLE score(

StuId INTEGER PRIMARY KEY NOT NULL,

StuName TEXT NOT NULL,

ChineseScore REAL DEFAULT 0 CHECK(ChineseScore>=0 AND ChineseScore<=100),

MathScore REAL DEFAULT 0 CHECK(ChineseScore>=0 AND ChineseScore<=100)

)

''')

上方代码中，表结构中共4个字段：

StuId：学号，整数，主键，非空字段

StuName：姓名，文本，非空字段

ChineseScore：语文成绩，浮点数，默认值为0，值必须大于等于0并且小于等于100

MathScore：数学成绩，浮点数，默认值为0，值必须大于等于0并且小于等于100

四、添加数据

通过之前的步骤，完成了表的创建，但这时还只是一个空表。

我们需要使用这个表存储数据，就要把数据添加到表中。

添加数据的SQL语句为：INSERT INTO 表名 [(列1，列2,…)] VALUES (值1，值2,…)

如果添加一行数据时，不指定仅为某些列添加数据，可以省略指定列的部分。

例如，为成绩表score添加一行数据：’INSERT INTO score VALUES(20712420205,”小楼”,100,100)’

注意：SQL语句中的值如果是字符串需要加上双引号。

接下来，我们将一个文本文档“score.txt”中的数据逐行读取，并添加到为这个数据表中。【点此下载】

文档的内容如下：

‘20712420001’,’吴陈’,92,93

‘20712420002’,’刘捷’,81,97

‘20712420003’,’万刚’,82,65

（省略部分内容）

如上所示，文档中每一行数据都是逗号分隔，学号和姓名带有单引号，语文成绩和数学成绩均为数字。

根据表中相应字段的数据类型，很明显学号带有引号，不符合INTEGER类型。

所以，我们需要在将每一行数据插入到数据表之前，先进行数据的修改，去除字段值两侧的单引号。

示例代码：

query = 'INSERT INTO score VALUES(?,?,?,?)' # 定义SQL语句

with open(r'C:\Users\Administrator\Desktop\score.txt') as file: # 打开文件

for line in file: # 读取文件每一行

values = [i.strip("'") for i in line.split(',')] # 以逗号为分隔符，将每一行内容变为值,去除单引号后存入列表。

conn.execute(query, values) # 将列表中的值代入SQL语句，并执行SQL语句。

上方代码中，定义SQL语句时使用了字段标记“?”，类似于格式化字符串时字符串中的“%”。

当我们使用execute()方法时，可以通过第2个可选参数parameters，将需要添加的值以列表的形式（代码中的values）传入。

五、提交事务

但是，当我们运行写好的代码，会发现数据表仍然是空表。

这是因为，我们只做了数据的修改，但是没有提交。

所以，我们还需要加上一句提交事务的代码。

示例代码：

connmit()

六、关闭游标与连接

当我们不再使用游标游标和连接，我们要确保它们正常的关闭。

示例代码：

cur.close()

conn.close()

不过，如果程序发生异常，怎么确保它们被关闭了呢？

可以通过try:…except:…finally:…的处理，将关闭语句写在finally的语句块中。

示例代码：（完整代码）

import sqlite3

conn = sqlite3.connect('student.db')

try:

cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS score')

cur.execute('''

CREATE TABLE score(

StuId INTEGER PRIMARY KEY NOT NULL,

StuName TEXT NOT NULL,

ChineseScore REAL DEFAULT 0 CHECK(ChineseScore>=0 AND ChineseScore<=100),

MathScore REAL DEFAULT 0 CHECK(ChineseScore>=0 AND ChineseScore<=100)

)

''')

query = 'INSERT INTO score VALUES(?,?,?,?)' # 定义SQL语句

with open(r'C:\Users\Administrator\Desktop\score.txt') as file: # 打开文件

for line in file: # 读取文件每一行

values = [i.strip("'") for i in line.split(',')] # 以逗号为分隔符，将每一行内容变为值,去除单引号后存入列表。

conn.execute(query, values) # 将列表中的值代入SQL语句，并执行SQL语句。

connmit()

except:

raise

finally:

cur.close()

conn.close()

以上是本篇教程的内容，在下一篇教程中，我们来一起学习更多的数据库操作。

本节知识点：

1、关系型数据库简介；

2、SQLite数据库；

3、创建数据库与数据表；

4、添加数据到数据表。

本节英文单词与中文释义：

1、connect：连接

2、cursor：指针/游标

3、commit：提交

4、exist：存在

5、drop：放弃

6、table：表

7、create：创建

8、primary：主要的

9、unique：唯一的

10、check：检查

11、insert into：插入 到

12、score：分数

前言

最近学了一下sql，因为做数据分析不会sql真不行。

平时学的都是Python，所以如果要用pandas做数据分析，数据除了导入excel和csv文件，

应该还要会从数据库中导入数据到Python中，于是我进行了以下的学习和探索。

环境

Python 3.X

IDE : juyter notebook

安装必要的模块

进入：

http://lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient

找到适合自己的版本

下载到本地（我的下载到e：盘根目录）

然后打开cmd如下图安装

这里推荐本地安装

使用Python连接数据库

import MySQLdb

如果导入模块没报错，恭喜你，安装模块成功了！

conn = MySQLdb.connect( host = '127.0.0.1',#本地地址 user = 'root',#一般默认用户名 passwd = '********',#本地数据库登录密码 db = 'test',#数据库名称 port = 3306,#安装mysql默认的端口号 charset = 'utf8'#设置数据库统一编码)

通过connect方法连接本地mysql数据库，这里要注意你要修改的或许是登录密码和数据库名称

创建游标

首先，我们要创建一个游标

cursor = conn.cursor()

什么是游标呢？

游标就相当于一个缓冲区，存放暂时的结果（这是我的理解）

就像去超市买买买，你可能要推一个手推车，这里的手推车就像游标，推着手推车去对应的货架完成挑选操作，而手推车最终会在出超市时候清空

观察数据库

我本地的数据库test中有个tdb_goods的表

表的内容如上图所示

有商品的id，名称，种类id，品牌id，是否在售和是否下架几个字段

一共有23条记录

获取数据

我们使用select语句可以获取数据

cursor.execute('SELECT * FROM `tdb_goods`;')data = cursor.fetchone()#取一条数据print(data)

首先我们用游标的execute方法执行一句sql语句获取tdb_goods中的所有数据

然后使用游标的fetchone方法取出其中一条记录并且展现出来

如上图，我们取出的数据是元组形式

当然，我们应该养成打开后关闭的好习惯，所以，在操作结束，应该断开与数据库的连接

conn.close()

异常处理

当我们在操作时，希望和在mysql数据库中一样，异常时会提示异常信息

那么我们就要加入异常处理模块

try: conn = MySQLdb.connect( host = '127.0.0.2222', user = 'root', passwd = '********', db = 'test', port = 3306, charset = 'utf8' )except MySQLdb.Error as e: print('Error:%s' % e)

这里异常时，就将异常的信息告知我们了

我们将以上代码整理一下：

import MySQLdbtry: conn = MySQLdb.connect( host = '127.0.0.1', user = 'root', passwd = '********', db = 'test', port = 3306, charset = 'utf8' ) cursor = conn.cursor() cursor.execute('SELECT * FROM `tdb_goods`;') data = cursor.fetchone() print(data) conn.close()except MySQLdb.Error as e: print('Error:%s' % e)

当我们将fetchone改成fetchall时候，就是取出所有信息了

使用面向对象编程

如果你熟悉面向对象编程或者看过我写的面向对象编程入门系列（小姐姐系列）

传送门：

Python面向对象编程从零开始（1）——从没对象到有对象

Python面向对象编程从零开始（2）—— 与对象相互了解

Python面向对象编程从零开始（3）—— 小姐姐请客上篇

Python面向对象编程从零开始（4）—— 小姐姐请客下篇

Python面向对象编程从零开始（5）—— 小姐姐要买房

如果你还没接触过面向对象编程，看完以上5篇至少对接下来的内容理解上不存在问题

import MySQLdbclass Mysql(object): def __init__(self): self.connect() def connect(self): try: self.conn = MySQLdb.connect( host = '127.0.0.1', user = 'root', passwd = '********', db = 'test', port = 3306, charset = 'utf8' ) except MySQLdb.Error as e: print('Error:%s' % e) def close_conn(self): try: if self.conn: self.conn.close() except MySQLdb.Error as e: print('Error:%s' % e) def get_a(self): sql = 'select * from `tdb_goods` where `cate_id` = %s;' cursor = self.conn.cursor() cursor.execute(sql,('1',)) data = cursor.fetchone() print(data) cursor.close() self.close_conn() def main(): object = Mysql() object.get_a() if __name__ == '__main__': main()

注：这里定义了一个类叫Mysql，然后用类创建一个对象名为object，在创建对象的时候，类的初始化时调用连接数据库函数

创建了一个叫object的对象，使用对象的get_a方法从数据库tdb_goods中选出cate_id=1的一条记录，在get_a函数最后位置断开数据库连接

结果如下：

插入记录到数据库

import MySQLdbclass Mysql(object): def __init__(self): self.connect() def connect(self): try: self.conn = MySQLdb.connect( host = '127.0.0.1', user = 'root', passwd = '********', db = 'test', port = 3306, charset = 'utf8' ) except MySQLdb.Error as e: print('Error:%s' % e) def close_conn(self): try: if self.conn: self.conn.close() except MySQLdb.Error as e: print('Error:%s' % e) def get_a(self): sql = 'select * from `tdb_goods` where `cate_id` = %s;' cursor = self.conn.cursor() cursor.execute(sql,('1',)) data = cursor.fetchone() print(data) cursor.close() self.close_conn() def add_a(self): sql = "insert into `tdb_goods`(`goods_name`,`cate_id`,`brand_id`,`goods_price`,`is_show`,`is_saleoff`) value (%s,%s,%s,%s,%s,%s);" cursor = self.conn.cursor() cursor.execute(sql,('伟哥牌notebook','8','1','66666','1','0')) cursor.close() self.close_conn() def main(): object = Mysql() object.add_a() if __name__ == '__main__': main()

注：这里，将一条记录插入了数据库，但是当我执行完这条语句（没报错）

刷新数据库并没有新增一条记录

划重点：在这里，有一个self.connmit()，这个叫提交，如果不写这句，就无法将所做修改保存的数据库中

加上这个之后（加在以下两句之间）：

可以看到，数据成功写入数据库了~

如果运行代码报错，很可能是你的数据库名和数据库登录密码没有修改~

文中如有错误和叙述不妥之处，望指正。

1.写在前边

上一次，我们介绍了Python的入门的文章，今天我们就来介绍下Python的数据库，GUI, CGI的编程，以及Python 2.x 与Python 3.x的区别。

2.连接数据库

Python 标准数据库接口为 Python DB-API，MySQLdb 是用于Python链接Mysql数据库的接口。使用的时候，必须安装MySQLdb 模块，否则会报出来模块找不到。

2.1 Linux下安装MySQLdb模块

使用包安装可以 wget http://sourceforge/projects/mysql-python/files/latest/download

$ tar xfz MySQL-python-1.2.3.tar.gz$ cd MySQL-python-1.2.3$whereis mysql_config mysql_config: /usr/bin/mysql_config$ vim site.cfg 修改mysql_config为mysql配置文件的路径 /usr/bin/mysql_config 还要修改 threadsafe = False$ python setup.py build$ sudo python setup.py install

2.2 mac下安装MySQLdb模块

使用easy_install pip命令安装pip

$ easy_install pip

$ pip install MySQL-Python

2.3 查询数据库

代码在GitHub上的源码地址：http://t/RQjcvfl

3. GUI编程

Python 提供了多个图形开发界面的库，其中Tkinter 是 Python 的标准 GUI 库。Python 使用 Tkinter 可以快速的创建 GUI 应用程序。

代码在GitHub上的源码地址：http://t/RQjcvfl

4. CGI编程

1）Python的CGIHTTPServer一般是与python一起安装的，使用如下命令既可以启动，为了便于组织目录，建议先建立一个目录，比如web，然后再运行下面的命令。

nohup python -m CGIHTTPServer 8088 &

2）静态文件代码地址：http://t/R8wMYHr

访问地址：http://localhost:8088/

3）提交给后端的地址是 http://localhost:8088/cgi-bin/c_area.sh

后端的shell脚本地址：http://t/R8wMmMn

5. Python 2.x 与 Python 3.x区别

5.1 两者不兼容

Python的3.0版本，常被称为Python 3000，或简称Py3k。相对于Python的早期版本，这是一个较大的升级。

为了不带入过多的累赘，Python 3.0在设计的时候没有考虑向下相容。

许多针对早期Python版本设计的程式都无法在Python 3.0上正常执行。

为了照顾现有程序，Python 2.6作为一个过渡版本，基本使用了Python 2.x的语法和库，同时考虑了向Python 3.0的迁移，允许使用部分Python 3.0的语法与函数。

5.2 一图胜千言

可以升级的自己的python版本，建议直接采用Python3，毕竟是趋势。使用 “2to3工具”也是不错的选择。

6. 写在最后

本文简单接受了Python的数据库链接，GUI, CUI的简单编程，最后简单介绍了Python2 与Python3的区别，欢迎大家交流。

许久没有开新的专题系列了，最近有了一些新的想法，准备完整的做一个项目，同时把过程做一个记录。

PyMySQL 安装

使用以下命令安装最新版的 PyMySQL：

$ pip install PyMySQL

Linux 系统：

$ wget $ python3 ez_setup.py

数据库连接

连接数据库前需确定已创建数据库，这里我们使用易百mysql教程中的yiibaidb示例数据库（下载地址：http://yiibai/downloads/yiibaidb.zip）。这里默认大家对MySQL基本操作已经了解，对MySQL不熟悉的朋友也建议先找教程入门学习一下。

yiibaidb数据库ER图如下：

以下实例链接 Mysql 的 yiibaidb数据库：

#!/usr/bin/python3 import pymysql# 打开数据库连接db = pymysql.connect("localhost","你的数据库用户名","你的密码","yiibaidb" )# 使用 cursor() 方法创建一个游标对象cursorcursor = db.cursor()# 使用 execute() 方法执行 SQL 查询cursor.execute("SELECT VERSION()")# 使用 fetchone() 方法获取单条数据.data = cursor.fetchone() print ("Database version : %s " % data)# 关闭数据库连接db.close()

执行以上脚本输出结果如下：

Database version : 5.7.21-log

创建数据库表

如果数据库连接存在我们可以使用execute()方法来为数据库创建表，如下所示创建表EMPLOYOR：

# 使用 execute() 方法执行 SQL，如果表存在则删除cursor.execute("DROP TABLE IF EXISTS EMPLOYOR")# 使用预处理语句创建表sql = """CREATE TABLE EMPLOYOR ( FIRST_NAME CHAR(20) NOT NULL,LAST_NAME CHAR(20), AGE INT,SEX CHAR(1),INCOME FLOAT )""" cursor.execute(sql)

数据库插入操作

以下实例使用执行 SQL INSERT 语句向表 EMPLOYOR 插入记录：

# SQL 插入语句sql = """INSERT INTO EMPLOYOR(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""try: # 执行sql语句 cursor.execute(sql) # 提交到数据库执行 dbmit()except: # 如果发生错误则回滚 db.rollback()

以上例子也可以写成如下形式：

# 使用cursor()方法获取操作游标 cursor = db.cursor()# SQL 插入语句sql = "INSERT INTO EMPLOYOR(FIRST_NAME, \LAST_NAME, AGE, SEX, INCOME) \VALUES ('%s', '%s', '%d', '%c', '%d' )" % \('Mac', 'Mohan', 20, 'M', 2000)try: # 执行sql语句 cursor.execute(sql) # 执行sql语句 dbmit()except: # 发生错误时回滚 db.rollback()

以下代码使用变量向SQL语句中传递参数:

......user_id = "XXX"password = "XXX"con.execute('insert into Login values("%s", "%s")' % \ (user_id, password))......

创建成功：

数据库查询操作

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。fetchone(): 该方法获取下一个查询结果集。结果集是一个对象fetchall(): 接收全部的返回结果行.rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

查询EMPLOYEES表中officeCode（办公室号）为1的所有数据：

sql="SELECT * FROM employees \ WHERE officeCode>'%s'" % (1)try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() for row in results: employeeNumber=row[0] lastName = row[1] firstName= row[2] extension= row[3] email= row[4] officeCode= row[5] reportsTo= row[6] jobTitle= row[7] print("employeeNumber=%d,lastName=%s,firstName=%s,extension=%s,email=%s,officeCode=%s,reportsTo=%d,jobTitle=%s" % \ (employeeNumber, lastName, firstName, extension, email, officeCode, reportsTo, jobTitle))except: print ("Error: unable to fetch data")

以上脚本执行结果如下：

数据库更新操作

更新操作用于更新数据表的的数据，以下实例将 TESTDB表中的 SEX 字段全部修改为 'M'，AGE 字段递增1：

sql="UPDATE employees SET lastname = 'Hill',email = 'mary.hill@yiibai' WHERE employeeNumber = 1056"try: cursor.execute(sql) dbmit()except: db.rollback()

删除操作

删除操作用于删除数据表中的数据，以下实例演示了删除数据表 EMPLOYEE 中 AGE 大于 20 的所有数据：

sql = "DELETE FROM EMPLOYOR WHERE AGE > '%d'" % (20)try: # 执行SQL语句 cursor.execute(sql) # 提交修改 dbmit()except: # 发生错误时回滚 db.rollback()

执行事务

事务机制可以确保数据一致性。

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

Python DB API 2.0 的事务提供了两个方法 commit 或 rollback。

# SQL删除记录语句sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)try: # 执行SQL语句 cursor.execute(sql) # 向数据库提交 dbmit()except: # 发生错误时回滚 db.rollback()

对于支持事务的数据库， 在Python数据库编程中，当游标建立之时，就自动开始了一个隐形的数据库事务。

commit()方法游标的所有更新操作，rollback（）方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务。

错误处理

DB API中定义了一些数据库操作的错误及异常，下表列出了这些错误和异常:

异常

描述

Warning当有严重警告时触发，例如插入数据是被截断等等。必须是 StandardError 的子类。

Error警告以外所有其他错误类。必须是 StandardError 的子类。

InterfaceError当有数据库接口模块本身的错误（而不是数据库的错误）发生时触发。 必须是Error的子类。

DatabaseError和数据库有关的错误发生时触发。 必须是Error的子类。

DataError当有数据处理时的错误发生时触发，例如：除零错误，数据超范围等等。 必须是DatabaseError的子类。

OperationalError指非用户控制的，而是操作数据库时发生的错误。例如：连接意外断开、 数据库名未找到、事务处理失败、内存分配错误等等操作数据库是发生的错误。 必须是DatabaseError的子类。

IntegrityError完整性相关的错误，例如外键检查失败等。必须是DatabaseError子类。

InternalError数据库的内部错误，例如游标（cursor）失效了、事务同步失败等等。 必须是DatabaseError子类。

ProgrammingError程序错误，例如数据表（table）没找到或已存在、SQL语句语法错误、 参数数量错误等等。必须是DatabaseError的子类。

NotSupportedError不支持错误，指使用了数据库不支持的函数或API等。例如在连接对象上 使用.rollback()函数，然而数据库并不支持事务或者事务已关闭。 必须是DatabaseError的子类。

前言

最近学了一下sql，因为做数据分析不会sql真不行。

平时学的都是Python，所以如果要用pandas做数据分析，数据除了导入excel和csv文件，

应该还要会从数据库中导入数据到Python中，于是我进行了以下的学习和探索。

环境

Python 3.X

IDE : juyter notebook

安装必要的模块

进入：

http://lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient

找到适合自己的版本

下载到本地（我的下载到e：盘根目录）

然后打开cmd如下图安装

这里推荐本地安装

使用Python连接数据库

import MySQLdb

如果导入模块没报错，恭喜你，安装模块成功了！

conn = MySQLdb.connect( host = '127.0.0.1',#本地地址 user = 'root',#一般默认用户名 passwd = '********',#本地数据库登录密码 db = 'test',#数据库名称 port = 3306,#安装mysql默认的端口号 charset = 'utf8'#设置数据库统一编码)

通过connect方法连接本地mysql数据库，这里要注意你要修改的或许是登录密码和数据库名称

创建游标

首先，我们要创建一个游标

cursor = conn.cursor()

什么是游标呢？

游标就相当于一个缓冲区，存放暂时的结果（这是我的理解）

就像去超市买买买，你可能要推一个手推车，这里的手推车就像游标，推着手推车去对应的货架完成挑选操作，而手推车最终会在出超市时候清空

观察数据库

我本地的数据库test中有个tdb_goods的表

表的内容如上图所示

有商品的id，名称，种类id，品牌id，是否在售和是否下架几个字段

一共有23条记录

获取数据

我们使用select语句可以获取数据

cursor.execute('SELECT * FROM `tdb_goods`;')data = cursor.fetchone()#取一条数据print(data)

首先我们用游标的execute方法执行一句sql语句获取tdb_goods中的所有数据

然后使用游标的fetchone方法取出其中一条记录并且展现出来

如上图，我们取出的数据是元组形式

当然，我们应该养成打开后关闭的好习惯，所以，在操作结束，应该断开与数据库的连接

conn.close()

异常处理

当我们在操作时，希望和在mysql数据库中一样，异常时会提示异常信息

那么我们就要加入异常处理模块

try: conn = MySQLdb.connect( host = '127.0.0.2222', user = 'root', passwd = '********', db = 'test', port = 3306, charset = 'utf8' )except MySQLdb.Error as e: print('Error:%s' % e)

这里异常时，就将异常的信息告知我们了

我们将以上代码整理一下：

import MySQLdbtry: conn = MySQLdb.connect( host = '127.0.0.1', user = 'root', passwd = '********', db = 'test', port = 3306, charset = 'utf8' ) cursor = conn.cursor() cursor.execute('SELECT * FROM `tdb_goods`;') data = cursor.fetchone() print(data) conn.close()except MySQLdb.Error as e: print('Error:%s' % e)

当我们将fetchone改成fetchall时候，就是取出所有信息了

使用面向对象编程

如果你熟悉面向对象编程或者看过我写的面向对象编程入门系列（小姐姐系列）

传送门：

Python面向对象编程从零开始（1）——从没对象到有对象

Python面向对象编程从零开始（2）—— 与对象相互了解

Python面向对象编程从零开始（3）—— 小姐姐请客上篇

Python面向对象编程从零开始（4）—— 小姐姐请客下篇

Python面向对象编程从零开始（5）—— 小姐姐要买房

如果你还没接触过面向对象编程，看完以上5篇至少对接下来的内容理解上不存在问题

import MySQLdbclass Mysql(object): def __init__(self): self.connect() def connect(self): try: self.conn = MySQLdb.connect( host = '127.0.0.1', user = 'root', passwd = '********', db = 'test', port = 3306, charset = 'utf8' ) except MySQLdb.Error as e: print('Error:%s' % e) def close_conn(self): try: if self.conn: self.conn.close() except MySQLdb.Error as e: print('Error:%s' % e) def get_a(self): sql = 'select * from `tdb_goods` where `cate_id` = %s;' cursor = self.conn.cursor() cursor.execute(sql,('1',)) data = cursor.fetchone() print(data) cursor.close() self.close_conn() def main(): object = Mysql() object.get_a() if __name__ == '__main__': main()

注：这里定义了一个类叫Mysql，然后用类创建一个对象名为object，在创建对象的时候，类的初始化时调用连接数据库函数

创建了一个叫object的对象，使用对象的get_a方法从数据库tdb_goods中选出cate_id=1的一条记录，在get_a函数最后位置断开数据库连接

结果如下：

插入记录到数据库

import MySQLdbclass Mysql(object): def __init__(self): self.connect() def connect(self): try: self.conn = MySQLdb.connect( host = '127.0.0.1', user = 'root', passwd = '********', db = 'test', port = 3306, charset = 'utf8' ) except MySQLdb.Error as e: print('Error:%s' % e) def close_conn(self): try: if self.conn: self.conn.close() except MySQLdb.Error as e: print('Error:%s' % e) def get_a(self): sql = 'select * from `tdb_goods` where `cate_id` = %s;' cursor = self.conn.cursor() cursor.execute(sql,('1',)) data = cursor.fetchone() print(data) cursor.close() self.close_conn() def add_a(self): sql = "insert into `tdb_goods`(`goods_name`,`cate_id`,`brand_id`,`goods_price`,`is_show`,`is_saleoff`) value (%s,%s,%s,%s,%s,%s);" cursor = self.conn.cursor() cursor.execute(sql,('伟哥牌notebook','8','1','66666','1','0')) cursor.close() self.close_conn() def main(): object = Mysql() object.add_a() if __name__ == '__main__': main()

注：这里，将一条记录插入了数据库，但是当我执行完这条语句（没报错）

刷新数据库并没有新增一条记录

划重点：在这里，有一个self.connmit()，这个叫提交，如果不写这句，就无法将所做修改保存的数据库中

加上这个之后（加在以下两句之间）：

可以看到，数据成功写入数据库了~

如果运行代码报错，很可能是你的数据库名和数据库登录密码没有修改~

文中如有错误和叙述不妥之处，望指正。