4

I am currently migrating an existing Python program that uses SQLAlchemy, but almost exclusively via using the SQLAlchemy engines for use in Pandas dataframes (e.g., pandas to_sql). Currently the connection strings used in creating the engine are those of another database (namely MySQL), and accessing databases (analogous to Postgres schemas) is easily done. How would I best specify the schema (and not merely the Postgres database which contains them) within a given engine?

Edit: Someone seems to have labeled this question as being a duplicate. It is not. I am not looking to auto-generate queries by using Sessions; I am looking to create connection strings in order to create SQLAlchemy engines for use in Pandas' to_sql function.

Dread Pirate Roberts
  • 479
  • 1
  • 6
  • 7
  • Ah well, my bad for duplicating this incorrectly, though you were asking a quite similar question. The real answer is: you connect to a database, not a schema, so you don't pass it in a connection string. But: [`DataFrame.to_sql()`](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html) takes the "default" `schema` to use as a keyword argument, if the underlying DB supports schemas. Your database - schema analog is misleading. Schemas separate objects to namespaces *in a database*, and a user can access all objects in different schemas in a DB (given perms). – Ilja Everilä Apr 07 '17 at 09:25
  • Also, have you considered using a preconnected engine instead of a connection url string as the `con` argument to `to_sql()`. You'd reap the benefits of connection pooling etc. between calls. Also if using an engine instance instead of connection strings, the linked duplicate is actually valid, since you could then for example implement the connection event listener that sets the search path. – Ilja Everilä Apr 07 '17 at 09:32
  • So why is this not unmarked as duplicate? Can you do this please and let's post an answer. Currently cleanest way to my knowledge is to issue sql statement "set search_path=your_schema_name" withing your connection before making queries. – Anatoly Alekseev Mar 28 '19 at 15:44

1 Answers1

1

You can use blaze to connect to the postgreSQL and Metadata from SQL Alchemy

from blaze import data
from sqlalchemy import MetaData
ds = data(MetaData('postgresql://localhost/test', schema='my_schema'))

before using blaze, install it by:

pip install blaze
SillyPerson
  • 589
  • 2
  • 7
  • 30