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
On Thursday, 28 November 2019 10:05:54 UTC, Felipe Araya Olea wrote:
>
> 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
>
>
>
>
>
> On Wednesday, 27 November 2019 21:57:41 UTC, Mike Bayer wrote:
>>
>> 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 sqlal...@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/89371b57-2287-47d4-a716-dcbb869b4fc8%40googlegroups.com.
