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.
On Wed, Nov 27, 2019, at 3:55 PM, Felipe Araya Olea wrote:
> 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
>
> <https://groups.google.com/d/msgid/sqlalchemy/070d905f-8dd9-4d4f-8d41-c65c5ab5686d%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/21998282-6193-489c-8f43-9a5f445fb583%40www.fastmail.com.
