Hello Mike,
Thanks for replying so quickly, I appreciate that. Below is the engine I am
using and the output of the code you mentioned:
params = urllib.parse.quote_plus('Driver={SQL Server};'\
"Server=*******;"\
"DSN=TreasuryMSSQL;"
"Trusted_Connection=yes;"
"UID=Felipe.Araya;"
"PWD=*******,"
)
engine = db.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params, echo=
debug)
insp = inspect(engine)
print(insp.get_table_names())
# I know you mentioned that "dbo" shouldn't be specified since it comes by
default, but if I don't specify a simply get nothing, in fact the only way
in which I have succeeded reflecting the table from mssql was using CORE
and specifying "dbo" in MetaData(engine, schema="dbo"), otherwise it
wouldn't work, something similar happened when I wanted to map a class from
the domain to the database the only way in which I could do it was by doing:
class User(Base):
id ...
name.....
User.__table__.schema = "dbo"
Base.metadata.create_all()engine
btw: I have binge-watched a couple of your 3hr videos on Youtube, they are
great man, thanks for those!
# Here is the output of the "echo"
2019-11-27 20:03:44,722 INFO sqlalchemy.engine.base.Engine SELECT
CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
INFO:sqlalchemy.engine.base.Engine:SELECT
CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR) 2019-11-27 20:03:44,724
INFO sqlalchemy.engine.base.Engine () INFO:sqlalchemy.engine.base.Engine:()
2019-11-27 20:03:44,736 DEBUG sqlalchemy.engine.base.Engine Col ('',)
DEBUG:sqlalchemy.engine.base.Engine:Col ('',) 2019-11-27 20:03:44,738 DEBUG
sqlalchemy.engine.base.Engine Row ('12.0.2000.8',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('12.0.2000.8',) 2019-11-27
20:03:44,739 INFO sqlalchemy.engine.base.Engine SELECT schema_name()
INFO:sqlalchemy.engine.base.Engine:SELECT schema_name() 2019-11-27
20:03:44,740 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:() 2019-11-27 20:03:44,746 DEBUG
sqlalchemy.engine.base.Engine Col ('',)
DEBUG:sqlalchemy.engine.base.Engine:Col ('',) 2019-11-27 20:03:44,747 DEBUG
sqlalchemy.engine.base.Engine Row ('guest',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('guest',) 2019-11-27 20:03:44,754
INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS
VARCHAR(60)) AS anon_1 INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test
plain returns' AS VARCHAR(60)) AS anon_1 2019-11-27 20:03:44,755 INFO
sqlalchemy.engine.base.Engine () INFO:sqlalchemy.engine.base.Engine:()
2019-11-27 20:03:44,760 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS
NVARCHAR(60)) AS anon_1 2019-11-27 20:03:44,762 INFO
sqlalchemy.engine.base.Engine () INFO:sqlalchemy.engine.base.Engine:()
2019-11-27 20:03:44,774 INFO sqlalchemy.engine.base.Engine 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]
INFO:sqlalchemy.engine.base.Engine: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 20:03:44,776 INFO
sqlalchemy.engine.base.Engine ('guest', 'BASE TABLE')
INFO:sqlalchemy.engine.base.Engine:('guest', 'BASE TABLE') 2019-11-27
20:03:44,785 DEBUG sqlalchemy.engine.base.Engine Col ('TABLE_NAME',)
DEBUG:sqlalchemy.engine.base.Engine:Col ('TABLE_NAME',)
On Wednesday, 27 November 2019 20:02:07 UTC, Mike Bayer wrote:
>
>
>
> 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 [email protected] <javascript:>.
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/7d0ed195-8ec0-4896-9cf2-5b7fbe9b4d75%40googlegroups.com.
