On Wed, Nov 27, 2019, at 2:45 PM, Felipe Araya Olea wrote:
> Hello,
>
> I am having problems reflecting my tables from the MS SQL Server database
> that have the schema "dbo". I have tried using the following code.
"dbo" is the default schema and usually does not need to be part of the
arguments that you provide to Metadata or reflect.
To debug a reflection operation, set echo='debug' on create_engine(), then look
at the queries being emitted as well as the results that come back . For a
quicker test, just do a get_table_names:
from sqlalchemy import inspect
engine = create_engine("...", echo='debug')
insp = inspect(engine)
print(insp.get_table_names())
the INFO/DEBUG output will show the queries being emitted and the results
returned. You want to make sure the get table names looks something like mine,
where I log in as "scott":
SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max))
AND [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max)) ORDER
BY [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME]
2019-11-27 14:59:02,092 INFO sqlalchemy.engine.base.Engine ('scott', 'BASE
TABLE')
2019-11-27 14:59:02,104 DEBUG sqlalchemy.engine.base.Engine Col ('TABLE_NAME',)
2019-11-27 14:59:02,105 DEBUG sqlalchemy.engine.base.Engine Row ('foo',)
the engine gets the name "scott" or "dbo" by invoking the command "schema_name".
if you show me the output of the command "SELECT schema_name" using the exact
ODBC connection you are using and also what schema your tables are under inside
of information_schema.tables we can determine what the correct schema name
argument should be.
>
>
> engine = db.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params) #
> Engine works, because I have tested it using CORE
> meta = MetaData(schema="dbo") # I have tried binding it to engine as well
> meta.reflect(bind=engine, schema="dbo") # I have tried without this line as
> well
> Base = automap_base(metadata=meta)
> Base.prepare(engine, reflect=True)
>
> ### That seemed to work fine, I get no errors, however, if I try to retrieve
> the name of the tables I can't see them:
> Base.classes.keys()
> >>> []
>
>
> I have seen that the problem could be that the table doesn't have a primary
> key, however that is not the problem, because I have multiple tables under
> the schema "dbo" and they have primary keys.
>
>
> If anyone knows a solution please let me know, I will be eternally grateful.
>
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/d145fd44-5ffd-427b-869f-84d2cbd0bab1%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/d145fd44-5ffd-427b-869f-84d2cbd0bab1%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/b48a6149-800d-4638-8604-8732dde3b174%40www.fastmail.com.
