Sqlalchemy - 用于Python的orm

Tavish Aggarwal.

 

I

n这篇文章,我们将讨论用于连接对象 - relitional数据库的Sqlalchemy包,如:

 

 

  1. sqlite.
  2. postgres.
  3. mysql和更多

有些人可能想知道为什么我们需要一个包连接到数据库,我们可以直接连接它?

Sqlalchemy包是一个ORM,它挑选了我们的开发工作,非常有用。您将了解在本帖子结束时使用Sqlalchemy包的优势。

ORM(对象关系模型)始终有用,有助于我们加快开发。

我也在分享我的 jupyter笔记本 跟随帖子。

先决条件

关于关系数据库的基本知识是潜入此帖子之前必须的必要。以下是您在帖子开始之前应该知道的主题:

  1. 创建表格
  2. 插入语句以在表中插入数据
  3. 选择基于条件选择行的语句
  4. 更新语句以更新表中的数据
  5. 订购和聚合数据 
  6. 小组逐个条款和加入表格

我们将从SQLite示例数据库开始: chin。如果您无法从指定的链接下载,则可以始终使用文件 GitHub..

主题涵盖

以下是此帖子中涵盖的主题,即Sqlalchemy包必须提供:

  1. 连接到数据库
  2. 创建表格
  3. 在表中插入数据
    1. 在表中插入单行
    2. 在表中插入多行
  4. 从表中选择数据
    1. 使用where子句过滤数据
    2. 使用子句过滤数据
    3. 使用和/或/不过滤数据
    4. 订购数据
      1. 根据单个列订购数据
      2. 数据的反向排序
      3. 根据多列订购数据
      4. 聚合和分组数据
        1. 汇总数据
        2. 分组数据
      5. 重命名专栏标签
      6. 使用案例和演员陈述
      7. 执行算术运算
  5. 加入桌子
  6. 更新操作
  7. 创建Pandas DataFrame Out结果集
  8. 删除操作
    1. 根据条件删除数据
    2. 删除所有数据
    3. 从数据库中删除表

让我们看看如何使用Sqlalchemy包连接到数据库引擎。

连接到数据库

要连接到数据库,我们需要由Sqlalchemy包提供的CREATE_ENGINE。以下是用于连接SQLite引擎的代码:

# Import create_engine
from sqlalchemy import create_engine

engine = create_engine('sqlite:///Chinook_Sqlite.sqlite')
# Print table names
print(engine.table_names())

# Defining metadata object
metadata = MetaData()

此外,我们正在使用Table_Names()函数来获取我们正在连接的数据库中的所有表。

笔记: 在这里,我们使用的是SQLite数据库,您也可以使用任何其他SQL数据库,唯一的区别将是POST中共享的命令的其余命令的剩余效果对于任何SQL引擎保持不变。

一旦我们连接到数据库,下一步就是在数据库中创建表。

创建表格

使用SQLALCHEMY包我们可以在数据库中创建一个表。用于创建表的代码如下所示:

from sqlalchemy import MetaData, create_engine, Table, Column, String, Integer, Float
from sqlalchemy import Boolean, delete, insert, select,func, and_, desc, case, cast


engine = create_engine('sqlite:///Chinook_Sqlite.sqlite')
metadata = MetaData()

data = Table('instrument', metadata,
             Column('InstrumentID', Integer(), unique=True),
             Column('ArtistID', Integer(), unique=True),
             Column('Instrument_Name', String(500)),
             Column('Cost', Integer())
)
# Use the metadata to create the table
metadata.create_all(engine)


# We have created instrument table having artistID as well

在这里,我们正在使用元数据并在元数据中创建表,然后将其创建给引擎。让我们了解我们为什么要在元数据中创建表?元数据是一个中央对象,其中创建的所有表都链接到。每当我们必须在表结构中进行更新时,我们将在元数据中使其在数据库中完成。

在数据库中添加外键(链接表)也很有用。

创建表后,让我们看看我们如何访问表的信息。

# Get table info
instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

# Print the column names
print(instrument.columns.keys())

# Print the table details
print(repr(metadata.tables['instrument']))

一旦我们的表创建了它,是时候在表中插入数据了。

在表中插入数据

在表中插入单行

让我们看看如何在上面创建的表中插入数据。要插入数据,我们正在使用SQLALCHEMY包提供的插入功能。 

instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = insert(instrument).values(InstrumentID=1, ArtistID = 1, Instrument_Name='Guitar', 
Cost=245)

results = engine.execute(stmt)

# Returns number of rows affected
print(results.rowcount)

一旦数据插入表中,我们正在打印RowCount。它将返回执行语句时影响的行数。

在表中插入多行

我们甚至可以立即在表中插入多行。我们可以通过创建一个字典对象数组来执行此操作。让我们在行动中看到这一点:

## Inserting multiple row in the table
instrument_list = [
    {'InstrumentID': 2, 'ArtistID': 2, 'Instrument_Name': 'Tabla', 'Cost': 200},
    {'InstrumentID': 3, 'ArtistID': 3, 'Instrument_Name': 'Vilon', 'Cost': 500}
]

instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = insert(instrument)
results = engine.execute(stmt, instrument_list)

print(results.rowcount)

我们可以通过将CSV文件转换为字典对象数组来将CSV文件插入数据库中。让我们看一下一个例子要将CSV文件插入表中:

# Reading from csv file
instruments_csv = pd.read_csv('instruments.csv')
instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = insert(instrument)

instrument_list = []

for index, row in instruments_csv.iterrows():
    data = {'InstrumentID': row[0], 'ArtistID': row[1], 
                'Instrument_Name': row[2], 'Cost': row[3]}
    instrument_list.append(data)

results = engine.execute(stmt, instrument_list)
print(results.rowcount)

一旦我们创建了表,并将数据插入到表中。让我们看看我们如何根据条件和更多选择从表中选择数据。

从表中选择数据

让我们看看如何在不使用Sqlalchemy包的情况下从表中选择所有数据。 

# Selecting data without using SQLAlchemy package
stmt = 'SELECT * FROM instrument'

results = engine.execute(stmt).fetchall()
print(results)

笔记: 显示上面代码的原因是有时我们有一个非常复杂的查询来写入,这很难使用ORM。

现在让我们看看如何使用SQLalchemy包从表中选择数据。 

instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = select([instrument])

# SQL Statement while will be executed
print(stmt)

result = engine.execute(stmt).fetchall()

# First row
first_row = result[0]

print(first_row)

# First column in first row
print(first_row[0])

print(first_row['Instrument_Name'])

我们可以在Sqlalchemy包中编写各种类型的选择查询。以下是列表:

  1. 使用where子句过滤数据
  2. 使用子句过滤数据
  3. 使用和/或/不过滤数据
  4. 订购数据
    1. 根据单个列订购数据
    2. 数据的反向排序
    3. 根据多列订购数据
  5. 聚合和分组数据
  6. 重命名专栏标签
  7. 使用案例和演员陈述
  8. 执行算术运算

使用在哪里过滤数据 条款

我们甚至可以使用以下示例中显示的WHERE子句过滤我们的结果:

# Selecting using where clause
customer = Table('customer', metadata, autoload=True, autoload_with=engine)

stmt = select([customer])

stmt = stmt.where(customer.columns.PostalCode == '12227-000')

# To get an equivalent SQL query that will be executed
print(stmt) 

results = engine.execute(stmt).fetchall()

for result in results:
    print(result)

在执行查询之前打印“STMT”,也可以获得等效的SQL命令。上述查询的等效SQL是:

SELECT customer."CustomerId", customer."FirstName", customer."LastName", 
customer."Company", customer."Address", customer."City", customer."State", 
customer."数数ry", customer."PostalCode", customer."Phone", customer."Fax", 
customer."电子邮件", customer."SupportRepId" 
FROM customer 
WHERE customer."PostalCode" = :PostalCode_1

使用子句过滤数据

我们可以使用条款过滤我们的结果,如下图所示:

# Selecting using in clause
customer = Table('customer', metadata, autoload=True, autoload_with=engine)

stmt = select([customer])

stmt = stmt.where(customer.columns.PostalCode.in_(['12227-000', '14700']))

results = engine.execute(stmt).fetchall()

for result in results:
    print(result)

在执行查询之前打印“STMT”,也可以获得等效的SQL命令。上述查询的等效SQL是:

SELECT customer."CustomerId", customer."FirstName", customer."LastName", 
customer."Company", customer."Address", customer."City", customer."State", 
customer."数数ry", customer."PostalCode", customer."Phone", customer."Fax", 
customer."电子邮件", customer."SupportRepId" 
FROM customer 
WHERE customer."PostalCode" IN (:PostalCode_1, :PostalCode_2) 
ORDER BY customer."PostalCode"

使用和/或/不过滤数据

过滤数据不仅限于“在”条款中“或”其中“。我们也可以使用AND_(),或_()和not_()函数来过滤掉结果。下面显示的示例表示使用和_()函数的使用。以非常相似的方式,我们也可以使用其他功能。

# Selecting using and clause
customer = Table('customer', metadata, autoload=True, autoload_with=engine)

stmt = select([customer])

stmt = stmt.where(
    and_(
        customer.columns.Country == 'Brazil',
        customer.columns.Email == '[email protected]'
    )
)

results = engine.execute(stmt).fetchall()

for result in results:
    print(result)

在执行查询之前打印“STMT”,也可以获得等效的SQL命令。上述查询的等效SQL是:

SELECT customer."CustomerId", customer."FirstName", customer."LastName", 
customer."Company", customer."Address", customer."City", customer."State", 
customer."数数ry", customer."PostalCode", customer."Phone", customer."Fax", 
customer."电子邮件", customer."SupportRepId" 
FROM customer
WHERE customer."数数ry" = :Country_1 AND customer."电子邮件" = :Email_1

订购数据

订购我们的结果是我们需要执行的操作来获取有序数据集。 Sqlalchemy包允许我们以升序或降序从表中订购我们的数据。我们还可以根据一个或多个列订购我们的数据。下面显示的示例演示了数据的排序

基于的订购数据 一个单一的 column

让我们首先基于一个列的数据排序。默认订单将以升序排序的结果。

#ordering data with one column
instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = select([instrument])

stmt = stmt.order_by(instrument.columns.Instrument_Name)

result = engine.execute(stmt).fetchall()
print(result)

在执行查询之前打印“STMT”,也可以获得等效的SQL命令。上述查询的等效SQL是:

SELECT instrument."InstrumentID", instrument."ArtistID", 
instrument."Instrument_Name", instrument."Cost" 
FROM instrument 
ORDER BY instrument."Instrument_Name"

相反 ordering of data

我们甚至可以以降序排序表中的数据。以降序排序数据,我们需要使用一个名为desc()的附加函数。

#ordering data with one column in reverse order
instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = select([instrument])

stmt = stmt.order_by(desc(instrument.columns.Instrument_Name))

result = engine.execute(stmt).fetchall()
print(result)

在执行查询之前打印“STMT”,也可以获得等效的SQL命令。上述查询的等效SQL是:

SELECT instrument."InstrumentID", instrument."ArtistID", 
instrument."Instrument_Name", instrument."Cost" 
FROM instrument 
ORDER BY instrument."Instrument_Name" DESC

根据多个订购数据

如果我们订购的列有相同的值,那么也许我们需要考虑不同的列正确订购数据。在这种情况下,我们需要基于多列订购数据。下面显示的示例演示了基于多列的排序:

# ordering data with more than one column
instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = select([instrument])

stmt = stmt.order_by(desc(instrument.columns.Cost), instrument.columns.Instrument_Name)

result = engine.execute(stmt).fetchall()
print(result)

在执行查询之前打印“STMT”,也可以获得等效的SQL命令。上述查询的等效SQL是:

SELECT instrument."InstrumentID", instrument."ArtistID", 
instrument."Instrument_Name", instrument."Cost" 
FROM instrument 
ORDER BY instrument."Cost" DESC, instrument."Instrument_Name"

聚合和分组数据

聚合和分组我们类似的数据再次是数据科学家需要解决他的日常工作问题的常见操作。让我们探索不同类型的聚合操作以及如何在SQLalchemy包中对数据进行分组。

汇总数据

我们可以执行各种类型的聚合功能,如:

  1. Avg.
  2. 数数
  3. 最大限度

显示下面的示例,SemonStarte使用Count()聚合函数。但以类似的方式,我们也可以使用其他聚合函数。

# Aggregating data
instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = select([func.count(instrument.columns.Instrument_Name.distinct())])

distinct_instruments = engine.execute(stmt).scalar()

print(distinct_instruments)

在执行查询之前打印“STMT”,也可以获得等效的SQL命令。上述查询的等效SQL是:

SELECT count(DISTINCT instrument."Instrument_Name") AS count_1 
FROM instrument

分组数据

我们可以基于特定列对数据进行分组。根据我们在本帖子中创建的仪器表中,基于仪器表的数据基于intrics_name列进行的示例。

# Grouping up the data
instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = select([instrument.columns.Instrument_Name, 
               func.count(instrument.columns.Instrument_Name), instrument.columns.Cost])

stmt = stmt.group_by(instrument.columns.Instrument_Name)

results = engine.execute(stmt).fetchall()

print(results)

在执行查询之前打印“STMT”,也可以获得等效的SQL命令。上述查询的等效SQL是:

SELECT instrument."Instrument_Name", count(instrument."Instrument_Name") AS count_1, 
instrument."Cost" 
FROM instrument 
GROUP BY instrument."Instrument_Name"

重命名专栏标签

当我们在列中执行任何聚合操作或算术运算时,我们需要为其提供名称。我们可以使用标签函数重命名列。重命名列的示例如下所示:

# renaming column
instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

instruments_count = func.count(instrument.columns.Instrument_Name).label('instruments_count')

stmt=select([instruments_count, instrument.columns.Instrument_Name, instrument.columns.Cost])

stmt = stmt.group_by(instrument.columns.Instrument_Name)

results = engine.execute(stmt).fetchall()

print(results)

print(results[0].keys())

在执行查询之前打印“STMT”,也可以获得等效的SQL命令。上述查询的等效SQL是:

SELECT count(instrument."Instrument_Name") AS instruments_count, 
instrument."Instrument_Name", instrument."Cost" 
FROM instrument 
GROUP BY instrument."Instrument_Name"

使用案例和演员陈述

如果您有遇到SQL数据库的经验,那么您可能会遇到案例并演示语句。

案例语句基本上选择基于条件的数据,并且演员语句用于转换数据类型。

让我们在下面如下所示的示例中看到用例和猫语句:

# case and cast statements
instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

price_gt_250 = case([
        (instrument.columns.Cost < 250, True)
    ], else_= False)

total_pop2000 = cast(price_gt_250, Boolean)

stmt = select([instrument.columns.Instrument_Name, price_gt_250])

result = engine.execute(stmt).fetchall()
# Print the percentage
print(result)

在执行查询之前打印“STMT”,也可以获得等效的SQL命令。上述查询的等效SQL是:

SELECT instrument."Instrument_Name", 
CASE WHEN (instrument."Cost" < :Cost_1) THEN :param_1 ELSE :param_2 END AS anon_1 
FROM instrument

执行算术运算

我们可以使用SQLALCHEMY包执行算术运算。我们可以执行的基本算术运算就像加法,减法,乘法,分区和更多。

下面显示的示例演示了两个字符串列的添加​​:

customer = Table('customer', metadata, autoload=True, autoload_with=engine)

stmt = select([customer.columns.CustomerId, (customer.columns.FirstName + 
           ' ' + customer.columns.LastName).label('customer_name')])

stmt = stmt.order_by('customer_name')

stmt = stmt.limit(5)

results = engine.execute(stmt).fetchall()

for result in results:
    print(result, ' ')

在执行查询之前打印“STMT”,也可以获得等效的SQL命令。上述查询的等效SQL是:

SELECT customer."CustomerId", 
customer."FirstName" || :FirstName_1 || customer."LastName" AS customer_name 
FROM customer 
ORDER BY customer_name
LIMIT :param_1

加入桌子

桌子的加入是每个数据科学家在他的桶中需要的操作之一。作为我们感兴趣的数据,几乎没有存储在一个表中。因此,我们需要加入多个表并获取我们正在寻找的数据。

让我们看看下面显示的示例演示了两个表的加入:

instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)
artist = Table('Artist', metadata, autoload=True, autoload_with=engine)

stmt = select([instrument, artist])

stmt = stmt.select_from(
    instrument.join(artist, instrument.columns.ArtistID == artist.columns.ArtistId))

result = engine.execute(stmt).fetchall()

# Printing first two items from result set
print(result[:2])

在执行查询之前打印“STMT”,也可以获得等效的SQL命令。上述查询的等效SQL是:

SELECT instrument."InstrumentID", instrument."ArtistID", instrument."Instrument_Name", 
instrument."Cost", "Artist"."ArtistId", "Artist"."Name" 
FROM instrument 
JOIN "Artist" ON instrument."ArtistID" = "Artist"."ArtistId"

更新操作

我们还可以在数据集上执行更新操作。我们可以在表中更新单行,或者我们可以在表中更新多行。让我们来看看下面显示的一个例子:

instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = update(instrument).values(Cost = 200).where(instrument.columns.Cost < 250)

results = engine.execute(stmt)

print(results.rowcount)

# checking data after updating it
stmt = select([instrument])
results = engine.execute(stmt).fetchall()
print(results)

创建Pandas DataFrame Out结果集

这是我们对熊猫的一个非常有趣的功能。我们从SQLalchemy作为输出获得的结果集可以转换为Pandas DataSet。

是的,你听到了它!!

让我们看看下面显示的示例:

instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = select([instrument])
results = engine.execute(stmt).fetchall()

df = pd.DataFrame(results)

df.columns = results[0].keys()

print(df)

删除操作

我们可以根据条件从数据库中从表或表中删除数据。让我们看看我们可以使用Sqlalchemy包执行的不同删除操作是什么。

  1. 根据条件删除数据
  2. 删除所有数据
  3. 从数据库中删除表

根据条件删除数据

instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = delete(instrument)
stmt = stmt.where(instrument.columns.Cost < 250)

results = engine.execute(stmt)

print(results.rowcount)

删除所有数据

instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)

stmt = delete(instrument)

results = engine.execute(stmt)

print(results.rowcount)

从数据库中删除表

instrument = Table('instrument', metadata, autoload=True, autoload_with=engine)
customer = Table('customer', metadata, autoload=True, autoload_with=engine)

instrument.drop(engine)
print(instrument.exists(engine))

# Dropping all tables
metadata.drop_all(engine)
print(customer.exists(engine))

我试图在处理关系数据库时涵盖任何数据科学家在日常需求的主题。 SQLALCHEMY包必须提供更多更多。您可以始终了解更多关于摘要文档的概念 Sqlalchemy包。如果您要我解释SQLALCHEMY包的任何特定命令,请留下评论。

作者信息

Tavish Aggarwal.

网站: http://tavishaggarwal.com

Tavish Aggarwal.是一个数据科学家 在一个海德拉巴工作,具有广泛的经验,在跨电子商务,财务,医疗保健等等不同领域解决现实世界的业务问题。 他对技术充满热情,喜欢在团队中工作。