❶ 挑战sql：图解pandas的数据合并merge函数
大家好，我是Peter~
在实际的业务需求中，我们的数据可能存在于不同的库表中。很多情况下，我们需要进行多表的连接查询来实现数据的提取，通过SQL的join，比如left join、left join、inner join等来实现。
在pandas中也有实现合并功能的函数，比如：concat、append、join、merge。本文中重点介绍的是 merge函数 ，也是pandas中最为重要的一个实现数据合并的函数。
看完了你会放弃SQL吗？
目前Pandas系列文章已经更新了13篇，文章都是以案例+图解的风格，欢迎访问阅读。有很多个人推荐的文章：
官网学习地址： https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html#
参数的具体解释为：
我们创建了4个DataFrame数据框；其中df1和df2、df3是具有相同的键userid；df4有类似的键userid1，取值也是ac，和df1或df2的userid取值有相同的部分。
left、how就是需要连接的两个数据帧，一般有两种写法：
图解过程如下：
inner称之为 内连接 。它会直接根据相同的列属性userid进行关联，取出属性下面相同的数据信息a、c
⚠️上面的图解过程就是默认的使用how="inner"
outer称之为外连接，在拼接的过程中会取两个数据框中键的并集进行拼接
图解过程如下：
以左边数据框中的键为基准；如果左边存在但是右边不存在，则右边用NaN表示
图解过程如下：
以右边数据框中的键的取值为基准；如果右边存在但是左边不存在，则左边用NaN表示
图解过程如下：
笛卡尔积：两个数据框中的数据交叉匹配，出现 n1*n2 的数据量
笛卡尔积的图解过程如下：
如果待连接的两个数据框有相同的键，则默认使用该相同的键进行联结。
上面的所有图解例子的参数on默认都是使用相同的键进行联结，所以有时候可省略。
再看个例子：
还可以将left和right的位置进行互换：
上面的两个例子都是针对数据框只有具有相同的一个键，如果不止通过一个键进行联结，该如何处理？通过一个来自官网的例子来解释，我们先创建两个DataFrame：df5、df6
现在进行两个数据框的合并：
合并的图解过程如下：
在看一个通过how="outer"进行连接的案例：
看看图解的过程：
上面在连接合并的时候，两个数据框之前都是有相同的字段，比如userid或者key1和key2。但是如何两个数据框中没有相同的键，但是这些键中的取值有相同的部分，比如我们的df1、df3：
在这个时候我们就使用left_on和right_on参数，分别指定两边的连接的键：
如果我们不指定，系统就会报错，因为这两个数据框是没有相同的键，本身是无法连接的：
如果连接之后结果有相同的字段出现，默认后缀是 _x_、_y 。这个参数就是改变我们默认的后缀。我们回顾下笛卡尔积的形成；
现在我们可以指定想要的后缀：
这个参数的作用是表明生成的一条记录是来自哪个DataFrame：both、left_only、right_only
如果带上参数会显示一个新字段 _merge ：
不带上参数的话，默认是不会显示来源的，看默认的情况：
merge函数真的是非常强大，在工作中也使用地很频繁，完全可以实现SQL中的join效果。希望本文的图解能够帮助读者理解这个合并函数的使用。同时pandas还有另外几个与合并相关的函数，比如：join、concat、append，会在下一篇文中统一讲解。
❷ 基于Pandas的数据分析平台，数据连接该不该用SqlAlchemy的ORM
、始使用：
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
DB_CONNECT_STRING = 'mysql+mysqldb://root:123@localhost/ooxx?charset=utf8'
engine = create_engine(DB_CONNECT_STRING, echo=True)
DB_Session = sessionmaker(bind=engine)
session = DB_Session()
DB_CONNECT_STRING 连接数据库路径mysql+mysqldb指定使用 MySQL-Python 连接root123别用户名密码localhost数据库域名ooxx使用数据库名（省略）charset指定连接使用字符集（省略）
create_engine() 返数据库引擎echo 参数 True 显示每条执行 SQL 语句产环境关闭
sessionmaker() 数据库类类实例数据库连接同记录些查询数据并决定候执行 SQL 语句由于 SQLAlchemy 自维护数据库连接池（默认 5 连接）初始化销并 Tornado 言 BaseHandler initialize() 初始化：
class BaseHandler(tornado.web.RequestHandler):
def initialize(self):
self.session = models.DB_Session()
def on_finish(self):
self.session.close()
其 Web 服务器说使用 sqlalchemy.orm.scoped_session能保证每线程获 session 象都唯 Tornado 本身单线程使用异步式能现问题并没使用
拿 session 执行 SQL ：
session.execute('create database abc')
print session.execute('show databases').fetchall()
session.execute('use abc')
# 建 user 表程略
print session.execute('select * from user where id = 1').first()
print session.execute('select * from user where id = :id', {'id': 1}).first()
直接使用 MySQL-Python 没啥区别；ORM 式采用 SQLAlchemy 唯原
于定义表：
from sqlalchemy import Column
from sqlalchemy.types import CHAR, Integer, String
from sqlalchemy.ext.declarative import declarative_base
BaseModel = declarative_base()
def init_db():
BaseModel.metadata.create_all(engine)
def drop_db():
BaseModel.metadata.drop_all(engine)
class User(BaseModel):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(CHAR(30)) # or Column(String(30))
init_db()
declarative_base() 创建 BaseModel 类类类自与表关联
User 类例 __tablename__ 属性数据库该表名称 id name 两字段别整型 30 定字符Column 些其参数我解释
BaseModel.metadata.create_all(engine) 找 BaseModel 所类并数据库建立些表；drop_all() 则删除些表
接着始使用表吧：
from sqlalchemy import func, or_, not_
user = User(name='a')
session.add(user)
user = User(name='b')
session.add(user)
user = User(name='a')
session.add(user)
user = User()
session.add(user)
session.commit()
query = session.query(User)
print query # 显示SQL 语句
print query.statement # 同
for user in query: # 遍历查询
print user.name
print query.all() # 返类似列表象
print query.first().name # 记录存first() 返 None
# print query.one().name # 存或行记录抛异
print query.filter(User.id == 2).first().name
print query.get(2).name # 主键获取等效于句
print query.filter('id = 2').first().name # 支持字符串
query2 = session.query(User.name)
print query2.all() # 每行元组
print query2.limit(1).all() # 返 1 条记录
print query2.offset(1).all() # 第 2 条记录始返
print query2.order_by(User.name).all()
print query2.order_by('name').all()
print query2.order_by(User.name.desc()).all()
print query2.order_by('name desc').all()
print session.query(User.id).order_by(User.name.desc(), User.id).all()
print query2.filter(User.id == 1).scalar() # 记录返第条记录第元素
print session.query('id').select_from(User).filter('id = 1').scalar()
print query2.filter(User.id > 1, User.name != 'a').scalar() # and
query3 = query2.filter(User.id > 1) # 拼接 filter and
query3 = query3.filter(User.name != 'a')
print query3.scalar()
print query2.filter(or_(User.id == 1, User.id == 2)).all() # or
print query2.filter(User.id.in_((1, 2))).all() # in
query4 = session.query(User.id)
print query4.filter(User.name == None).scalar()
print query4.filter('name is null').scalar()
print query4.filter(not_(User.name == None)).all() # not
print query4.filter(User.name != None).all()
print query4.count()
print session.query(func.count('*')).select_from(User).scalar()
print session.query(func.count('1')).select_from(User).scalar()
print session.query(func.count(User.id)).scalar()
print session.query(func.count('*')).filter(User.id > 0).scalar() # filter() 包含 User需要指定表
print session.query(func.count('*')).filter(User.name == 'a').limit(1).scalar() == 1 # 用 limit() 限制 count() 返数
print session.query(func.sum(User.id)).scalar()
print session.query(func.now()).scalar() # func 跟任意函数名要该数据库支持
print session.query(func.current_timestamp()).scalar()
print session.query(func.md5(User.name)).filter(User.id == 1).scalar()
query.filter(User.id == 1).update({User.name: 'c'})
user = query.get(1)
print user.name
user.name = 'd'
session.flush() # 写数据库并提交
print query.get(1).name
session.delete(user)
session.flush()
print query.get(1)
session.rollback()
print query.get(1).name
query.filter(User.id == 1).delete()
session.commit()
print query.get(1)
二、进阶知识
1）何批量插入批数据
使用非 ORM 式：
session.execute(
User.__table__.insert(),
[{'name': `randint(1, 100)`,'age': randint(1, 100)} for i in xrange(10000)]
)
session.commit()
何批量插入批数据
使用非 ORM 式：
session.execute(
User.__table__.insert(),
[{'name': `randint(1, 100)`,'age': randint(1, 100)} for i in xrange(10000)]
)
session.commit()
面批量插入 10000 条记录半秒内执行完； ORM 式花掉间
2）何让执行 SQL 语句增加前缀
使用 query 象 prefix_with() ：
session.query(User.name).prefix_with('HIGH_PRIORITY').all()
session.execute(User.__table__.insert().prefix_with('IGNORE'), {'id': 1, 'name': '1'})
3）何替换已主键记录
使用 session.merge() 替代 session.add()其实 SELECT + UPDATE：
user = User(id=1, name='ooxx')
session.merge(user)
session.commit()
或者使用 MySQL INSERT … ON DUPLICATE KEY UPDATE需要用 @compiles 装饰器点难懂自看吧：《SQLAlchemy ON DUPLICATE KEY UPDATE》 sqlalchemy_mysql_ext
4）何使用符号整数
使用 MySQL 言：
from sqlalchemy.dialects.mysql import INTEGER
id = Column(INTEGER(unsigned=True), primary_key=True)
5）模型属性名需要表字段名办
发遇奇怪需求其系统表包含from字段 Python 关键字于能处理：
from_ = Column('from', CHAR(10))
6）何获取字段度
Column 复杂象想获取度比较麻烦 User.name 例：
User.name.property.columns[0].type.length
7）何指定使用 InnoDB及使用 UTF-8 编码
简单式修改数据库默认配置非要代码指定：
class User(BaseModel):
__table_args__ = {
'mysql_engine': 'InnoDB',
'mysql_charset': 'utf8'
}
MySQL 5.5 始支持存储 4 字节 UTF-8 编码字符iOS 自带 emoji（ ?? 字符）属于种
表设置面代码 utf8 改 utf8mb4DB_CONNECT_STRING charset 更改
库或字段设置则自写 SQL 语句比较便具体细节参考《How to support full Unicode in MySQL databases》
建议全用 utf8mb4 代替 utf8前者更慢索引占用更空间
8）何设置外键约束
from random import randint
from sqlalchemy import ForeignKey
class User(BaseModel):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
age = Column(Integer)
class Friendship(BaseModel):
__tablename__ = 'friendship'
id = Column(Integer, primary_key=True)
user_id1 = Column(Integer, ForeignKey('user.id'))
user_id2 = Column(Integer, ForeignKey('user.id'))
for i in xrange(100):
session.add(User(age=randint(1, 100)))
session.flush() # 或 session.commit()执行完user 象 id 属性才访问（ id 自增）
for i in xrange(100):
session.add(Friendship(user_id1=randint(1, 100), user_id2=randint(1, 100)))
session.commit()
session.query(User).filter(User.age < 50).delete()
执行段代码应该遇错误：
sqlalchemy.exc.IntegrityError: (IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`ooxx`.`friendship`, CONSTRAINT `friendship_ibfk_1` FOREIGN KEY (`user_id1`) REFERENCES `user` (`id`))') 'DELETE FROM user WHERE user.age < %s' (50,)原删除 user 表数据能导致 friendship 外键指向真实存记录默认情况MySQL 拒绝种操作 RESTRICTInnoDB 允许指定 ON DELETE CASCADE SET NULL前者删除 friendship 效记录者些记录外键设 NULL
除删除能更改主键导致 friendship 外键失效于相应 ON UPDATE 其 CASCADE 变更新相应外键删除
SQLAlchemy 处理：
class Friendship(BaseModel):
__tablename__ = 'friendship'
id = Column(Integer, primary_key=True)
user_id1 = Column(Integer, ForeignKey('user.id', ondelete='CASCADE', onupdate='CASCADE'))
user_id2 = Column(Integer, ForeignKey('user.id', ondelete='CASCADE', onupdate='CASCADE'))
9）何连接表
from sqlalchemy import distinct
from sqlalchemy.orm import aliased
Friend = aliased(User, name='Friend')
print session.query(User.id).join(Friendship, User.id == Friendship.user_id1).all() # 所朋友用户
print session.query(distinct(User.id)).join(Friendship, User.id == Friendship.user_id1).all() # 所朋友用户（掉重复）
print session.query(User.id).join(Friendship, User.id == Friendship.user_id1).distinct().all() # 同
print session.query(Friendship.user_id2).join(User, User.id == Friendship.user_id1).order_by(Friendship.user_id2).distinct().all() # 所别朋友用户
print session.query(Friendship.user_id2).select_from(User).join(Friendship, User.id == Friendship.user_id1).order_by(Friendship.user_id2).distinct().all() # 同join 向相反 STRAIGHT_JOIN所 MySQL 自选择顺序
print session.query(User.id, Friendship.user_id2).join(Friendship, User.id == Friendship.user_id1).all() # 用户及其朋友
print session.query(User.id, Friendship.user_id2).join(Friendship, User.id == Friendship.user_id1).filter(User.id < 10).all() # id 于 10 用户及其朋友
print session.query(User.id, Friend.id).join(Friendship, User.id == Friendship.user_id1).join(Friend, Friend.id == Friendship.user_id2).all() # 两 join由于使用相同表需要别名
print session.query(User.id, Friendship.user_id2).outerjoin(Friendship, User.id == Friendship.user_id1).all() # 用户及其朋友（朋友则 None使用左连接）
-
-
❸ 数据库间如何自动拿数据
详情如下：
1、连接数据库，不同数据库python有对应的第三方库，用账号密码ip地址直接连接。
2、执行sql语句，可以用pandas里面的read_sql_query，也可以用数据库第三方库的fetchall。
3、获取结果，read_sql_query直接出来带列名的DataFrame，但fetchall这种还要另外处理成DataFrame，还有其他数据处理的，在这一步加上就是了。
4、保存结果，pandas里面有一个to_pickle的函数，可以把数据序列化保存在本地文件，需要用到的时候再read_pickle反序列拿出来用，比不停地执行sql要方便。
5、将以上四步所有的操作代码封装成函数，作为scheler的其中一个作业，设置执行周期和执行时间，到点就会自动获取数据保存在本地文件了
❹ 如何从postgresq获取数据到pandas(不通过pandas 的 tosql函数)
直接用pandas读取PostgreSQL数据不行吗
from sqlalchemy import create_engine
engine = create_engine('postgresql://user@localhost:5432/mydb')
df = pd.read_sql_query('select * from "Stat_Table"',con=engine)
❺ pandas 可以载入sql语句吗
各种数据库有相应的软件包，SQL Server有pyodbc，Oracle有cx_Oracle，MySQL有MySQLdb，各种包有相应的调用方法。 需要根据需求选择合适的数据库类型，然后选择访问数据库的包进一步细化后续的工作。
❻ 基于Pandas的数据分析平台，数据连接该不该用SqlAlchemy的ORM
当然可以自己写代码连接到数据库，并操作之。使用Sqlalchmy的目的主要是ROM吧。工作重心可以放在业务处理上，而不用太操心数据库的具体操作和sql语法，特别是多表的join～Flask的文档里，就有说怎么直接通过Python操作数据库的内容。
❼ python数据分析需要哪些库
Python数据分析需要安装的第三方扩展库有：Numpy、Pandas、SciPy、Matplotlib、Scikit-Learn、Keras、Gensim、Scrapy等，以下是对该第三方扩展库的简要介绍：
1. Pandas
Pandas是Python强大、灵活的数据分析和探索工具，包含Series、DataFrame等高级数据结构和工具，安装Pandas可使Python中处理数据非常快速和简单。
Pandas是Python的一个数据分析包，Pandas最初被用作金融数据分析工具而开发出来，因此Pandas为时间序列分析提供了很好的支持。
Pandas是为了解决数据分析任务而创建的，Pandas纳入了大量的库和一些标准的数据模型，提供了高效的操作大型数据集所需要的工具。Pandas提供了大量是我们快速便捷的处理数据的函数和方法。Pandas包含了高级数据结构，以及让数据分析变得快速、简单的工具。它建立在Numpy之上，使得Numpy应用变得简单。
带有坐标轴的数据结构，支持自动或明确的数据对齐。这能防止由于数据结构没有对齐，以及处理不同来源、采用不同索引的数据而产生的常见错误。
使用Pandas更容易处理丢失数据。
合并流行数据库（如：基于SQL的数据库）
Pandas是进行数据清晰/整理的最好工具。
2. Numpy
Python没有提供数组功能，Numpy可以提供数组支持以及相应的高效处理函数，是Python数据分析的基础，也是SciPy、Pandas等数据处理和科学计算库最基本的函数功能库，且其数据类型对Python数据分析十分有用。
Numpy提供了两种基本的对象：ndarray和ufunc。ndarray是存储单一数据类型的多维数组，而ufunc是能够对数组进行处理的函数。Numpy的功能：
•N维数组，一种快速、高效使用内存的多维数组，他提供矢量化数学运算。
•可以不需要使用循环，就能对整个数组内的数据进行标准数学运算。
•非常便于传送数据到用低级语言编写(C\C++)的外部库,也便于外部库以Numpy数组形式返回数据。
Numpy不提供高级数据分析功能，但可以更加深刻的理解Numpy数组和面向数组的计算。
#一般以np作为numpy的别名
import numpy as np
#创建数组
a = np.array([2,1,0,5])
print(a)
print(a[:3])
print(a.min())
a.sort()
b = np.array([1,2,3],[4,5,6])
print(b*b)
3. Matplotlib
Matplotlib是强大的数据可视化工具和作图库，是主要用于绘制数据图表的Python库，提供了绘制各类可视化图形的命令字库、简单的接口，可以方便用户轻松掌握图形的格式，绘制各类可视化图形。
Matplotlib是Python的一个可视化模块，他能方便的只做线条图、饼图、柱状图以及其他专业图形。
使用Matplotlib，可以定制所做图表的任一方面。他支持所有操作系统下不同的GUI后端，并且可以将图形输出为常见的矢量图和图形测试，如PDF SVG JPG PNG BMP GIF.通过数据绘图，我们可以将枯燥的数字转化成人们容易接收的图表。
Matplotlib是基于Numpy的一套Python包，这个包提供了吩咐的数据绘图工具，主要用于绘制一些统计图形。
Matplotlib有一套允许定制各种属性的默认设置，可以控制Matplotlib中的每一个默认属性：图像大小、每英寸点数、线宽、色彩和样式、子图、坐标轴、网个属性、文字和文字属性。
4. SciPy
SciPy是一组专门解决科学计算中各种标准问题域的包的集合，包含的功能有最优化、线性代数、积分、插值、拟合、特殊函数、快速傅里叶变换、信号处理和图像处理、常微分方程求解和其他科学与工程中常用的计算等，这些对数据分析和挖掘十分有用。
Scipy是一款方便、易于使用、专门为科学和工程设计的Python包，它包括统计、优化、整合、线性代数模块、傅里叶变换、信号和图像处理、常微分方程求解器等。Scipy依赖于Numpy，并提供许多对用户友好的和有效的数值例程，如数值积分和优化。
Python有着像Matlab一样强大的数值计算工具包Numpy；有着绘图工具包Matplotlib;有着科学计算工具包Scipy。
Python能直接处理数据，而Pandas几乎可以像SQL那样对数据进行控制。Matplotlib能够对数据和记过进行可视化，快速理解数据。Scikit-Learn提供了机器学习算法的支持，Theano提供了升读学习框架（还可以使用CPU加速）。
5. Keras
Keras是深度学习库，人工神经网络和深度学习模型，基于Theano之上，依赖于Numpy和Scipy，利用它可以搭建普通的神经网络和各种深度学习模型，如语言处理、图像识别、自编码器、循环神经网络、递归审计网络、卷积神经网络等。
6. Scikit-Learn
Scikit-Learn是Python常用的机器学习工具包，提供了完善的机器学习工具箱，支持数据预处理、分类、回归、聚类、预测和模型分析等强大机器学习库，其依赖于Numpy、Scipy和Matplotlib等。
Scikit-Learn是基于Python机器学习的模块，基于BSD开源许可证。
Scikit-Learn的安装需要Numpy S Matplotlib等模块，Scikit-Learn的主要功能分为六个部分，分类、回归、聚类、数据降维、模型选择、数据预处理。
Scikit-Learn自带一些经典的数据集，比如用于分类的iris和digits数据集，还有用于回归分析的boston house prices数据集。该数据集是一种字典结构，数据存储在.data成员中，输出标签存储在.target成员中。Scikit-Learn建立在Scipy之上，提供了一套常用的机器学习算法，通过一个统一的接口来使用，Scikit-Learn有助于在数据集上实现流行的算法。
Scikit-Learn还有一些库，比如：用于自然语言处理的Nltk、用于网站数据抓取的Scrappy、用于网络挖掘的Pattern、用于深度学习的Theano等。
7. Scrapy
Scrapy是专门为爬虫而生的工具，具有URL读取、HTML解析、存储数据等功能，可以使用Twisted异步网络库来处理网络通讯，架构清晰，且包含了各种中间件接口，可以灵活的完成各种需求。
8. Gensim
Gensim是用来做文本主题模型的库，常用于处理语言方面的任务，支持TF-IDF、LSA、LDA和Word2Vec在内的多种主题模型算法，支持流式训练，并提供了诸如相似度计算、信息检索等一些常用任务的API接口。
以上是对Python数据分析常用工具的简单介绍，有兴趣的可以深入学习研究一下相关使用方法!