平时用ORM偏多，生疏了SQL，周末翻了一遍《SQL必知必会》，也整理了一下相关工具，做个记录

关系数据库（Relational database）

按照维基百科说法

表(关系Relation)是以行(属性Attribate)和列(值组Tuble)的形式组织起来的数据的集合。一个数据库包括一个或多个表 (关系Relation)。例如，可能有一个有关作者信息的名为authors的表(关系Relation)。每列(值组Tuble)都包含特定类型的信息，如作者的姓氏。每行(属性Attribate)都包含有关特定作者的所有信息：姓、名、住址等等。在关系型数据库当中一个表 (关系Relation)就是一个关系，一个关系数据库可以包含多个表(关系Relation)

安装

开源的关系数据库中，PostgreSQL 与MySQL 最为流行。一般使用linux发行版的包管理器就能方便地安装，更多的安装方式可以参考官网

客户端

关系数据库中，我用MySQL和SQLite偏多，所以列出这两个数据库相关的工具

当然更经常地，我偏好在jupyter里使用sqlalchemy来连接数据库

SQL

《SQL必知必会》（第三版）里的代码:teach-yourself-sql

使用mycli连接数据库

1
2

CREATE DATABASE mytest; --创建数据库
USE mytest; --进入数据库

1
2
3


mysql -u root -D mytest   < /tmp/teach-yourself-sql/create.txt  #创建新表
mysql -u root -D mytest   < /tmp/teach-yourself-sql/populate.txt  # 插入数据

1
2
3

SHOW TABLES;  --查看表
DESCRIBE Customers;  --显示表结构
select * from Customers limit 5; --查看数据

查询

使用pandas做实验，存为ipynb

SQL.ipynb

todo：用SQLAlchemy实现

SQLAlchemy

SQLAlchemy是Python社区中最广泛使用的ORM工具，底层而强大

SQLAlchemy的理念是，SQL数据库的量级和性能重要于对象集合；而对象集合的抽象又重要于表和行

连接已有数据库

一般情况下我们已经有数据库了，那么如何将既有数据库和SQLAlchemy对接呢

可以利用表的反射（Table Reflection）。把它们”导入”进来即可，这时得使用autoload参数。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

from sqlalchemy import create_engine, MetaData, Table
#使用mysql，需要安装mysql驱动
#brew install mysql-connector-c（mac）
#sudo apt-get install libmysqlclient-dev （ubuntu）
#pip install MySQL-python
engine = create_engine('mysql+mysqldb://root@edx_host/edxapp', echo=True)
metadata = MetaData(engine)
conn = engine.connect() #连接成功
print 'auth_user' in metadata.tables #true
user_table = Table('auth_user', metadata, autoload=True) #metadata携带连接信息
print [c.name for c in user_table.columns] #打印出字段名
ins = user_table.insert()
print ins #查看编译的sql
users= conn.execute("SELECT * FROM auth_user limit 5") #直接执行sql

s = sqlalchemy.select([user_table])

##基于SQLAlchemy的便利工具

dataset

In short, dataset makes reading and writing data in databases as simple as reading and writing JSON files.

1
2
3
4
5
6

db = dataset.connect('mysql+mysqldb://root@edx_host/edxapp')
#print(db.tables)
table = db['auth_user']
table.columns
#result = db.query
# 导出数据 dataset.freeze(result, format='json', filename='users.json')

####records

SQL for Humans

易于导出到xls或是json。records 使用了 tablib 可以导出为任何格式

1
2
3

db = records.Database('mysql+mysqldb://root@edx_host/edxapp')
rows = db.query('select * from auth_user limit 5')
print rows.dataset

peewee

a small, expressive orm – supports postgresql, mysql and sqlite

使用习惯和django orm很像

 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

:::python
# http://docs.peewee-orm.com/en/latest/peewee/example.html

from peewee import *
import datetime

mysql_db = MySQLDatabase('yunfan', user='root', charset='utf8mb4')
#CREATE DATABASE `yunfan` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;  创建数据库使用utf-8 否则有编码问题

class QiniuStore(Model):
    course_id = CharField(null = True)
    username = CharField(default="")
    #key是关键字
    file_key = CharField(default="")
    filename = CharField(default="")
    filesize = CharField(default="0")
    #endUser = Column(String(100),nullable=True)
    create_time = DateTimeField(default=datetime.datetime.now)
    class Meta:
        database = mysql_db
        order_by = ('-create_time',)


if __name__ == "__main__":
    mysql_db.connect()
    mysql_db.create_tables([QiniuStore])

pandas

pandas的DataFrame大多时候也用来容纳二维数据，像一张表,所以易于与数据库打交道

1
2
3
4
5
6
7

#Pandas读取Mysql数据
import pandas as pd
import MySQLdb
mysql_cn= MySQLdb.connect(host='127.0.0.1', port=3306,user='root', passwd='', db='edxapp')
#df = pd.read_sql('select * from auth_user limit 100;', con=mysql_cn)  
df = pd.read_sql('select * from auth_user limit 10;', con=mysql_cn)
mysql_cn.close()

Read SQL query into a DataFrame ,之后可以使用pandas的查询和绘图统计功能

参考:

心得

  • 数据库的重点是表，操作可以用python方法，为了查询的灵活，还是需要sql
  • 用好ORM的前提是了解关系数据库的概念和典型用例

理解

数据库表是一个二维表，包含多行多列。把一个表的内容用Python的数据结构表示出来的话，可以用一个list表示多行，list的每一个元素是tuple，表示一行记录

行，想象为二维数据(csv),外键是一种关系

通用技能

  • 多维数据的可视化 （降维）是一个常见的问题模式 。如何聚合，pandas部分
  • 帆船项目 帮助理解pandas如何用于web流
    • 如何实时读取sql，这样一来oricle问题就解决了 可视化问题
    • 从真实数据学习数据图表化的原理 《网站分析》
  • 投资技能 pandas -> scikit-learn
  • edx的log先导入数据库 保留关系 然后变为扁平化数据
    • 将log导入数据库是为了获得强大的查询能力
  • 对sql的使用场景要有所了解 适合作什么

#参考