On Wed, Nov 1, 2017 at 6:43 PM, Jacopo Farina <jacopo1.far...@gmail.com> wrote:
> Hello everybody,
>
> I am writing a component of an application that should display a chart of
> database tables and foreign keys (this one), and trying to make it database
> agnostic. I encountered a strange behavior when inspecting the foreign keys
> of a SQLite database, in particular I can't see them by setting schema_name
> to the value returned from get_schema_names but only by passing
> schema_name=None.
>
> I create a test db this way:
>
> conn = sqlite3.connect('test.db')
> conn.execute('CREATE TABLE table_one(column_one INTEGER, column_two TEXT)')
> conn.execute("""CREATE TABLE table_two(
> column_X INTEGER,
> column_Y TEXT,
> FOREIGN KEY(column_X) REFERENCES table_one(column_one)
> )""")
> conn.close()
>
>
> If I use a sqlite database I see that
>
> inspect(engine).get_schema_names()
>
> returns ['main'] and
>
> sa.engine.reflection.Inspector.from_engine(db).get_table_names('main')
>
> shows the tables in the database, as expected.
>
> But when I run
> sa.engine.reflection.Inspector.from_engine(db).get_foreign_keys('table_name',
> schema='main')
>
> it doesn't return any foreign key, while it does with schema=None
>
> is this a bug or the expected behavior?
can't reproduce your result with any of SQLAlchemy version 1.0, 1.1,
or current master. Here's a test case against 1.1's format, if you
are using 1.0 remove the "options" element from the assertion list:
import sqlite3
import os
if os.path.exists('test.db'):
os.unlink('test.db')
conn = sqlite3.connect('test.db')
conn.execute('CREATE TABLE table_one(column_one INTEGER, column_two TEXT)')
conn.execute("""CREATE TABLE table_two(
column_X INTEGER,
column_Y TEXT,
FOREIGN KEY(column_X) REFERENCES table_one(column_one)
)""")
conn.close()
from sqlalchemy import create_engine, inspect
import sqlalchemy as sa
engine = create_engine("sqlite:///test.db", echo='debug')
for table_name in ("table_one", "table_two"):
print("1. -------------------------------------")
fk_no_schema = sa.engine.reflection.Inspector.\
from_engine(engine).get_foreign_keys(table_name)
print(fk_no_schema)
print("2. -------------------------------------")
fk_schema = sa.engine.reflection.Inspector.\
from_engine(engine).get_foreign_keys(table_name, schema='main')
print(fk_schema)
if table_name == "table_two":
assert fk_no_schema == [
{
'name': None,
'referred_columns': [u'column_one'],
'referred_table': u'table_one',
'constrained_columns': [u'column_X'],
'referred_schema': None,
'options': {}}
]
assert fk_schema == [
{
'name': None,
'referred_columns': [u'column_one'],
'referred_table': u'table_one',
'constrained_columns': [u'column_X'],
'referred_schema': 'main',
'options': {}}
]
the output of each "table2" without the SQL echoing is:
[{'name': None, 'referred_columns': [u'column_one'], 'referred_table':
u'table_one', 'constrained_columns': [u'column_X'], 'referred_schema':
None, 'options': {}}]
[{'name': None, 'referred_columns': [u'column_one'], 'referred_table':
u'table_one', 'constrained_columns': [u'column_X'], 'referred_schema':
'main', 'options': {}}]
the only difference is that when you ask for the FKs in terms of the
"main" schema, vs. the "default" schema (which is the same thing, just
implicit), it maintains that as the "referred_schema" argument in the
returned list.
Things which may impact the behavior of SQLite are:
SQLAlchemy version
Python / pysqlite version
sqlite3.so version underlying the pysqlite version
operating system
otherwise if you can confirm the above test case works, the job is to
figure out what's different about your original run vs. this one.
>
>
> --
> 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.
