python与数据库操作

01 前言

Python链接数据库的方式有几种，但是原理都是一样的，总共可以分为两个步骤，第一步是与数据库建立链接，第二步执行sql查询语句，这篇将分别介绍如何与数据库链接以及如何进行sql语句查询。

02 与数据库进行链接

在与数据库进行链接时，主要用到两种方法，一种是pymysql.connect，另一种是create_engine。

pymysql.connectpymysql是python自带的一个库，使用前需要使用pip install pymysql安装这个库，安装完以后使用该库中的connect方法可以直接与数据库进行链接。

# 方法一: 使用pymsql.connect方法import pymysql# Connect to the databaseeng = pymysql.connect(host='localhost',user='user',password='passwd',db='db',charset='utf8')# user:用户名# password:密码# host:数据库地址/本机使用localhost# db:数据库名# charset:数据库编码# 连接sample# charset='utf8'是解决中文乱码eng=pymysql.connect(host="118.190.xxx.xxx",user="zhangjian",password="ZhangJian",db="demo",charset='utf8')

这样就将python与数据库进行了链接，接下来执行sql查询语句就可以将数据库中的内容读取到python中。

create_enginecreate_engine是sqlarchemy包内的一个模块，而sqlarchemy是Python下的一款ORM框架，建立在数据库API之上，使用关系对象映射进行数据库操作，将对象转换成SQL，使用数据库API执行SQL并获取执行结果。

ORM是Object Relational Mapper ,是一种对象映射关系程序，比较难解释，大家有兴趣的自己去了解一下，这里只分享如何使用这个进行链接。

# 方法二: 使用create_engine方法from sqlarchemy import create_enginecreate_engine("mysql+pymysql://:@/[?]")# mysql:数据库类型# pymysql:驱动器类型# username:用户名# password:密码# host:数据库地址/本机使用localhost# dbname:数据库名# options:数据库编码格式如:charset=utf8# 连接sampleeng = create_engine("mysql+pymysql://zhangjian:ZhangJian@118.190.xxx.xxx:3306/demo?charset=utf8")

03 执行sql语句

# 方法一:使用pd.read_sql() 主要参数如下所示pd.read_sql(sql, #需要使用的sql语句或者数据表con, #sqlalchemy连接引擎名称index_col = None, #将被用作索引的名称columns = None#当sql参数使用的是表名称是,指定需要读入的列,使用list提供)# 方法二:使用pd.read_sql_query 主要参数如下所示pd.read_sql(sql, #完整的sql语句con, #sqlalchemy连接引擎名称index_col = None, #将被用作索引的名称columns = None#当sql参数使用的是表名称是,指定需要读入的列,使用list提供)# 方法三:使用pd.read_sql_table 主要参数如下所示pd.read_sql(table, #表名称con, #sqlalchemy连接引擎/或者连接名称index_col = None, #将被用作索引的名称columns = None#当sql参数使用的是表名称是,指定需要读入的列,使用list提供)# 从以上方法可看出,read_sql()方法已经打包了read_sql_table() 与 read_sql_query()的所有功能,推荐直接使用read_sql()方法

pd.read_sql()方法读取数据文件

import pandas as pd from sqlalchemy import create_engineeng = create_engine("mysql+pymysql://zhangjian:ZhangJian*2018@118.190.000.111:3306/demo?charset=gbk") data = pd.read_sql(sql = 'select * from orderitem limit 10',con=eng,index_col='SDate')data# 输入正确的数据库新信息后,read_sql方法返回的是我们熟悉的数据框结构,可以方便浏览数据,如需查看汇总信息,修改sql语句即可。

▲

（点击可查看大图）

# read_sql()方法sql参数使用表名称from sqlalchemy import create_engineimport pandas as pdeng = create_engine("mysql+pymysql://zhangjian:ZhangJian*2018@118.190.000.111:3306/demo?charset=gbk") data = pd.read_sql(sql = "category",con=eng)# 此方法会读取指定表中的全部数据，如果表数据量比较大,会造成读取数据慢,慎用。

# 修改改数据库密码后重新连接数据库# 如用户名,密码,数据库名称包含% @等特殊字符串报错如下所示:报错关键信息1045eng = create_engine("mysql+pymysql://账号:密码@118.190.000.111:3306/demo?charset=gbk") data = pd.read_sql(sql = 'select * from orderitem limit 10',con=eng)data

OperationalError Traceback (most recent call last)C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py in _wrap_pool_connect(self, fn, connection)2157try:-> 2158return fn()2159except dialect.dbapi.Error as e:

▲

（点击可查看大图）

# 用户名,密码,数据库名称包含特殊字符串报错解决方法# 方法二:使用pymysql.connect()方法建立连接import pymysqleng = pymysql.connect("118.190.000.111","zhangjian","zhangjiang*2018","demo" )data = pd.read_sql(sql = "select * from orderitem limit 10" ,con=eng)data

▲

（点击可查看大图）

# pymsql.connect连接,读入指定表名称,会报错,关键信息1064eng=pymysql.connect(host="118.190.000.111",user="zhagnjian",password="zhangjian*2018",db="demo" ,charset='utf8')data = pd.read_sql(sql = "category",con=eng)data

▲

（点击可查看大图）

使用connection.cursor()方法读取数据库文件

# 导入sql文件 使用官方文档案例方法#导入数据库模块import pymysql# 连接数据库eng = pymysql.connect("118.190.000.111","zhangjian","ZhangJian*2018","demo" )# 使用 cursor() 方法创建一个游标对象 cursorcursor = eng.cursor()# 编写sql语句sql = """select * from orderitem limit 10;"""# 使用 execute() 方法执行 SQL 查询cursor.execute(sql)# 使用 fetchall() 方法获取所有数据.data = cursor.fetchall()# 关闭数据库连接eng.close()# 返回元组data# 返回信息包括数据类型等数据列信息

▲

前言

恭喜你，非常明智的选择了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框架带来的便捷吧！

转载自百家号作者：编程是有趣的

大家好，今天我们就一起聊聊Python“数据库操作”之ODBC。什么是ODBC呢？ODBC即Open DataBase Connection的缩写，意为开放数据库连接，是微软提出的一种数据访问的方法，只要数据库提供了ODBC驱动程序，应用程序就能以ODBC的方式访问数据库中的数据。

为了便于访问数据，WINDOWS系统提供了ODBC数据源管理工具，该工具用来设置数据源的名字DSN（DATA SOURCE NAME）。所谓DSN只不过是一个数据源的标志，设置它的目的是便于应用程序访问数据，也就是说，只要为某个数据库设置了相应的DSN，应用程序就不必理会该数据库存储的位置和驱动程序，可以按DSN直接访问数据库。

DSN有三种类型：用户DSN、系统DSN和文件DSN。用户DSN只对设置它的用户可见，而且只能在设置了该DSN的机器上使用；系统DSN对机器上的所用户都是可见的，包括NT服务；文件DSN将DSN的配置信息存在一个文件里，这样的文件就叫文件DSN。我们一般只需要考虑用户DSN即可。

在Windows中手动添加DSN是很简单，依次点击控制面板-管理工具-数据源，打开下图所示的界面，点击添加，按照提示一步步完成即可。

Python中用来操作ODBC的类库是pypyodbc，该库不是Python内置的，需要我们手动安装，也很简单，在命令行窗口下输入pip install pypyodbc即可，如下图所示，显示出这样的界面表明pypyodbc库已经安装成功。除了使用pypyodbc，大家也可以使用pyodbc，二者没什么区别，pypyodbc是pyodbc的纯python实现，平台移植性更好。

使用pypyodbc库之前，需要采用import语句导入，也很简单。

还记得数据库操作第一步要干什么吗？对了，是建立数据库连接，不过在ODBC里面提供了两种方法：一是直接与数据库连接，二是通过数据源（DSN）与数据库连接，pypyodbc提供了connect方法完成这一操作。

我们注意到connect方法有一个参数connectString（连接字符串），通过使用connectString，便可以连接到ODBC，得到一个Connection对象，之后的操作便和上一篇文章《Python“数据库操作”初窥》中写的一样了，所以重点就是学习如何根据自己的实际情况和需要书写connectString，官网有详细的操作手册 http://connectionstrings，建议大家好好看看，在此我列出几条常用的备查。

简单解释一下，Uid代表数据库的登录名称，Pwd表示密码，如果没有设置的话就都为空。不过，并不是写好上述语句，就能连接到ODBC，还需看你的Python环境是否支持上述驱动，如何看我们的Python环境支持哪些驱动呢，可以使用下面的代码。

另外，如果使用的是Access的mdb数据库，则还有更简单的方法，pypyodbc为我们提供了两个操作方法。

win_connect_mdb用于连接到mdb数据库；win_create_mdb用于创建mdb数据库，二者的参数都是mdb_path，即数据库所在的路径。

OK，今天的讲解就到这儿，别看篇幅不长，可知识点却多多了，大家一定要多多阅读，好好消化，感谢大家的持续关注，还有更多的Python编程大餐，敬请享用哦！

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

1).什么是关系数据库

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

优点：

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

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

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

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

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

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

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

前言

最近学了一下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()，这个叫提交，如果不写这句，就无法将所做修改保存的数据库中

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

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

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

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

在日常的程序序开发过程中，经常要跟关系数据库打交道。python异步连接数据库用aiomysql,笔者就在使用此库过程中的一些心得和技巧记录如下。

我们先看一下同步数据库连接方法，PyMySQL官方给的使用方法如下：

在日常使用过程序中，如果均采用这样的方法来操作数据库，代码的编写量很大，而且容易出错，现就异步程序数据库连接解决办法归纳如下，如果是同步程序，就可以依据异步程序相应做修改即可，思路是一模一样的：

创建连接池

我们需要创建一个全局的连接池，每个HTTP请求都可以从连接池中直接获取数据库连接。使用连接池的好处是不必频繁地打开和关闭数据库连接，而是能复用就尽量复用。缺省情况下将编码设置为utf8，自动提交事务：

import logging

import aiomysql #这是异步操作pymysql的库

DATABASES = { 'host':'127.0.0.1',

'port':3306,

'user':'root',

'password':'',

'db':'mydb',

'charset':'utf8mb4',

# 'cursorclass':pymysql.cursors.DictCursor }

async def create_pool(loop, **kw):

"""定义mysql全局连接池"""

logging.info('create database connection pool...')

global _mysql_pool

_mysql_pool = await aiomysql.create_pool(host=DATABASES['host'], port=DATABASES['port'], user=DATABASES['user'],

password=DATABASES['password'], db=DATABASES['db'], loop=loop,

charset=kw.get('charset', 'utf8'), autocommit=kw.get('autocommit', True),

maxsize=kw.get('maxsize', 10), minsize=kw.get('minsize', 1))

return _mysql_pool

封装增删改查

访问数据库需要创建数据库连接、游标对象，然后执行SQL语句，最后处理异常，清理资源。这些访问数据库的代码如果分散到各个函数中，势必无法维护，也不利于代码复用。所以，我们要首先把常用的SELECT、INSERT、UPDATE和DELETE操作用函数封装起来。由于Web框架使用了基于asyncio的aiohttp，这是基于协程的异步模型。在协程中，不能调用普通的同步IO操作，因为所有用户都是由一个线程服务的，协程的执行速度必须非常快，才能处理大量用户的请求。而耗时的IO操作不能在协程中以同步的方式调用，否则，等待一个IO操作时，系统无法响应任何其他用户。这就是异步编程的一个原则：一旦决定使用异步，则系统每一层都必须是异步，“开弓没有回头箭”。幸运的是 aiomysql 为MySQL数据库提供了异步IO的驱动。

1. 要执行SELECT语句，我们用select函数执行，需要传入SQL语句和SQL参数：

async def fetchone(sql, args=(), size=None):

"""封装select，查询单个，返回数据为字典"""

log(sql, args)

async with _mysql_pool.acquire() as conn:

async with conn.cursor(aiomysql.DictCursor) as cur:

await cur.execute(sql, args)

result = await cur.fetchone()

return result

async def select(sql, args=(), size=None):

"""封装select，查询多个，返回数据为列表"""

log(sql, args)

async with _mysql_pool.acquire() as conn:

async with conn.cursor(aiomysql.DictCursor) as cur:

await cur.execute(sql, args)

if size:

result = await cur.fetchmany(size)

else:

result = await cur.fetchall()

logging.info('rows returned: %s' % len(result))

return result

注意要始终坚持使用带参数的SQL，而不是自己拼接SQL字符串，这样可以防止SQL注入攻击。如果传入size参数，就通过fetchmany()获取最多指定数量的记录，否则，通过fetchall()获取所有记录。

2. 封装Insert, Update, Delete

要执行INSERT、UPDATE、DELETE语句，可以定义一个通用的execute()函数，因为这3种SQL的执行都需要相同的参数，以及返回一个整数表示影响的行数：

async def execute(sql, args=()):

"""封装insert, delete, update"""

log(sql, args)

async with _mysql_pool.acquire() as conn:

async with conn.cursor() as cur:

try:

await cur.execute(sql, args)

except BaseException:

await conn.rollback()

return

else:

affected = cur.rowcount

return affected

execute()函数和select()函数所不同的是，cursor对象不返回结果集，而是通过rowcount返回结果数。