第九章

原文:Chapter 9

译者:飞龙

协议:CC BY-NC-SA 4.0

  1. import pandas as pd
  2. import sqlite3

到目前为止,我们只涉及从 CSV 文件中读取数据。 这是一个存储数据的常见方式,但有很多其它方式! Pandas 可以从 HTML,JSON,SQL,Excel(!!!),HDF5,Stata 和其他一些东西中读取数据。 在本章中,我们将讨论从 SQL 数据库读取数据。

您可以使用pd.read_sql函数从 SQL 数据库读取数据。 read_sql将自动将 SQL 列名转换为DataFrame列名。

read_sql需要 2 个参数:SELECT语句和数据库连接对象。 这是极好的,因为它意味着你可以从任何种类的 SQL 数据库读取 - 无论是 MySQL,SQLite,PostgreSQL 或其他东西。

此示例从 SQLite 数据库读取,但任何其他数据库将以相同的方式工作。

  1. con = sqlite3.connect("../data/weather_2012.sqlite")
  2. df = pd.read_sql("SELECT * from weather_2012 LIMIT 3", con)
  3. df
id date_time temp
0 1 2012-01-01 00:00:00 -1.8
1 2 2012-01-01 01:00:00 -1.8
2 3 2012-01-01 02:00:00 -1.8

read_sql不会自动将主键(id)设置为DataFrame的索引。 你可以通过向read_sql添加一个index_col参数来实现。

如果你大量使用read_csv,你可能已经看到它有一个index_col参数。 这个行为是一样的。

  1. df = pd.read_sql("SELECT * from weather_2012 LIMIT 3", con, index_col='id')
  2. df
date_time temp
id
1 2012-01-01 00:00:00 -1.8
2 2012-01-01 01:00:00 -1.8
3 2012-01-01 02:00:00 -1.8

如果希望DataFrame由多个列索引,可以将列的列表提供给index_col

  1. df = pd.read_sql("SELECT * from weather_2012 LIMIT 3", con,
  2. index_col=['id', 'date_time'])
  3. df
temp
id date_time
1 2012-01-01 00:00:00 -1.8
2 2012-01-01 01:00:00 -1.8
3 2012-01-01 02:00:00 -1.8

9.2 写入 SQLite 数据库

Pandas 拥有write_frame函数,它从DataFrame创建一个数据库表。 现在这只适用于 SQLite 数据库。 让我们使用它,来将我们的 2012 天气数据转换为 SQL。

你会注意到这个函数在pd.io.sql中。 在pd.io中有很多有用的函数,用于读取和写入各种类型的数据,值得花一些时间来探索它们。 (请参阅文档!

  1. weather_df = pd.read_csv('../data/weather_2012.csv')
  2. con = sqlite3.connect("../data/test_db.sqlite")
  3. con.execute("DROP TABLE IF EXISTS weather_2012")
  4. weather_df.to_sql("weather_2012", con)

我们现在可以从test_db.sqlite中的weather_2012表中读取数据,我们看到我们得到了相同的数据:

  1. con = sqlite3.connect("../data/test_db.sqlite")
  2. df = pd.read_sql("SELECT * from weather_2012 LIMIT 3", con)
  3. df
index Date/Time Temp (C) Dew Point Temp (C) Rel Hum (%) Wind Spd (km/h) Visibility (km) Stn Press (kPa) Weather
0 0 2012-01-01 00:00:00 -1.8 -3.9 86 4 8 101.24 Fog
1 1 2012-01-01 01:00:00 -1.8 -3.7 87 4 8 101.24 Fog
2 2 2012-01-01 02:00:00 -1.8 -3.4 89 7 4 101.26 Freezing Drizzle,Fog

在数据库中保存数据的好处在于,可以执行任意的 SQL 查询。 这非常酷,特别是如果你更熟悉 SQL 的情况下。 以下是Weather列排序的示例:

index Date/Time Temp (C) Dew Point Temp (C) Rel Hum (%) Wind Spd (km/h) Visibility (km) Stn Press (kPa) Weather
0 67 2012-01-03 19:00:00 -16.9 -24.8 50 24 25 101.74 Clear
1 114 2012-01-05 18:00:00 -7.1 -14.4 56 11 25 100.71 Clear
2 115 2012-01-05 19:00:00 -9.2 -15.4 61 7 25 100.80 Clear

如果你有一个 PostgreSQL 数据库或 MySQL 数据库,从它读取的工作方式与从 SQLite 数据库读取完全相同。 使用psycopg2.connect()MySQLdb.connect()创建连接,然后使用

  1. pd.read_sql("SELECT whatever from your_table", con)

9.3 连接到其它类型的数据库

为了连接到 MySQL 数据库:

注:为了使其正常工作,你需要拥有 MySQL/PostgreSQL 数据库,并带有正确的localhost,数据库名称,以及其他。

  1. import MySQLdb con = MySQLdb.connect(host="localhost", db="test")

为了连接到 PostgreSQL 数据库:

  1. import psycopg2 con = psycopg2.connect(host="localhost")