>
> the default schema is what the SQLAlchemy dialect assumes will be the
> effective schema name **when we do not state the schema name explicitly**
Ahh, now I understand! Thanks for the detailed explanation ;).
you write your own dialect that explicitly injects the schema name into SQL
Yes, this is what my database requires. I do the following now for my
Progress OpenEdge database to set the schema:
def on_connect(self):
def connect(con):
cur = con.cursor()
cur.execute("SET SCHEMA 'pub'")
cur.close()
return connect
Thanks a lot for your help!
On Monday, 22 May 2017 23:33:22 UTC+10, Mike Bayer wrote:
>
>
>
> On 05/21/2017 11:21 PM, ben4ever wrote:
> > if you refer to a table without a schema name, that's the schema it
> > will actually use
> >
> > Yes, and that's the problem.
> >
> > In my dialect's base.py I define the following method:
> > |
> > def _get_default_schema_name(self, connection):
> > return 'pub'
> >
> > |
> >
> > But the code sample mentioned in my first email yields the following
> > SELECT *without* prefixing the column and table names with "pub":
> > |
> > 2017-05-18 16:26:51,151 INFO sqlalchemy.engine.base.Engine SELECT foo.f1
> > FROM foo
> > |
> >
> > I noticed that there is no handling to retrieve the dialect's
> > default_schema_name from within the MetaData class in sql/schema.py but
> > like I said I just don't know whether this is intended for a reason.
>
>
> OK, let me try again to explain.
>
>
> if you refer to a table without a schema name, that's the schema **it**
> > will actually use
>
>
> "**it**" here means **the database, implicitly**.
>
>
> That is, the default schema is what the SQLAlchemy dialect assumes will
> be the effective schema name **when we do not state the schema name
> explicitly**.
>
>
> Example. On Postgresql, the default schema name is named "public".
>
> Go to your Postgresql command line, run this statement:
>
> psql > SELECT * FROM some_table
>
> What schema is "some_table" in? Answer: "public". That's the default
> schema. We *did not state this name*. It is *implicit*. SQLAlchemy
> uses this name when you ask it a question like, "reflect the columns
> from some_table". When it queries system catalogs, the default schema
> is no longer implicit, we need it. We use get_default_schema to know
> what that name is.
>
> The only way SQLAlchemy states the schema name in the query is if:
>
> a. you include it in the Table() definition, e.g. Table(...,
> schema='myschema')
>
> b. You include it in the MetaData so that it is inherited by the Table,
> e.g. MetaData(schema='myschema')
>
> c. you write your own dialect that explicitly injects the schema name
> into SQL. If your database is unable to invoke SQL without all symbols
> being schema-qualified, then you would need to do this. It's not clear
> what the actual problem is.
>
>
>
>
>
>
> >
> > On Friday, 19 May 2017 23:19:06 UTC+10, Mike Bayer wrote:
> >
> >
> >
> > On 05/18/2017 08:42 PM, ben4ever wrote:
> > > I'm writing a Progress OpenEdge dialect and when experimenting
> > with the
> > > dialect's default_schema_name I noticed that it is not
> automatically
> > > used when creating a MetaData object without specifying a schema.
> > > Here is an example where the SELECT statement does not include
> the
> > > schema name specified in default_schema_name:
> > > |
> > > from urllib.parse import quote
> > >
> > > from sqlalchemy import create_engine, select, Table, Column,
> > Integer,
> > > MetaData
> > > import sqlalchemy_progress
> > >
> > > connect_string = 'progress:///?odbc_connect={}'.format(quote(
> > >
> > 'DRIVER=progress;DB=bizcomm;HOST=hostname;PORT=1234;UID=admin'))
> > > engine = create_engine(connect_string, echo=True)
> > >
> > > metadata = MetaData()
> > > foo = Table('foo', metadata,
> > > Column('f1', Integer),
> > > Column('f2', Integer))
> > >
> > > print(engine.connect().execute(
> > > select([foo.c.f1])
> > > ).fetchall())
> > > |
> > >
> > > Is this the intended behaviour?
> >
> > yes. the "default" schema means, the *database's* default schema.
> > that is, if you refer to a table without a schema name, that's the
> > schema it will actually use.
> >
> >
> >
> >
> > >
> > > --
> > > SQLAlchemy -
> > > The Python SQL Toolkit and Object Relational Mapper
> > >
> > > http://www.sqlalchemy.org/
> > >
> > > To post example code, please provide an MCVE: Minimal, Complete,
> and
> > > Verifiable Example. See http://stackoverflow.com/help/mcve
> > <http://stackoverflow.com/help/mcve> for a full
> > > description.
> > > ---
> > > You received this message because you are subscribed to the
> Google
> > > Groups "sqlalchemy" group.
> > > To unsubscribe from this group and stop receiving emails from it,
> > send
> > > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>
> <javascript:>>.
> > > To post to this group, send email to sqlal...@googlegroups.com
> > <javascript:>
> > > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > > Visit this group at https://groups.google.com/group/sqlalchemy
> > <https://groups.google.com/group/sqlalchemy>.
> > > For more options, visit https://groups.google.com/d/optout
> > <https://groups.google.com/d/optout>.
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > description.
> > ---
> > You received this message because you are subscribed to the Google
> > Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
