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 [email protected].
>> 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/070d905f-8dd9-4d4f-8d41-c65c5ab5686d%40googlegroups.com.
