How to write a pandas dataframe as a table into a MySQL database?

Question

Just a quick question guys: I created a darabase in mysql (opinions):

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| opinions           |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.06 sec)

I also have a pandas dataframe df:

import pandas as pd
df = pd.read_csv('file.csv', sep='|', names=['id', 'opinions'])
df.head(3)

Out:

    id content
0   id1 'blablababla
1   id2 'blalbabla
...
n   idn 'blalbabl

Then, I would like to load df as a table (items) into opinions database. This is what I tried:

import mysql.connector
from sqlalchemy import create_engine

engine = create_engine('mysql+mysqlconnector://user:password@localhost:3306/opinions', echo=False)
df.to_sql(name='items', con=engine, if_exists = 'replace', index=False)
df

However, I got this exception:

OperationalError: (mysql.connector.errors.OperationalError) 2055: Lost connection to MySQL server at 'localhost:3306', system error: 32 Broken pipe

How should I load or write as a table df into opinions database?

your problem is with reading the database, not writing to it — Paul H
– Paul H, Commented Jun 7, 2016 at 5:36
Thanks for the help @PaulH, Could you provide an example of how to do it correctly?. — tumbleweed
– tumbleweed, Commented Jun 7, 2016 at 5:42
Maybe help PyMySQL and cnx = pymysql.connect(user='user', password='pw', host= '127.0.0.1', port=3306, db='mydb', autocommit=True) df=psql.read_sql_query('call sproc', con=cnx) — jezrael
– jezrael, Commented Jun 7, 2016 at 6:04
Did you actually create a table in the database, If not, Then the problem is not pandas its your knowledge of databases in general, I would go learn about basic database use, then tackle the above problem. If you did did create table in MySQL, show the 'create table' sql above. — Merlin
– Merlin, Commented Jun 7, 2016 at 18:22

sebastian · Accepted Answer · 2016-06-07 06:26:58Z

2

On your MySQL Server opinions is not a table, but a database (as show databases implies), which is basically a collection of tables. You can issue select statements only on tables however.

You can check which tables e.g. exist via SHOW TABLES IN opinions. It's also convenient to add the database as an argument to the connection:

config = {
    'user' : 'root',
    'passwd' : 'password',
    'host' : 'localhost',
    'raise_on_warnings' : True,
    'use_pure' : False,
    'database' : 'opinions'
    }
con = mysql.connector.connect(**config)

This results in all table references to be targeted at tables in the opinions database. A plain select on an existing table should then work:

data = pd.read_sql('SELECT * FROM <some_table_name>', con)

answered Jun 7, 2016 at 6:26

sebastian

9,71628 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tumbleweed Over a year ago

This actually worked. However, I still can not load the dataframe to the database as a table, neither insert the dataframe to the table:Empty DataFrame Columns: [id, opinions] Index: []

sebastian Over a year ago

I'm not really familiar with pandas - but did you actually retrieve any results with the read_sql call? Also: If my answer helped you out, please accept it :)

tumbleweed Over a year ago

Thanks for the help sebastian, I will accept your answer. One more thing, I tried the following:

df.to_sql(name = 'opinions2', con = con, flavor='mysql', schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)

. However when I describe it with sql the table is empty... how should I load the dataframe to the database as a new table or overwrite it to an existing table?

sebastian Over a year ago

Well if the table is empty you'll have to fill in some data first. You could e.g. create a dataframe with some arbitrary data (matching your table definition) and insert that into your database. Maybe you should also think about what you're actually trying to accomplish (reading data from sql and trying to insert it right back in doesn't make that much sense after all...)

Merlin · Accepted Answer · 2016-06-07 20:58:47Z

1

Use Pandas to read from MySQL. MySQLdb to write to database.

  mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | opinions           |
    | performance_schema |
    | sys                |
    +--------------------+

Great! For your example, replace test with opinions

mysql> use test;
Reading table information for completion of table and column names

Database changed
mysql> show tables;
+-----------------+
| Tables_in_test |
+-----------------+
| _t              |
| food            |
| food_in         |
| food_min        |
| type            |
| user            |
+-----------------+
4 rows in set (0.00 sec)

mysql> select * from test.food;
+----+-------+--------------+--------+----------+
| ID | Cat   | Item         | price  | quantity |
+----+-------+--------------+--------+----------+
|  1 | Food  | Pizza        | 2.7500 |      300 |
|  2 | Liq   | Beer         | 2.5000 |      300 |
|  4 | Food  | Sandwich     | 4.0000 |      222 |
| 12 | Food  | Soup         | 3.5000 |      100 |
+----+-------+--------------+--------+----------+
4 rows in set (0.00 sec)

So:

data = pd.read_sql('SELECT * FROM opinions', con)

Turns into:

data = pd.read_sql('SELECT * FROM opinions.MYTABLETHATIWANT', con)

I use MySQLdb to write to database. So, I can use basic python and pandas. You may also have permission problems that inhibits database/table access. Are you using root, or another user and have set the permission for user.

edited Jun 7, 2016 at 20:58

answered Jun 7, 2016 at 6:48

Merlin

25.9k44 gold badges141 silver badges213 bronze badges

6 Comments

tumbleweed Over a year ago

Thanks for the help, Could you provide an example of how to write it?.

Merlin Over a year ago

@ml_student write what?

tumbleweed Over a year ago

A pandas dataframe df, assuming I all ready have one... Actually I allready have a dataframe with data... I just omitted it for space issues...

Merlin Over a year ago

You are still not telling me what you would like to help you write? Update the question above

Merlin Over a year ago

I see you are using 'root' on MySQL. Stop it!-- create a user and use that user. Did you read the docs on pandas writing to database. I dont use pandas to write to MySQL. Sorry, Not much help there --

|

Collectives™ on Stack Overflow

How to write a pandas dataframe as a table into a MySQL database?

2 Answers 2

4 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related