使用Pandas导入多个SQL表 [英] Importing multiple SQL tables using pandas
问题描述
我有一个包含多个表的数据库,并且我试图将每个表作为pandas数据框导入.我可以对单个表执行以下操作:
I have a database that contains multiple tables, and I am trying to import each table as a pandas dataframe. I can do this for a single table as follows:
import pandas as pd
import pandas.io.sql as psql
import pypyodbc
conn = pypyodbc.connect("DRIVER={SQL Server};\
SERVER=serveraddress;\
UID=uid;\
PWD=pwd;\
DATABASE=db")
df1 = psql.read_frame('SELECT * FROM dbo.table1', conn)
数据库中的表数将改变,并且我希望能够随时将每个表导入其自己的数据框中.如何将所有这些桌子放入大熊猫中?
The number of tables in the database will change, and at any time I would like to be able to import each table into its own dataframe. How can I get all of these tables into pandas?
推荐答案
根据您的SQL Server,您可以检查数据库中的表.
Depending on your SQL server, you can inspect the tables in a database.
例如:
tables_df = pd.read_sql('SELECT table_name FROM database_name', conn)
现在您的表名可以作为pandas数据框访问,您只需解析一下即可:
Now your table names are accessible as a pandas data frame, you just need to parse it out:
table_name_list = tables_df.table_name
select_template = 'SELECT * FROM {table_name}'
frames_dict = {}
for tname in table_name_list:
query = select_template.format(table_name = tname)
frames_dict[tname] = pd.read_sql(query, conn)
您的词典frames_dict
包含所有以table_name为键的数据框
Your dictionary frames_dict
contains all the dataframes with the table_name as the key
这篇关于使用Pandas导入多个SQL表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!