pandas的DataFrame如何与数据库交互

pandas可以从多种类型的文件中读取数据，csv, Excel，除了这些意外，还可以从数据库中读取数据，也能够将数据写入到数据库中，本文将和你一起探讨pandas如何与数据库交互

1. 准备数据

首先，要准备好数据，为了方便，我使用sqlite作为数据库

import sqlite3
conn = sqlite3.connect('stu.db')

def create_table():
    cursor = conn.cursor()

    table_sql = """
    create table user(
      id INTEGER PRIMARY KEY autoincrement NOT NULL ,
      name text NOT NULL,
      age INTEGER NOT NULL,
      score INTEGER NOT NULL
    )
    """
    cursor.execute(table_sql)
    conn.commit()  # 一定要提交,否则不会执行sql

def insert_table():
    cursor = conn.cursor()

    sql_lst = [
        "insert into user(name, age, score)values('lili', 18, 560)",
        "insert into user(name, age, score)values('poly', 19, 600)",
        "insert into user(name, age, score)values('lilei', 30, 606)"
    ]
    for sql in sql_lst:
        cursor.execute(sql)
        conn.commit()


def select(table):
    sql = "select * from {table}".format(table=table)
    cursor = conn.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()  # 获取全部数据
    for row in rows:
        print(row)


create_table()
insert_table()
select('user')

三个函数分别实现创建数据库，写入数据，查询数据，select函数输出结果

(1, 'lili', 18, 560)
(2, 'poly', 19, 600)
(3, 'lilei', 30, 606)

2. pandas从数据库中读取数据

与数据库交互，使用pandas.io模块

from pandas.io import sql
import sqlite3

conn = sqlite3.connect('stu.db')
query = "SELECT * FROM user;"

results = sql.read_sql(query, con=conn)
print(results.head())

read_sql方法执行执行了查询操作并将返回结果转换为DataFrame，程序输出结果

   id   name  age  score
0   1   lili   18    560
1   2   poly   19    600
2   3  lilei   30    606

3. DataFrame写入数据库

import sqlite3
import pandas as pd

conn = sqlite3.connect('stu.db')

data = [
    {'id': 4, 'name': '小明', 'age': 19, 'score': 590},
    {'id': 5, 'name': '小红', 'age': 20, 'score': 620},
]

df = pd.DataFrame(data)
df.to_sql('student', con=conn, index=False)

select('student')

df里的数据，通过to_sql写入到一张新表student中

to_sql方法有一个if_exists参数，有三个可选值

fail 如果表已经存在，则写入失败，默认fail
replace 如果表存在，则删除原表，重新写入
如果表存在，则新增数据

上面的例子中，我将数据写入到了新表，如果想写入表user中，则可以这样操作

import sqlite3
import pandas as pd

conn = sqlite3.connect('stu.db')

data = [
    {'id': 4, 'name': '小明', 'age': 19, 'score': 590},
    {'id': 5, 'name': '小红', 'age': 20, 'score': 620},
]

df = pd.DataFrame(data)
df.to_sql('user', con=conn, if_exists='append', index=False)

select('user')