Re: [sqlalchemy] dialect's default_schema_name not used for MetaData object
>
> 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
> > for a full
> > > description.
> > > ---
> > > You received this message because
Re: [sqlalchemy] dialect's default_schema_name not used for MetaData object
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
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
> .
> To post to this group, send email to sqlal...@googlegroups.com
> .
> 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
Re: [sqlalchemy] dialect's default_schema_name not used for MetaData object
>
> 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.
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 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
> > .
> > To post to this group, send email to sqlal...@googlegroups.com
>
> > .
> > 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.
Re: [sqlalchemy] dialect's default_schema_name not used for MetaData object
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 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.
--
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.
