将数据从 MySQL 数据库导入包含列名的 Pandas 数据框 [英] Importing data from a MySQL database into a Pandas data frame including column names

问题描述

我正在将数据从 MySQL 数据库导入 Pandas 数据框.以下摘录是我正在使用的代码:

I am importing data from a MySQL database into a Pandas data frame. The following excerpt is the code that I am using:

import mysql.connector as sql
import pandas as pd

db_connection = sql.connect(host='hostname', database='db_name', user='username', password='password')
db_cursor = db_connection.cursor()
db_cursor.execute('SELECT * FROM table_name')

table_rows = db_cursor.fetchall()

df = pd.DataFrame(table_rows)

当我打印数据框时，它确实正确地表示了数据，但我的问题是，是否也可以保留列名?这是一个示例输出:

When I print the data frame it does properly represent the data but my question is, is it possible to also keep the column names? Here is an example output:

                          0   1   2     3     4     5     6     7     8
0  :ID[giA0CqQcx+(9kbuSKV== NaN NaN  None  None  None  None  None  None
1  lXB+jIS)DN!CXmj>0(P8^]== NaN NaN  None  None  None  None  None  None   
2  lXB+jIS)DN!CXmj>0(P8^]== NaN NaN  None  None  None  None  None  None   
3  lXB+jIS)DN!CXmj>0(P8^]== NaN NaN  None  None  None  None  None  None   
4  lXB+jIS)DN!CXmj>0(P8^]== NaN NaN  None  None  None  None  None  None   

我想做的是保留列名，它将替换熊猫列索引.例如，列名不是 0，而是:First_column"，如 MySQL 表中那样.有什么好的方法可以解决这个问题吗?或者有没有比我的更有效的方法将数据从 MySQL 导入 Pandas 数据框?

What I would like to do is keep the column name, which would replace the pandas column indexes. For example, instead of having 0, the column name would be: "First_column" as in the MySQL table. Is there a good way to go about this? or is there a more efficient approach of importing data from MySQL into a Pandas data frame than mine?

推荐答案

IMO 使用 Pandas 从 MySQL 服务器读取数据会更有效:

IMO it would be much more efficient to use pandas for reading data from your MySQL server:

from sqlalchemy import create_engine
import pandas as pd

db_connection_str = 'mysql+pymysql://mysql_user:mysql_password@mysql_host/mysql_db'
db_connection = create_engine(db_connection_str)

df = pd.read_sql('SELECT * FROM table_name', con=db_connection)

这也应该注意列名...

this should also take care of column names...

这篇关于将数据从 MySQL 数据库导入包含列名的 Pandas 数据框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！