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. On Wednesday, 27 November 2019 20:36:48 UTC, Felipe Araya Olea wrote: > > 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 sqlal...@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/070d905f-8dd9-4d4f-8d41-c65c5ab5686d%40googlegroups.com.