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.

Improve this question
3
  • 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() 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). Commented Apr 7, 2017 at 9: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. Commented Apr 7, 2017 at 9: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. Commented Mar 28, 2019 at 15:44

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.