将数据从 MySQL 数据库导入包含列名的 Panda 数据框架

我从 MySQL 数据库导入数据到熊猫数据框架。下面是我正在使用的代码:

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)

当我打印数据帧时，它确实正确地表示了数据，但我的问题是，是否有可能也保留列名？下面是一个输出示例:

                          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，而是 MySQL 表中的“ First _ column”。有什么好办法吗？或者有没有比我的更有效的方法从 MySQL 导入数据到熊猫数据框架？

119523 次浏览

小开

最佳答案

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...