On 05/18/2017 06:56 PM, jan.karstens via sqlalchemy wrote:
Upgrading a specific dialect (EXASOL) to 1.1.9 made me stumble across
this test (part of test_reflection.py):
https://github.com/zzzeek/sqlalchemy/blob/rel_1_1_9/lib/sqlalchemy/testing/suite/test_reflection.py#L737
def test_get_table_names(self):
tablenames = [
t for t in inspect(testing.db).get_table_names()
if t.lower() in ("t1", "t2")]
eq_(tablenames[0].upper(), tablenames[0].lower())
eq_(tablenames[1].upper(), tablenames[1].lower())
The test case fails and this does not look too unexpected to me. The
tables T1 and T2 are added to the tablenames array, and u"t1".upper() is
always different from u"t1".lower().
Am I missing the purpose of the test case or is this always bound to fail?
we have two provided dialects, Oracle and Firebird, for which the
backend databases behave like this:
1. create a table called "table1":
CREATE TABLE tablw1 (some_col integer)
2. ask the database what the name of "table1" is:
SQL> SELECT table_name FROM <system catalog stuff, returns the name
of "table1"
TABLE_NAME
----------
TABLE1
Now what just happened. We created the table with the name "table1" and
then it insisted we just named it "TABLE1". What's going on here?
Basically, we didn't quote the name "table1" in our CREATE TABLE
statement, which means in virtually all databases that the name is case
insensitive. It means this table will respond to:
SELECT * FROM TABLE1
SELECT * FROM table1
SELECT * FROM TaBLe1
etc.
In SQLAlchemy, we first wrote things with SQLite, Mysql, Postgresql in
mind. In these databases, if you don't quote the name, the system
catalogs give you back the name *in lowercase*. Plus, lowercase names
are much more of a Python thing.
What all this means is that *SQLAlchemy considers an all lower case
table name to be **case insensitive**, and any other combination casings
to be **case sensitive**. This could really use a whole chapter in
the docs, but for now you can see it at:
http://docs.sqlalchemy.org/en/latest/core/metadata.html?highlight=table%20name#sqlalchemy.schema.Table.params.name
"Names which contain no upper case characters will be treated as case
insensitive names, and will not be quoted unless they are a reserved
word or contain special characters. A name with any number of upper case
characters is considered to be case sensitive, and will be sent as quoted."
So now we have this round trip case that everyone expects to work:
t = Table("table1", metadata, autoload_with=engine)
t.create(some_other_engine)
t2 = Table("table1", some_other_metadata, autoload_with=some_other_engine)
That is, we can go back and forth, using "table1", and we get that same
name back. If we got back "TABLE1", then it would create the table as
follows:
CREATE TABLE "TABLE1" (some_col integer)
and now we have a **case sensitive** name. It means we can only SELECT
from it like this:
SELECT * FROM "TABLE1"
These SQL statements OTOH will fail:
SELECT * FROM table1
SELECT * FROM TaBlE1
SELECT * FROM "table1"
this statement *might* fail depending on backend:
SELECT * FROM TABLE1
So that's a lot of detail. Let's talk about EXASOL, which I have noted,
you have called EXASOL, and not "Exasol", which while this is a database
I know nothing about, leads to the impression that EXASOL LIKES THINGS
TO BE CAPITALIZED, basically the way ORACLE and FIREBIRD SEEM TO THINK
IS GREAT.
If that's the case, then you need to take the steps that the Oracle /
Firebird DBs take which is to "normalize" / "denormalize" the names that
you get back from the server. To do that, start with the top-level
dialect flag:
class EXASOLDialect(default.DefaultDialect):
# ...
requires_name_normalize = True
then you're going to want to denormalize names every time you pass them
to a system catalog query, and normalize names every time you receive
rows back from the system catalogs - look inside of
lib/sqlalchemy/oracle/base.py for examples of this, such as:
@reflection.cache
def get_table_names(self, connection, schema=None, **kw):
schema = self.denormalize_name(schema or self.default_schema_name)
if schema is None:
schema = self.default_schema_name
sql_str = "SELECT table_name FROM all_tables WHERE "
sql_str += (
"OWNER = :owner "
"AND IOT_NAME IS NULL "
"AND DURATION IS NULL")
cursor = connection.execute(sql.text(sql_str), owner=schema)
return [self.normalize_name(row[0]) for row in cursor]
where you can see to get the table names, we denormalize the incoming
schema name, so that if it is "myschema", we call it "MYSCHEMA", and if
it is '"myschema"', we call it '"myschema"', so that it matches either
case insensitively or sensitively per user request, and then when we get
names back, we convert a name like "TABLE1" to "table1", that is, if we
see an UPPERCASE name, we assume that is a case insensitive name and we
convert to lowercase.
In general, when you see a test that is nonsensical and is failing, just
run it against the default dialect which is SQLite, and watch what it
does, and use pdb.set_trace() to get in there and poke around at the
state of things. That will give you direct insight into what's going on
within a particular test.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto: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.
