SQLite 是一个数据库引擎,使用它能方便地存储和处理关系型数据。它和 cvs 格式很相似, 在我们开始之前,让我们先快速检阅下我们之后要处理的数据。我们看到的是航空公司的航班数据,它包含了有关航空公司的信息,机场名称和往来各个机场的航线名称。每一条航线代表着有一架航班重复往返于目的地和始发地的机场。 所有的数据都存在一个叫做 这里有两行来自
就如你在上表中看到的,每一行都是一个不同的航空公司,每一列是这个航空公司的属性,例如 这里有两行来自
就如你所看到的,每一行都对应了一个机场,并且包含了机场所在地的信息。每一个机场也有一个独一无二的 这里有两行来自
每一条航线包含有一个 至此,我们知道了需要处理的是什么数据,让我们先从连接数据库和执行一条查询指令开始。 使用
|
index | id | name | alias | iata | icao | callsign | country | active | |
---|---|---|---|---|---|---|---|---|---|
0 | 0 | 1 | Private flight | \N | - | None | None | None | Y |
1 | 1 | 2 | 135 Airways | \N | None | GNL | GENERAL | United States | N |
2 | 2 | 3 | 1Time Airline | \N | 1T | RNX | NEXTIME | South Africa | Y |
3 | 3 | 4 | 2 Sqn No 1 Elementary Flying Training School | \N | None | WYT | None | United Kingdom | N |
4 | 4 | 5 | 213 Flight Unit | \N | None | TFU | None | Russia | N |
如你所见,我们得到了一个有着清晰格式的 DataFrame
作为结果。我们能方便地操作这些列:
df["country"]
0 None
1 United States
2 South Africa
3 United Kingdom
4 Russia
Name: country, dtype: object
强烈建议尽可能使用 read_sql_query
方法。
至此,我们已经知道如何把查询结果读取到 pandas
DataFrames
中,我们能在世界地图上创建每一个航空公司的航线图。首先,我们需要查询这些数据。查询方式如下:
float
类型。DataFrame
中,并把他们存在变量 routes
中。 routes = pd.read_sql_query("""
select cast(sa.longitude as float) as source_lon,
cast(sa.latitude as float) as source_lat,
cast(da.longitude as float) as dest_lon,
cast(da.latitude as float) as dest_lat
from routes
inner join airports sa on
sa.id = routes.source_id
inner join airports da on
da.id = routes.dest_id;
""",
conn)
之后,我们开始创建地图:
m = Basemap(projection='merc',llcrnrlat=-80,urcrnrlat=80,llcrnrlon=-180,urcrnrlon=180,lat_ts=20,resolution='c')
m.drawcoastlines()
首先,我们开始遍历最先的 3000
行数据,并绘制他们。代码如下:
3000
行数据遍历存储到 routes
中。 for name, row in routes[:3000].iterrows():
if abs(row["source_lon"] - row["dest_lon"]) < 90:
# Draw a great circle between source and dest airports.
m.drawgreatcircle(
row["source_lon"],
row["source_lat"],
row["dest_lon"],
row["dest_lat"],
linewidth=1,
color='b'
)
最后,我们完成了这个地图:
比起直接使用 sqlite3
处理这些原始检索数据,当我们使用 pandas
把所有的 SQL
检索到的数据读入一个 DataFrame
中是一个非常有效的方法。
至此,我们理解了如何检索数据库的内容,接下来,让我们看看如何对这些数据进行修改。
Dataquest
学习数据科学我们可以使用 sqlite3
开发包来修改一个 SQLite
数据库,比如插入,更新或者删除某些行内容。创建数据库的连接和查询一个数据表的方法一样,所以我们会跳过这个部分。
Python
插入行内容为了插入一行数据,我们需要写一条 INSERT
查询指令。以下代码会对 airlines
表中新增加一行数据。我们指定 9
个需要被添加的数据,对应着 airlines
表格的每一列。这会为这个表增加一行新数据。
cur = conn.cursor()
cur.execute("insert into airlines values (6048, 19846, 'Test flight', '', '', null, null, null, 'Y')")
如果你尝试对这个表格进行检索,你其实还不能看到这条新的数据。然而,你会看到一个名字为 flights.db-journal
的文件被创建了。在你准备好把它 commit
到主数据库 flights.db
之前,flights.db-journal
会代为存储新增加的行数据。
SQLite
并不会写入数据库直到你提交了一个 transaction。每一个 transaction
包含了
1 个或者多个查询指令,它能把所有新的变化一次性提交给数据库。这样的设计使得从意外的修改或错误中恢复变得更加容易。Transaction
允许你执行多个查询指令,最终这些结果都会修改数据库。这确保了如果有一条查询指令失败了,数据库不会只有部分内容被更新。
举个例子来说,如果你有两张表,一张表包含了对银行账户收取的费用(charges
),另一张表包含了账户在银行内存款的余额(balances
)。假定有一位银行客户
Roberto,他想给姐妹 Luisa 转 $50 美元。为了完成这笔交易,银行应该需要执行以下几步:
charges
中新增加一行,描述有
$50 美元正要从 Roberto 的账户转到 Luisa。balances
表中的数据内容,并且移除
$50 美元。balances
表中的数据内容,并且增加
$50 美元。如此来说,为了更新所有的表格需要三次单独的 SQL
查询指令。如果一个查询指令失败了,我们的数据库就会被破损的数据卡住。举例来说,如果前两条指令成功运行了,第三条失败了,Roberto
将会损失他的钱,但是 Luisa 也不会获得这笔钱。Transactions
意味着主数据库不会被更新除非所有的查询指令都被成功执行。这避免了系统进入错误的状态,用户可能会丢失他们的存款。
默认情况下,当你执行了任何会修改数据库的查询指令时,sqlite3
会打开一个 transaction
。你能在 这里 了解更多。我们能提交 transaction
,也能使用 commit 方法对 airlines
表新增加内容:
conn.commit()
现在,当我们检索 flights.db
的时候,我们将看到这个额外的数据,它包含我们的测试航班。
pd.read_sql_query("select * from airlines where id=19846;", conn)
index | id | name | alias | iata | icao | callsign | country | active | |
---|---|---|---|---|---|---|---|---|---|
0 | 1 | 19846 | Test flight | None | None | None | Y |
在最后那条查询指令中,我们把固定值插入到所需要的数据库中。多数情况下,当你想插入数据到数据库中的时候,它不会是一些固定值,它应该是一些你想传入方法的动态值。这些动态值可能来自于下载得到的数据,或者来自于用户的输入。
当操作动态值的时候,有些人尝试用 Python
的格式化字符串来插入这些值:
cur = conn.cursor()
name = "Test Flight"
cur.execute("insert into airlines values (6049, 19847, {0}, '', '', null, null, null, 'Y')".format(name))
conn.commit()
你应该避免这样做!通过 Python
的格式化字符串插入数值会让你的程序更加容易受到 SQL
注入 的攻击。幸运的是,sqlite3
有一个更加直接的方式来注入动态值,而不是依赖格式化的字符串。
cur = conn.cursor()
values = ('Test Flight', 'Y')
cur.execute("insert into airlines values (6049, 19847, ?, '', '', null, null, null, ?)", values)
conn.commit()
任何在查询指令中以 ?
形式出现的数值都会被 values
中的数值替代。第一个 ?
将会被 values
中的第一个数值替代,第二个也是,其他以此类推。这个方式对任何形式的查询指令都有用。如此就创建了一个 SQLite
带参数形式的查询指令,它有效避免了 SQLite
注入
的问题。
通过使用 execute
方法,我们可以修改在 SQLite
表格中某些行数据的内容:
cur = conn.cursor()
values = ('USA', 19847)
cur.execute("update airlines set country=? where id=?", values)
conn.commit()
之后,我们能验证更新的内容:
pd.read_sql_query("select * from airlines where id=19847;", conn)
index | id | name | alias | iata | icao | callsign | country | active | |
---|---|---|---|---|---|---|---|---|---|
0 | 6049 | 19847 | Test Flight | None | None | USA | Y |
最后,通过使用 execute
方法,我们能删除数据库中的某些行数据内容:
cur = conn.cursor()
values = (19847, )
cur.execute("delete from airlines where id=?", values)
conn.commit()
之后,通过确认没有相匹配的查询内容,我们能验证这些行数据内容确实被删除了:
pd.read_sql_query("select * from airlines where id=19847;", conn)
我们可以通过执行一条 SQLite
查询指令来创建表。我们能创建一个表,它能展示每天在某一条航线上的航班,使用以下几列:
id
—
整型departure
—
日期型,表示飞机离开机场的时间arrival
—
日期型,表示飞机到达目的地的时间number
—
文本型,飞机航班号route_id
—
整型,正在飞行的航线号 cur = conn.cursor()
cur.execute("create table daily_flights (id integer, departure date, arrival date, number text, route_id integer)")
conn.commit()
一旦我们创建了这个表,我们就能对这个表插入数据:
cur.execute("insert into daily_flights values (1, '2016-09-28 0:00', '2016-09-28 12:00', 'T1', 1)")
conn.commit()
当我们对该表执行查询指令的时候,我们就能看到这些行数据内容:
pd.read_sql_query("select * from daily_flights;", conn)
id | departure | arrival | number | route_id | |
---|---|---|---|---|---|
0 | 1 | 2016-09-28 0:00 | 2016-09-28 12:00 | T1 | 1 |
pandas
创建表pandas
包提供给我们一个更加快捷地创建表格的方法。我们只需要先创建一个 DataFrame
,之后把它导出到一个 SQL
表格内。首先,我们将创建一个 DataFrame
:
from datetime import datetime
df = pd.DataFrame(
[[1, datetime(2016, 9, 29, 0, 0) , datetime(2016, 9, 29, 12, 0), 'T1', 1]],
columns=["id", "departure", "arrival", "number", "route_id"]
)
之后,我们就能调用 to_sql 方法,它将 df
转化成一个数据库中的数据表。我们把参数 keep_exists
设定成 replace
,为了删除并且替换数据库中任何已存在的 daily_flights
:
df.to_sql("daily_flights", conn, if_exists="replace")
通过对数据库执行查询指令,我们能验证是否正常工作了:
pd.read_sql_query("select * from daily_flights;", conn)
index | id | departure | arrival | number | route_id | |
---|---|---|---|---|---|---|
0 | 0 | 1 | 2016-09-29 00:00:00 | 2016-09-29 12:00:00 | T1 | 1 |
Pandas
修改数据表对于现实世界中的数据科学来说,最难处理的部分就是那些几乎经常每秒都不停变换着的数据。拿 aireline
这个例子来说,我们可能决定在 airelines
表中新增加一个 airplanes
的属性,它显示出每一个航空公司拥有多少架飞机。幸运的是,在 SQLite
中有一个方式能修改表并且添加这些列:
cur.execute("alter table airlines add column airplanes integer;")
请注意,我们不需要调用 commit
方法
—— alter table
查询指令会被立刻执行,并且不会发生在一个 transaction
中。现在,我们能查询并且看到这些额外的列:
pd.read_sql_query("select * from airlines limit 1;", conn)
index | id | name | alias | iata | icao | callsign | country | active | airplanes | |
---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 1 | Private flight | \N | - | None | None | None | Y | None |
你可能注意到了,在 SQLite
中所有的列都被设值成了 null
(在 Python
中被转化成了 None
),因为这些列还没有任何数值。
Pandas
修改表也可以使用 Pandas
通过把表导出成 DataFrame
去修改表格的内容,仅需要对 DataFrame
进行修改,之后把这个 DataFrame
导出成一个表:
df = pd.read_sql("select * from daily_flights", conn)
df["delay_minutes"] = None
df.to_sql("daily_flights", conn, if_exists="replace")
以上代码将会对 daily_flight
表增加一个叫做 delay_minutes
的列项。
|