35

From the source of to_sql, I can see that it gets mapped to an Meta Data object meta = MetaData(con, schema=schema). However, I can't find SQLAlchemy docs that tell me how to define the Schema for MySQL

How do I specify the schema string ?

van
  • 74,297
  • 13
  • 168
  • 171
HackToHell
  • 2,223
  • 5
  • 29
  • 44
  • See [here](http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html#specifying-the-schema-name) for the docs. But as far as I know, MySQL does not support "schema's" in the sense of a "subcollection of tables" ("schema" is a but dubious word with multiple meanings). You will have to clarify your question with what exactly you mean with "how to define the Schema" – joris Jun 23 '15 at 09:16
  • Oh, what I mean is, I want to define the type of each column, their properties(index,key) etc – HackToHell Jun 23 '15 at 09:20
  • 6
    This is done automatically by `to_sql`. If you want to overwrite the used column types, see the [`dtype`](http://pandas.pydata.org/pandas-docs/stable/io.html#sql-data-types) keyword argument. And if you want to customize this even more, probably best to make the sql table manually, and then use `to_sql(...., if_exists='append')` to append the data to that created table. – joris Jun 23 '15 at 09:22
  • That's actually how I am doing it right now, using the engine to send raw sql queries. Figured it might be easier using schema thingy. What does the schema variable define anyway ? – HackToHell Jun 23 '15 at 09:29
  • See the link I posted. Most other databases (but not MySQL) have a 'schema' concept of a group of database tables that eg are owned/accessible by a specific user. See eg http://www.postgresql.org/docs/9.1/static/ddl-schemas.html – joris Jun 23 '15 at 09:43
  • [The highest voted answer](https://stackoverflow.com/a/40770849/6560549) to [this question](https://stackoverflow.com/q/30867390/6560549) suggests altering the table definition after pandas creates it. – SuperShoot Aug 27 '19 at 08:16

3 Answers3

12

The schema parameter in to_sql is confusing as the word "schema" means something different from the general meaning of "table definitions". In some SQL flavors, notably postgresql, a schema is effectively a namespace for a set of tables.

For example, you might have two schemas, one called test and one called prod. Each might contain a table called user_rankings generated in pandas and written using the to_sql command. You would specify the test schema when working on improvements to user rankings. When you are ready to deploy the new rankings, you would write to the prod schema.

As others have mentioned, when you call to_sql the table definition is generated from the type information for each column in the dataframe. If the table already exists in the database with exactly the same structure, you can use the append option to add new data to the table.

Eilif Mikkelsen
  • 326
  • 3
  • 7
1
DataFrame.to_sql(self, name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

Just use schema parameter. But note that schema is not odbc driver.

Vasin Yuriy
  • 484
  • 5
  • 13
-6

Starting from the Dialects page of the SQLAlchemy documentation, select documentation page of your dialect and search for create_engine to find example on how to create it.

Even more concise overview you can get on Engine Configuration page for all supported dialects.

Verbatim extract for mysql:

# default
engine = create_engine('mysql://scott:tiger@localhost/foo')

# mysql-python
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')

# MySQL-connector-python
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')

# OurSQL
engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')

Then pass this engine to the to_sql(...) of pandas' DataFrame.

van
  • 74,297
  • 13
  • 168
  • 171