Python中使用 sqlalchemy 访问数据库和简单操作

在我们的数据仓库设计中,python 是我们用来连接各个数据库和应用逻辑的纽带。python 几乎无所不能,反正性能上有要求的就交给 java,要快速开发的逻辑多变的就交给 python。

python 中访问数据库最有名的就是 sqlalchemy 包了。

from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.sql import select, insert

engine = create_engine('mysql+pymysql://macmini:***@192.168.199.***:3306/test?charset=utf8mb4', pool_recycle=3600)
meta = MetaData(bind=engine)
students = Table("students", meta, autoload=True, autoload_with=engine)

conn = engine.connect()

s = select([students])
result = conn.execute(s)

print(s)

for row in result:
    print(row)

 

通过 sqlalchemy 访问 mysql 数据库,需要先创建一个 engine 对象,输入 mysql 的 ip 地址和用户名密码。后面的就简单了。

sqlalchemy 访问数据库可以用 orm 或者称之为 Expression Language 的方法,上面例子里面的 select 方法就是属于 sqlaclchemy 的快速语言方法,s 变量是用来检验的生成的 sql 语句,

看一下结果就很明白了,列出了sql语句(一般是不需要的),以及 select 的查询结果:

SELECT students.id, students.name, students.sex, students.age, students.tel
FROM students

(1, ‘王刚’, ‘男’, 20, ‘13811371377’)
(2, ‘张三’, ‘男’, 30, ‘16512341234’)

下面则是 insert 的用法,是不是非常简洁,这样的写法避免了 sql 语句的啰嗦,抽象起来,就可以用在业务逻辑复杂多变的场景了,即便是所谓写死的业务逻辑,这样也比较容易看懂,而一般的 sql 语句非常不容易调试:

conn.execute(students.insert(), [
    {'name' : '张三', 'sex' : '男', 'age' : 30, 'tel' : '16512341234'},
    ])

 

python和数据库学习笔记-在mac下配置python和mysql开发环境

一个小项目结束,于是可以继续研究。现在研究的课题和大数据有关,数据仓库目前是在vertica上面,然后计划用公有云的方案来处理计算能力的弹性问题,也就是将部分数据从vertica导入到hive+spark中,方案还是庞大复杂的,所以除了设计整个架构以外,也继续要考虑应用端的很多实践。和spark打交道的事情就交给java了,应用层交给php+js,中间最复杂的业务逻辑自然是我现在最为推崇的python了。

好,慢慢开始学习。

任务:

  1. 在mac上搭建一个vmware上的ubuntu虚拟机,安装mysql,这样从mac的开发环境访问,比较接近真实情况
  2. python在mac上设置为可以访问mysql,且不需要安装mysql

安装vmware,我是在 osx 10.11.2 上安装的 vmware 8.0,从 ubuntu 官网上下载的 ubuntu 15.04 desktop 版本,选择 desktop 版本其实作为 server 是不太好的,不过我对linux 的熟悉程度还不够,所以考虑有图形界面比较容易使用。

在虚拟机里面安装 ubuntu 还是比较容易的,然后安装 ubuntu 后再在命令上下安装 mysql:

sudo apt-get mysql-server

 

很多google上的资料说要注意 mysql 的 root 密码之类,我在安装的时候,mysql 是主动提醒要修改 root 密码的。我安装的 mysql 版本是 5.6.27。

在 ubuntu 下比较好用的 mysql 的图形管理客户端是官方的免费的 MySQL Workbench,我安装的版本是 6.2。用这个图形客户端设置用户名、数据库之类都非常方便。

mysql 安装后在默认情况下的确不允许其他电脑连接,这个弄了我很久,很多资料都说的不对,我觉得权限给的很高也不合适,很多资料说到的 bind ip 的修改,我发现是在 /etc/mysql/mysql.conf.d/mysqld.cnf 中修改,去掉 bindip = 127.0.0.1 的设置。

在 vmware 的虚拟机上安装好 ubuntu+mysql 之后,开始在mac 开发环境连接,同样是安装 MySQL Workbench 的 mac 版本,然后就可以测试连接之类,这个方法最方便实用。

这样,比起 localhost 当然是麻烦一点,主要是为之后多环境做好一些准备(开发环境、测试环境、准生产环境和生产环境)。在 mac 上用 xampp 也是不错的,改天再测试,更加适合笔记本电脑。

对于 python 怎么连接 mysql,发现 python 世界真的发展好快,方法很多。

因为准备之后要用 petl 来进行数据库的抽取和处理,所以按照 petl 的推荐,使用 pymysql 这个库。安装用标准的 pip 即可,我用的是conda,方法一样。

mac 开发环境下是不需要安装整个 mysql server 的,但是还是要安装一些驱动的,安装 mysql 标准的 connector,参考这里,下载后,在 mac 这里是一个 dmg 安装包,安装后,可以理解为 mysql 的驱动程序就安装好了。

import pymysql

connection = pymysql.connect(host='192.168.***.***',
                             user='***',
                             password='***',
                             db='test',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

cur = connection.cursor()

cur.execute('select * from students')

for data in cur.fetchall():
    print(data)

 

当然这个用法是比较简单的,输出结果,运行ok

{‘tel’: ‘13811371377’, ‘sex’: ‘男’, ‘id’: 1, ‘age’: 20, ‘name’: ‘王刚’}

这个demo数据库的建立参考了这里,一个不错的mysql的基础教程。

大致这样,真实的开发项目会越来越复杂,且数据库应用的复杂度比独立应用高很多,希望这些经验能够给初学者们启发。

———————–后续补充

看到廖雪峰网站,上面有非常不错的python 2和3 的教程,对于安装mysql connector 有更加简单的办法

$ pip install mysql-connector-python --allow-external mysql-connector-python

 

以及非常简明实用的mysql 测试:

# 导入MySQL驱动:
>>> import mysql.connector
# 注意把password设为你的root口令:
>>> conn = mysql.connector.connect(user='root', password='password', database='test')
>>> cursor = conn.cursor()
# 创建user表:
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
# 插入一行记录,注意MySQL的占位符是%s:
>>> cursor.execute('insert into user (id, name) values (%s, %s)', ['1', 'Michael'])
>>> cursor.rowcount
1
# 提交事务:
>>> conn.commit()
>>> cursor.close()
# 运行查询:
>>> cursor = conn.cursor()
>>> cursor.execute('select * from user where id = %s', ['1'])
>>> values = cursor.fetchall()
>>> values
[('1', 'Michael')]
# 关闭Cursor和Connection:
>>> cursor.close()
True
>>> conn.close()

 

需要大数据么?

2016.9.28

这篇 blog 是写在2013.8.4,我觉得个人其中大部分的观点还是正确的,不过,这三年大数据以及相关技术的确发展的很快,更多的应用场景也被挖掘出来。看来是要重新写一点文字了。不管是机器人还是人看到这些文字,都可以。

—-

2013.8.4

下面这些问题不是给纯粹技术人员看的,也不是给纯粹业务人员看的,如果你想在业务中引入大数据,那么可以冷静的思考一下。

1 大数据和传统数据有什么差别。

首先是数据量的差别。我觉得1亿条数据或者1T数据是一个分界岭,oracle、sql server,以及mysql等对于1亿条的数据还是可以很好的支持的,但是在应用层面如果设计的不是很好会有点瓶颈。还有就是数据增量的问题,如果一个月里面1亿条变成2亿条了,那么就开始准备大数据吧。

其次是需求。大数据讲的是通过数据来进行挖掘、建模和产生效益,传统的数据也有BI等,但是更多的报表汇总等。这一点我始终觉得是大数据时代和传统数据时代区别最大的地方,大数据时代侧重于从海量数据中产生价值,并且不需要抽样,处理的是全数据,且是实时或者准实时,并且是性价比较高的方式。

2 大数据要花很多钱么?

传统的应用设计模式,对于每天能产生百万条记录本身也要花费不少钱的,当然能够产生这么多有价值数据(特别是交易记录)的话,你的商业模式也会不差,所以有趣的是在国内,很多大数据应用都是在电商,比如淘宝、亚马逊、1号店等。

除了基础设施,主要的开销就是在hadoop、服务器和网络、分析软件和人上面了。并不是想象中那么便宜,即便hadoop是免费开源软件,光是hadoop的基础服务器群,就不少预算。阿里贷据说搭建了2000台服务器的hadoop集群。性能是需要硬件支撑的。分析软件、二次开发、运营维护等等,不会是键盘上一个键按下去,就搞定了这样的好事。(我们有一个使用mongodb的最小集群进行map/reduce计算的实际使用场景,至少我个人觉得周期并不短)

3 大数据到底带来什么?

前面说了一点,如果用得好,我们可以准实时(5s里面)获得所有需要的数据,并且是有各类基于维度统计、统计模型、自定义的计算。这也是令传统数据库产业链惊慌的,因为互联网、因为智能手机,产生了大量的数据,所以终于需要分析了。数据不再匮乏,怎么用倒是成了一个问题。

我们可以根据用户、交易的行为,通过建模得出我们想要的用户行为模式或者用户资料,然后在营销中得以实践,并不断修正模型。除了传统理解的数据以外,大量的非结构化数据一样可以纳入大数据的计算范畴,比如微博的记录、电子邮件的内容等等。