数据库和python的关系_Python-关系数据库

数据库和python的关系

Python-关系数据库 (Python - Relational Databases)

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.

我们可以连接到关系数据库，以使用pandas库以及用于实现数据库连接性的另一个附加库来分析数据。 该软件包名为sqlalchemy ，它提供了可在python中使用的完整SQL语言功能。

安装SQLAlchemy (Installing 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.

使用Anaconda的安装非常简单，我们已经在“ 数据科学环境 ”一章中进行了讨论。 假设您已按照本章中的说明安装了Anaconda，请在Anaconda提示窗口中运行以下命令以安装SQLAlchemy软件包。

conda install sqlalchemy

阅读关系表 (Reading Relational Tables)

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.

我们将使用Sqlite3作为我们的关系数据库，因为它非常轻巧且易于使用。 尽管SQLAlchemy库可以连接到各种关系源，包括MySql，Oracle和Postgresql和Mssql。 我们首先创建一个数据库引擎，然后使用SQLAlchemy库的to_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.

在下面的示例中，我们使用to_sql函数从已通过读取csv文件创建的数据框中创建关系表。 然后，我们使用pandas的read_sql_query函数执行并捕获各种SQL查询的结果。

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.00

将数据插入关系表 (Inserting Data to Relational Tables)

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.

我们还可以使用pandas中提供的sql.execute函数将数据插入关系表中。 在下面的代码中，我们将先前的csv文件作为输入数据集，将其存储在关系表中，然后使用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-27

从关系表中删除数据 (Deleting Data from Relational Tables)

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.

我们还可以使用pandas中提供的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:')
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

翻译自: https://www.tutorialspoint.com/python_data_science/python_relational_databases.htm

数据库和python的关系