I ran your code and worked perfectly, I was using "sqlite+pysqlite" instead of "sqlite" as the engine but the behavior remains the same.
Examining the example I discovered the inspector was correctly retrieving the foreign keys using 'main' as a schema but filtering them out later, sorry for making you waste time -_- Thanks a lot! Jacopo On Thursday, 2 November 2017 02:45:56 UTC+1, Mike Bayer wrote: > > On Wed, Nov 1, 2017 at 6:43 PM, Jacopo Farina <[email protected] > <javascript:>> 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 [email protected] <javascript:>. > > To post to this group, send email to [email protected] > <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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
