Problems reflecting "dbo" schema dynamically (ORM) in MS SQL Server

[Felipe Araya Olea]

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.

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.

[Mike Bayer]

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d145fd44-5ffd-427b-869f-84d2cbd0bab1%40googlegroups.com.

[Felipe Araya Olea]

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',)

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Felipe Araya Olea]

One thing I forgot to mention.

This is not my personal database, this is my company's database, so maybe there are some restrictions and permissions that might play a part, although, if I use the engine as it is, it works, if I change even the smallest thing, I get an error saying access denied, so the log in is working as far as I can tell, the only thing that I have had changed depending if I am using CORE or ORM is the DNS (it was throwing me an error with CORE, so I commented out),

I can see that the output of the "echo" says "guest" instead of "Felipe.Araya", which is strange, isn't it, I don't really understand why that might be happening since the log in seems to be working.

Hope you are having a good day.

Looking forward to hearing back from you.

[Mike Bayer]

right so, automap requires that the tables have primary key constraints on them, otherwise they won't show up as mapped classes.    So you'd need to define the declarative classes you are interested in and establish which columns would be used for primary keys on each if there is no primary key constraint coming from the table itself.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/070d905f-8dd9-4d4f-8d41-c65c5ab5686d%40googlegroups.com.

[Felipe Araya Olea]

Hello Mike,

Thanks for your answer. I think I am definitely closer to find the solution thanks to your comments, I created a new table in mssql without using any pre-existing schema and also making sure it has a primary key. I managed to find that table using the code I showed you above and  map it, I couldn't find the "dbo" tables though, however, you are right, I think it has to do with the primary keys.

So, if I understand correctly, you are saying that I will have to create the class manually and specify there the primary key? In other words, I can't reflect that table from the database to a Python class? This table that I am trying to reflect has 30+ columns, I think that will be a bit hard, any suggestions for this?

Thanks for helping me by the way, much appreciated.

Hope you are having a good day.

Kind Regards

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/070d905f-8dd9-4d4f-8d41-c65c5ab5686d%40googlegroups.com.

[Felipe Araya Olea]

UPDATE!

I have gone to the mssql server, manually added a new primary key to the table I was looking for.

I used the following SQL code if anyone is interested:

ALTER TABLE AssetCashFlows

ADD ID INT IDENTITY; 

## Then I had to do a second query, to transform ID into a primary key.

ALTER TABLE AssetCashFlows

ADD PRIMARY KEY (ID);

After doing this I had a primary key ready and then to find the table using ORM I had to use the following:

Base = automap_base()

Base.prepare(engine, reflect=True, schema="dbo")

Base.classes.keys()

## You could have also done:

meta = MetaData(engine, schema="dbo")

Base = automap_base(metadata= meta)

Base.prepare(engine, reflect=True)

## OR - This one showed me all the tables, not only the "dbo" ones

meta = MetaData()

meta.reflect(bind=engine, schema="dbo")

Base = automap_base(metadata= meta)

Base.prepare(engine, reflect=True)

## OR

Base = automap_base()

Base.metadata = MetaData(engine, schema="dbo")

Base.prepare(engine, reflect=True)

All of these work for me.

Hope that helps someone, and thanks Mike for the help.

Kind Regards