Python 将数据从 MySQL 数据库导入包含列名的 Pandas 数据框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37730243/
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
Importing data from a MySQL database into a Pandas data frame including column names
提问by vFlav
I am importing data from a MySQL database into a Pandas data frame. The following excerpt is the code that I am using:
我正在将数据从 MySQL 数据库导入到 Pandas 数据框中。以下摘录是我正在使用的代码：
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
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?
我想要做的是保留列名，它将替换熊猫列索引。例如，列名不是 0，而是：“First_column”，如 MySQL 表中那样。有什么好的方法可以解决这个问题吗？或者是否有比我的更有效的方法将数据从 MySQL 导入 Pandas 数据框？
回答by MaxU
IMO it would be much more efficient to use pandas for reading data from your MySQL server:
IMO 使用 Pandas 从 MySQL 服务器读取数据会更有效：
from sqlalchemy import create_engine
import pymysql
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...
这也应该照顾列名......
