平时用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的便利工具
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
|
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先导入数据库 保留关系 然后变为扁平化数据
- 对sql的使用场景要有所了解 适合作什么
#参考
