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 <javascript:>.
> 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/f65e2d43-0d0b-4115-9da8-34ffbbd15404%40googlegroups.com.
