93

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

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?

MaxU - stand with Ukraine
  • 205,989
  • 36
  • 386
  • 419
vFlav
  • 1,099
  • 1
  • 8
  • 9
  • 1
    why don't you use [pd.read_sql()](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html)? – MaxU - stand with Ukraine Jun 09 '16 at 15:31
  • 19
    The question here is related to _MySQL db_ - and not _SQLalchemy_ - as asked in the [duplicate](https://stackoverflow.com/questions/12047193/how-to-convert-sql-query-result-to-pandas-data-structure). `pd.read_sql()` does not support mysql connection. This question should not be marked as a duplicate. To answer the query: `df = pd.DataFrame(table_rows, columns=db_cursor.column_names)` will do what is asked. – kneewarp Apr 08 '18 at 10:15
  • 1
    @kneewarp you should post this as an answer. The accepted answer will not work with a MySQL connection, which the OP requested. – kainC Jul 21 '18 at 21:45
  • As of 2022 we see problems to install sqlalchemy+pymysql+mysqlclient on MacOS when using pandas.read_sql(). You must manually brew mysql or mysqlclient on your OS first. So I think using mysql-connector-python is a better way. – Z.Wei Dec 20 '22 at 15:34

1 Answers1

193

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

ssoler
  • 4,884
  • 4
  • 32
  • 33
MaxU - stand with Ukraine
  • 205,989
  • 36
  • 386
  • 419
  • 2
    In my case this worked, but couldn't query the table directly anymore until I closed the connection: `db_connection.close()` – HaMi Aug 14 '19 at 18:11
  • according to https://stackoverflow.com/questions/42118750/loading-mysql-table-into-python-takes-a-very-long-time-compared-to-r. I've decided to use MySQLdb instead, how to accomplish this in MySQLdb? – chaikov Nov 08 '19 at 03:44
  • 1
    One can also use mysql.connect to connect the database (instead of importing two packages sqlalchemy & pymysql) and then can use pd.read_sql function – Yogesh Awdhut Gadade Jan 22 '20 at 04:50
  • 1
    db_connection.close() gives a error and the mysql server cannot be connected – Aman Khandelwal Feb 04 '20 at 04:59
  • 3
    to close connection: >>> db_connection.dispose() – Brad123 Feb 23 '20 at 23:50
  • does this not work anymore? – Raksha Dec 21 '22 at 21:13
  • You could also use python's `mysql`'s db connection rather than `sqlalchemy` as `db_connection = mysql.connect(host, database, user, password, port)` then `df = pd.read_sql(query, con=db_connection)` – Perl Del Rey Jan 04 '23 at 09:59