Python Data Science 简明教程
Python - Relational Databases
我们可以使用库以及用于实现数据库连接的另一个附加库 pandas 连接到关系数据库以分析数据。此包被命名为 sqlalchemy ，它提供可用于 python 的完整 SQL 语言功能。
We can connect to relational databases for analysing data using the pandas library as well as another additional library for implementing database connectivity. This package is named as sqlalchemy which provides full SQL language functionality to be used in python.
Installing SQLAlchemy
使用我们在章节 Data Science Environment 中讨论过的 Anaconda 安装非常简单，假设你已按照本章中的说明安装了 Anaconda，在 Anaconda Prompt 窗口中运行以下命令以安装 SQLAlchemy 包。
The installation is very straight forward using Anaconda which we have discussed in the chapter Data Science Environment. Assuming you have installed Anaconda as described in this chapter, run the following command in the Anaconda Prompt Window to install the SQLAlchemy package.
conda install sqlalchemyReading Relational Tables
我们将在作为关系数据库，因为它非常轻巧且易于使用。虽然 SQLAlchemy 库可以连接到各种关系源，包括 MySql、Oracle、Postgresql 和 Mssql。我们首先创建一个数据库引擎，然后使用 SQLAlchemy 库的 to_sql 函数连接到数据库引擎。
We will use Sqlite3 as our relational database as it is very light weight and easy to use. Though the SQLAlchemy library can connect to a variety of relational sources including MySql, Oracle and Postgresql and Mssql. We first create a database engine and then connect to the database engine using the to_sql function of the SQLAlchemy library.
在下面的示例中，我们使用 to_sql 函数从通过读取 csv 文件而创建的数据框中创建关系表。然后，我们使用熊猫中的 read_sql_query 函数从各种 SQL 查询中执行和获取结果。
In the below example we create the relational table by using the to_sql function from a dataframe already created by reading a csv file. Then we use the read_sql_query function from pandas to execute and capture the results from various SQL queries.
from sqlalchemy import create_engine
import pandas as pd
data = pd.read_csv('/path/input.csv')
# Create the db engine
engine = create_engine('sqlite:///:memory:')
# Store the dataframe as a table
data.to_sql('data_table', engine)
# Query 1 on the relational table
res1 = pd.read_sql_query('SELECT * FROM data_table', engine)
print('Result 1')
print(res1)
print('')
# Query 2 on the relational table
res2 = pd.read_sql_query('SELECT dept,sum(salary) FROM data_table group by dept', engine)
print('Result 2')
print(res2)当我们执行上面的代码时，它会产生以下结果。
When we execute the above code, it produces the following result.
Result 1
index id name salary start_date dept
0 0 1 Rick 623.30 2012-01-01 IT
1 1 2 Dan 515.20 2013-09-23 Operations
2 2 3 Tusar 611.00 2014-11-15 IT
3 3 4 Ryan 729.00 2014-05-11 HR
4 4 5 Gary 843.25 2015-03-27 Finance
5 5 6 Rasmi 578.00 2013-05-21 IT
6 6 7 Pranab 632.80 2013-07-30 Operations
7 7 8 Guru 722.50 2014-06-17 Finance
Result 2
dept sum(salary)
0 Finance 1565.75
1 HR 729.00
2 IT 1812.30
3 Operations 1148.00Inserting Data to Relational Tables
我们还可以使用熊猫中可用的 sql.execute 函数将数据插入到关系表中。在下方的代码中我们将以前 csv 文件作为输入数据集，将其存储在关系表中，然后使用 sql.execute 插入另一条记录。
We can also insert data into relational tables using sql.execute function available in pandas. In the below code we previous csv file as input data set, store it in a relational table and then insert another record using sql.execute.
from sqlalchemy import create_engine
from pandas.io import sql
import pandas as pd
data = pd.read_csv('C:/Users/Rasmi/Documents/pydatasci/input.csv')
engine = create_engine('sqlite:///:memory:')
# Store the Data in a relational table
data.to_sql('data_table', engine)
# Insert another row
sql.execute('INSERT INTO data_table VALUES(?,?,?,?,?,?)', engine, params=[('id',9,'Ruby',711.20,'2015-03-27','IT')])
# Read from the relational table
res = pd.read_sql_query('SELECT ID,Dept,Name,Salary,start_date FROM data_table', engine)
print(res)当我们执行上面的代码时，它会产生以下结果。
When we execute the above code, it produces the following result.
id dept name salary start_date
0 1 IT Rick 623.30 2012-01-01
1 2 Operations Dan 515.20 2013-09-23
2 3 IT Tusar 611.00 2014-11-15
3 4 HR Ryan 729.00 2014-05-11
4 5 Finance Gary 843.25 2015-03-27
5 6 IT Rasmi 578.00 2013-05-21
6 7 Operations Pranab 632.80 2013-07-30
7 8 Finance Guru 722.50 2014-06-17
8 9 IT Ruby 711.20 2015-03-27Deleting Data from Relational Tables
我们还可以使用熊猫中可用的 sql.execute 函数从关系表中删除数据。下面的代码根据给定的输入条件删除一行。
We can also delete data into relational tables using sql.execute function available in pandas. The below code deletes a row based on the input condition given.
from sqlalchemy import create_engine
from pandas.io import sql
import pandas as pd
data = pd.read_csv('C:/Users/Rasmi/Documents/pydatasci/input.csv')
engine = create_engine('sqlite:///:memory:')
data.to_sql('data_table', engine)
sql.execute('Delete from data_table where name = (?) ', engine, params=[('Gary')])
res = pd.read_sql_query('SELECT ID,Dept,Name,Salary,start_date FROM data_table', engine)
print(res)当我们执行上面的代码时，它会产生以下结果。
When we execute the above code, it produces the following result.
id dept name salary start_date
0 1 IT Rick 623.3 2012-01-01
1 2 Operations Dan 515.2 2013-09-23
2 3 IT Tusar 611.0 2014-11-15
3 4 HR Ryan 729.0 2014-05-11
4 6 IT Rasmi 578.0 2013-05-21
5 7 Operations Pranab 632.8 2013-07-30
6 8 Finance Guru 722.5 2014-06-17