6.4 数据库交互

在商业场景下，大多数数据可能不是存储在文本或Excel文件中。基于SQL的关系型数据库（如SQL Server、PostgreSQL和MySQL等）使用非常广泛，其它一些数据库也很流行。数据库的选择通常取决于性能、数据完整性以及应用程序的伸缩性需求。

将数据从SQL加载到DataFrame的过程很简单，此外pandas还有一些能够简化该过程的函数。例如，我将使用SQLite数据库（通过Python内置的sqlite3驱动器）：

  1. In [121]: import sqlite3
  3. In [122]: query = """
  4.    .....: CREATE TABLE test
  5.    .....: (a VARCHAR(20), b VARCHAR(20),
  6.    .....:  c REAL,        d INTEGER
  7.    .....: );"""
  9. In [123]: con = sqlite3.connect('mydata.sqlite')
  11. In [124]: con.execute(query)
  12. Out[124]: <sqlite3.Cursor at 0x7f6b12a50f10>
  14. In [125]: con.commit()

然后插入几行数据：

  1. In [126]: data = [('Atlanta', 'Georgia', 1.25, 6),
  2.    .....:         ('Tallahassee', 'Florida', 2.6, 3),
  3.    .....:         ('Sacramento', 'California', 1.7, 5)]
  5. In [127]: stmt = "INSERT INTO test VALUES(?, ?, ?, ?)"
  7. In [128]: con.executemany(stmt, data)
  8. Out[128]: <sqlite3.Cursor at 0x7f6b15c66ce0>

从表中选取数据时，大部分Python SQL驱动器（PyODBC、psycopg2、MySQLdb、pymssql等）都会返回一个元组列表：

  1. In [130]: cursor = con.execute('select * from test')
  3. In [131]: rows = cursor.fetchall()
  5. In [132]: rows
  6. Out[132]: 
  7. [('Atlanta', 'Georgia', 1.25, 6),
  8.  ('Tallahassee', 'Florida', 2.6, 3),
  9.  ('Sacramento', 'California', 1.7, 5)]

你可以将这个元组列表传给DataFrame构造器，但还需要列名（位于光标的description属性中）：

  1. In [133]: cursor.description
  2. Out[133]: 
  3. (('a', None, None, None, None, None, None),
  4.  ('b', None, None, None, None, None, None),
  5.  ('c', None, None, None, None, None, None),
  6.  ('d', None, None, None, None, None, None))
  8. In [134]: pd.DataFrame(rows, columns=[x[0] for x in cursor.description])
  9. Out[134]: 
  10.              a           b     c  d
  11. 0      Atlanta     Georgia  1.25  6
  12. 1  Tallahassee     Florida  2.60  3
  13. 2   Sacramento  California  1.70  5

这种数据规整操作相当多，你肯定不想每查一次数据库就重写一次。SQLAlchemy项目是一个流行的Python SQL工具，它抽象出了SQL数据库中的许多常见差异。pandas有一个read_sql函数，可以让你轻松的从SQLAlchemy连接读取数据。这里，我们用SQLAlchemy连接SQLite数据库，并从之前创建的表读取数据：

  1. In [135]: import sqlalchemy as sqla
  3. In [136]: db = sqla.create_engine('sqlite:///mydata.sqlite')
  5. In [137]: pd.read_sql('select * from test', db)
  6. Out[137]: 
  7.              a           b     c  d
  8. 0      Atlanta     Georgia  1.25  6
  9. 1  Tallahassee     Florida  2.60  3
  10. 2   Sacramento  California  1.70  5