pandas 从数据库表中获取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28080244/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting data from table in database
提问by Gabriel L'Heureux
I want to extract data from a postgresqldatabase and use that data (in a dataframe format) in a script. Here's my initial try:
我想从postgresql数据库中提取数据并在脚本中使用该数据（以数据帧格式）。这是我的初步尝试：
from pandas import DataFrame
import psycopg2
conn = psycopg2.connect(host=host_address, database=name_of_database, user=user_name, password=user_password)
cur = conn.cursor()
cur.execute("SELECT * FROM %s;" % name_of_table)
the_data = cur.fetchall()
colnames = [desc[0] for desc in cur.description]
the_frame = DataFrame(the_data)
the_frame.columns = colnames
cur.close()
conn.close()
Note: I am aware that I should not use "string parameters interpolation (%) to pass variables to a SQL query string", but this works great for me as it is.
注意：我知道我不应该使用“字符串参数插值 (%) 将变量传递给 SQL 查询字符串”，但这对我来说非常有用。
Would there be a more direct approach to this?
有没有更直接的方法来解决这个问题？
Edit: Here's what I used from the selected answer:
编辑：这是我从所选答案中使用的内容：
import pandas as pd
import sqlalchemy as sq
engine = sq.create_engine("postgresql+psycopg2://username:password@host:port/database")
the_frame = pd.read_sql_table(name_of_table, engine)
回答by John Zwinck
Pandas can load data from Postgres directly:
Pandas 可以直接从 Postgres 加载数据：
import psycopg2
import pandas.io.sql as pdsql
conn = psycopg2.connect(...)
the_frame = pdsql.read_frame("SELECT * FROM %s;" % name_of_table, conn)
If you have a recent pandas (>=0.14), you should use read_sql_query/table(read_frameis deprecated) with an sqlalchemy engine:
如果您有最近的 Pandas (>=0.14)，您应该将read_sql_query/table(read_frame已弃用) 与 sqlalchemy 引擎一起使用：
import pandas as pd
import sqlalchemy
import psycopg2
engine = sqlalchemy.create_engine("postgresql+psycopg2://...")
the_frame = pd.read_sql_query("SELECT * FROM %s;" % name_of_table, engine)
the_frame = pd.read_sql_table(name_of_table, engine)
回答by DataByDavid
Here is an alternate method:
这是一种替代方法：
# run sql code
result = conn.execute(sql)
# Insert to a dataframe
df = DataFrame(data=list(result), columns=result.keys())
