Re: [sqlalchemy] Getting column data from result set with column name as a string.
Thanks. The label is an interesting option; I'll look into that.
On a StackOverflow thread, I got *row.__table__.columns* which I can
iterate over and test the key, allowing me to get the column I need but I
have to loop through all the columns until I find the one I want for each
row because columns doesn't have an index either. I also don't like using
a private property but I guess (hope) __table__ would always be there.
On Tuesday, August 18, 2020 at 6:05:49 PM UTC-5 Mike Bayer wrote:
>
>
> On Tue, Aug 18, 2020, at 5:20 PM, Dale Preston wrote:
>
> I'm using sqlalchemy 1.3.18. I'm trying to write an app that looks at
> data from an ORM declarative table without necessarily knowing the table
> definition.
>
> What I am looking for is a way to get a single object (row in resultSet),
> having the name of column[1] is "lastname", and having "lastname" as a
> string in memory, how can I get the value of the "lastname" field from the
> row in resultSet?
>
> It's easy if I know in code that the row has a lastname property and I can
> use row.lastname but I want to do something like row["lastname"] or
> row.columns["lastname"] if there's a way.
>
>
> to get individual columns in the row you query for those columns directly:
>
>
> row = sess.query(User.lastname).first()
>
> print(row.lastname)
>
>
> otherwise you can always label a column if you need:
>
> row = sess.query(User.anything.label("lastname")).first()
>
> print(row.lastname)
>
>
>
>
>
>
>
> I'm using reflection to get the columns for the table. Here's some code I
> tried:
>
> class Users(Base):
> __tablename__ = 'users'
> userid = Column(String(80), primary_key=True)
> lastname = Column(String(40), nullable=False)
> firstname = Column(String(40), nullable=False)
> emailaddress = Column(String(80), nullable=False)
>
> def ReflectTableColumns(DbEngine, meta, targetTable):
>
> tableschema = Table(targetTable, meta, autoload=True,
> autoload_with=DbEngine)
> cols = dict()
> for c in tableschema.columns:
> print("{0}\t|\t{1}".format(c.name, c.type))
> cols[c.name] = c.type
>
> return cols
>
> def GetUsers():
> DBSession = sessionmaker(bind=Engine)
> session = DBSession()
> ShowTableData(session.query(Users).all(), 'users')
>
>
> def ShowTableData(resultSet, tablename):
> columns = ReflectTableColumns(Engine, Base.metadata, tablename)
> columnNames = list(columns.keys())
> print (type(resultSet))
> for row in resultSet:
> print (row.items[columnNames[1]])
> print (row.columns[columnNames[1]])
> print (row[columnNames[1]])
>
> GetUsers()
>
>
> --
> 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+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/f11bf60e-e872-489e-9a9b-03998440bbb1n%40googlegroups.com
>
>
> .
>
>
>
--
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/975d8626-39cf-439f-b5ed-df1e9680f7bfn%40googlegroups.com.
Re: [sqlalchemy] Getting column data from result set with column name as a string.
On Tue, Aug 18, 2020, at 5:20 PM, Dale Preston wrote:
> I'm using sqlalchemy 1.3.18. I'm trying to write an app that looks at data
> from an ORM declarative table without necessarily knowing the table
> definition.
>
> What I am looking for is a way to get a single object (row in resultSet),
> having the name of column[1] is "lastname", and having "lastname" as a string
> in memory, how can I get the value of the "lastname" field from the row in
> resultSet?
>
> It's easy if I know in code that the row has a lastname property and I can
> use row.lastname but I want to do something like row["lastname"] or
> row.columns["lastname"] if there's a way.
to get individual columns in the row you query for those columns directly:
row = sess.query(User.lastname).first()
print(row.lastname)
otherwise you can always label a column if you need:
row = sess.query(User.anything.label("lastname")).first()
print(row.lastname)
>
> I'm using reflection to get the columns for the table. Here's some code I
> tried:
>
> class Users(Base):
> __tablename__ = 'users'
> userid = Column(String(80), primary_key=True)
> lastname = Column(String(40), nullable=False)
> firstname = Column(String(40), nullable=False)
> emailaddress = Column(String(80), nullable=False)
>
> def ReflectTableColumns(DbEngine, meta, targetTable):
> tableschema = Table(targetTable, meta, autoload=True,
> autoload_with=DbEngine)
> cols = dict()
> for c in tableschema.columns:
> print("{0}\t|\t{1}".format(c.name, c.type))
> cols[c.name] = c.type
>
> return cols
>
> def GetUsers():
> DBSession = sessionmaker(bind=Engine)
> session = DBSession()
> ShowTableData(session.query(Users).all(), 'users')
>
>
> def ShowTableData(resultSet, tablename):
> columns = ReflectTableColumns(Engine, Base.metadata, tablename)
> columnNames = list(columns.keys())
> print (type(resultSet))
> for row in resultSet:
> print (row.items[columnNames[1]])
> print (row.columns[columnNames[1]])
> print (row[columnNames[1]])
>
> GetUsers()
>
> --
> 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/f11bf60e-e872-489e-9a9b-03998440bbb1n%40googlegroups.com
>
> .
--
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/70b1537b-14ea-4819-ae48-2c145d8dae63%40www.fastmail.com.
[sqlalchemy] Getting column data from result set with column name as a string.
I'm using sqlalchemy 1.3.18. I'm trying to write an app that looks at data
from an ORM declarative table without necessarily knowing the table
definition.
What I am looking for is a way to get a single object (row in resultSet),
having the name of column[1] is "lastname", and having "lastname" as a
string in memory, how can I get the value of the "lastname" field from the
row in resultSet?
It's easy if I know in code that the row has a lastname property and I can
use row.lastname but I want to do something like row["lastname"] or
row.columns["lastname"] if there's a way.
I'm using reflection to get the columns for the table. Here's some code I
tried:
class Users(Base):
__tablename__ = 'users'
userid = Column(String(80), primary_key=True)
lastname = Column(String(40), nullable=False)
firstname = Column(String(40), nullable=False)
emailaddress = Column(String(80), nullable=False)
def ReflectTableColumns(DbEngine, meta, targetTable):
tableschema = Table(targetTable, meta, autoload=True,
autoload_with=DbEngine)
cols = dict()
for c in tableschema.columns:
print("{0}\t|\t{1}".format(c.name, c.type))
cols[c.name] = c.type
return cols
def GetUsers():
DBSession = sessionmaker(bind=Engine)
session = DBSession()
ShowTableData(session.query(Users).all(), 'users')
def ShowTableData(resultSet, tablename):
columns = ReflectTableColumns(Engine, Base.metadata, tablename)
columnNames = list(columns.keys())
print (type(resultSet))
for row in resultSet:
print (row.items[columnNames[1]])
print (row.columns[columnNames[1]])
print (row[columnNames[1]])
GetUsers()
--
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/f11bf60e-e872-489e-9a9b-03998440bbb1n%40googlegroups.com.
[sqlalchemy] pg_temp for testing with postgres
Hi all, thought I would post this here since this community might find this useful. I've created a simple python module which can be used to quickly spin up a temporary, disposable postgres server. The main purpose I envisioned for this was for writing tests. https://pypi.org/project/pg-temp/ Basically, it works like this: from pg_temp import TempDB temp_db = TempDB(databases=['testdb']) # you can connect to this database using temp_db's pg_socket_dir connection = psycopg2.connect(host=temp_db.pg_socket_dir, database='testdb') For many years since using sqlalchemy to abstract the database, I had fallen into the common trap of writing production code targeting postgres, and running unit tests against sqlite, which is a bad idea for many reasons. So I wrote pg-temp so that I'd be able to run unit tests against postgres as easily as I had been doing so against sqlite. I've been working on it for a couple of years already, so I'd appreciate any feedback or opinions you, the sqlalchemy community, might have. Thanks Uri -- 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/a5691243-5685-47ca-be35-78723060fe15n%40googlegroups.com.
Re: [sqlalchemy] `Base.prepare()` doesn't backfill missing columns and relationships in AutomapBase-derived models
Thanks Mike! `extend_existing=True` is having the partially-specified
explicit models pick up all the remaining columns.
On Tuesday, August 18, 2020 at 11:29:39 AM UTC-7 Mike Bayer wrote:
> your reflect() call requires extend_existing=True in this case otherwise
> existing Table objects as the one you are creating with SqaGlobalContext
> will not be affected.
>
>
>
> On Tue, Aug 18, 2020, at 1:59 PM, Vitaly Kruglikov wrote:
>
> Dear all,
>
> I am using:
> sqlalchemy==1.3.18
> psycopg2==2.8.4
> connection url schema: "postgresql+psycopg2://..."
> postgres 10.x
>
> when I define an explicit AutomapBase-derived model for
> 'sqa_global_context' table with only the primary key, I expected that
> running `Base.metadata.reflect(bind=database.ENGINE,
> only=['sqa_global_context']); Base.prepare()` would backfill missing
> columns and relationships in that table. However, after running
> `Base.prepare(engine, reflect=True)`, the missing columns and relationships
> are not populated in my table.
>
> My code looks like this:
>
> ```
> _AutomapBase = automap.automap_base()
>
> class SqaGlobalContext(_AutomapBase):
> """Type template for the SqaGlobalContext table."""
> __tablename__ = 'sqa_global_context'
>
> key = sa.Column(
> sa.Integer, primary_key=True, server_default=sa.text('0'))
>
> _AutomapBase.metadata.reflect(bind=ENGINE,
> only=['ewf_sqa_global_context', ])
> _AutomapBase.prepare()
> ```
>
> The reason I use `_AutomapBase.metadata.reflect(ENGINE, only=[...])`
> followed by `_AutomapBase.prepare()` instead of just
> `_AutomapBase.prepare(ENGINE, reflect=True)` is because
> `_AutomapBase.prepare(ENGINE, reflect=True)` ends up reflecting **all** the
> tables in the database and I wanted to avoid the unnecessary overhead of
> reflecting the many additional tables that are managed by another subsystem
> not of interest to my application.
>
> So, after running the above code, I expected `SqaGlobalContext` model to
> be populated with additional columns (`created_at`, `updated_at`, etc.) and
> relationships based on foreign keys (see below for actual table DDL). But
> the missing columns and relationships didn't get added:
>
> ```
>
> dir(SqaGlobalContext)
>
> ['__abstract__', '__class__', '__delattr__', '__dict__', '__dir__',
> '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__',
> '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__',
> '__mapper__', '__module__', '__ne__', '__new__', '__reduce__',
> '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__',
> '__subclasshook__', '__table__', '__tablename__', '__weakref__',
> '_decl_class_registry', '_sa_class_manager', '_sa_decl_prepare',
> '_sa_raise_deferred_config', 'classes', 'key', 'metadata', 'prepare']
>
> ```
>
>
> As you can see from `dir(SqaGlobalContext)` - the additional columns
> didn't get populated in `SqaGlobalContext`. So, how can I make this work
> without reflecting the all the tables in the database?
>
> Alembic outputs this when generating this table:
>
> ```
> INFO [sqlalchemy.engine.base.Engine]
> CREATE TABLE ewf_sqa_global_context (
> key INTEGER DEFAULT 0 NOT NULL,
> created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
> updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
> testing_suspended BOOLEAN DEFAULT false NOT NULL,
> metadata_tag VARCHAR(256) DEFAULT NULL,
> attempt_number INTEGER DEFAULT NULL,
> attempt_context_id INTEGER DEFAULT NULL,
> CONSTRAINT pk_ewf_sqa_global_context PRIMARY KEY (key),
> CONSTRAINT fk_ewf_sqa_global_context_metadata_tag_ewf_empower_metadata
> FOREIGN KEY(metadata_tag) REFERENCES ewf_empower_metadata (tag) ON DELETE
> RESTRICT ON UPDATE CASCADE,
> CONSTRAINT fk_ewf_sqa_global_context_attempt_context_id_ewf_sqa_at_0183
> FOREIGN KEY(attempt_context_id) REFERENCES ewf_sqa_attempt_context (key) ON
> DELETE RESTRICT ON UPDATE CASCADE
> ```
>
> Actual Schema in database.
> ```
> CREATE TABLE public.ewf_sqa_global_context
> (
> key integer NOT NULL DEFAULT 0,
> created_at timestamp with time zone NOT NULL DEFAULT now(),
> updated_at timestamp with time zone NOT NULL DEFAULT now(),
> testing_suspended boolean NOT NULL DEFAULT false,
> metadata_tag character varying(256) COLLATE pg_catalog."default"
> DEFAULT NULL::character varying,
> attempt_number integer,
> attempt_context_id integer,
> CONSTRAINT pk_ewf_sqa_global_context PRIMARY KEY (key),
> CONSTRAINT
> fk_ewf_sqa_global_context_attempt_context_id_ewf_sqa_at_0183 FOREIGN KEY
> (attempt_context_id)
> REFERENCES public.ewf_sqa_attempt_context (key) MATCH SIMPLE
> ON UPDATE CASCADE
> ON DELETE RESTRICT,
> CONSTRAINT fk_ewf_sqa_global_context_metadata_tag_ewf_empower_metadata
> FOREIGN KEY (metadata_tag)
> REFERENCES public.ewf_empower_metadata (tag) MATCH SIMPLE
> ON UPDATE CASCADE
> ON DELETE RESTRICT
> ```
>
>
> --
> SQLAlchemy -
> The
[sqlalchemy] Re: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) ([Errno 13] Permission denied)")
> using the same user and am able to connect from python terminal from the same linux box but it doesnt work using the python code. When this works in the terminal, how are you connecting? In Python via the database driver, or using the mysql client? If it's the mysql client, check the server's mysql and system/auth logs. The mysql instance might not be configured to allow this type of connection/authentication from the user. -- 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/d91a42f0-80e8-4431-8882-eaeb29d7ffc9o%40googlegroups.com.
Re: [sqlalchemy] `Base.prepare()` doesn't backfill missing columns and relationships in AutomapBase-derived models
your reflect() call requires extend_existing=True in this case otherwise
existing Table objects as the one you are creating with SqaGlobalContext will
not be affected.
On Tue, Aug 18, 2020, at 1:59 PM, Vitaly Kruglikov wrote:
> Dear all,
>
> I am using:
> sqlalchemy==1.3.18
> psycopg2==2.8.4
> connection url schema: "postgresql+psycopg2://..."
> postgres 10.x
>
> when I define an explicit AutomapBase-derived model for 'sqa_global_context'
> table with only the primary key, I expected that running
> `Base.metadata.reflect(bind=database.ENGINE, only=['sqa_global_context']);
> Base.prepare()` would backfill missing columns and relationships in that
> table. However, after running `Base.prepare(engine, reflect=True)`, the
> missing columns and relationships are not populated in my table.
>
> My code looks like this:
>
> ```
> _AutomapBase = automap.automap_base()
>
> class SqaGlobalContext(_AutomapBase):
> """Type template for the SqaGlobalContext table."""
> __tablename__ = 'sqa_global_context'
>
> key = sa.Column(
> sa.Integer, primary_key=True, server_default=sa.text('0'))
>
> _AutomapBase.metadata.reflect(bind=ENGINE,
> only=['ewf_sqa_global_context', ])
> _AutomapBase.prepare()
> ```
>
> The reason I use `_AutomapBase.metadata.reflect(ENGINE, only=[...])` followed
> by `_AutomapBase.prepare()` instead of just `_AutomapBase.prepare(ENGINE,
> reflect=True)` is because `_AutomapBase.prepare(ENGINE, reflect=True)` ends
> up reflecting **all** the tables in the database and I wanted to avoid the
> unnecessary overhead of reflecting the many additional tables that are
> managed by another subsystem not of interest to my application.
>
> So, after running the above code, I expected `SqaGlobalContext` model to be
> populated with additional columns (`created_at`, `updated_at`, etc.) and
> relationships based on foreign keys (see below for actual table DDL). But the
> missing columns and relationships didn't get added:
>
> ```
> dir(SqaGlobalContext)
> ['__abstract__', '__class__', '__delattr__', '__dict__', '__dir__',
> '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__',
> '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__',
> '__mapper__', '__module__', '__ne__', '__new__', '__reduce__',
> '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__',
> '__subclasshook__', '__table__', '__tablename__', '__weakref__',
> '_decl_class_registry', '_sa_class_manager', '_sa_decl_prepare',
> '_sa_raise_deferred_config', 'classes', 'key', 'metadata', 'prepare']
> ```
>
> As you can see from `dir(SqaGlobalContext)` - the additional columns didn't
> get populated in `SqaGlobalContext`. So, how can I make this work without
> reflecting the all the tables in the database?
>
> Alembic outputs this when generating this table:
>
> ```
> INFO [sqlalchemy.engine.base.Engine]
> CREATE TABLE ewf_sqa_global_context (
> key INTEGER DEFAULT 0 NOT NULL,
> created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
> updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
> testing_suspended BOOLEAN DEFAULT false NOT NULL,
> metadata_tag VARCHAR(256) DEFAULT NULL,
> attempt_number INTEGER DEFAULT NULL,
> attempt_context_id INTEGER DEFAULT NULL,
> CONSTRAINT pk_ewf_sqa_global_context PRIMARY KEY (key),
> CONSTRAINT fk_ewf_sqa_global_context_metadata_tag_ewf_empower_metadata
> FOREIGN KEY(metadata_tag) REFERENCES ewf_empower_metadata (tag) ON DELETE
> RESTRICT ON UPDATE CASCADE,
> CONSTRAINT fk_ewf_sqa_global_context_attempt_context_id_ewf_sqa_at_0183
> FOREIGN KEY(attempt_context_id) REFERENCES ewf_sqa_attempt_context (key) ON
> DELETE RESTRICT ON UPDATE CASCADE
> ```
>
> Actual Schema in database.
> ```
> CREATE TABLE public.ewf_sqa_global_context
> (
> key integer NOT NULL DEFAULT 0,
> created_at timestamp with time zone NOT NULL DEFAULT now(),
> updated_at timestamp with time zone NOT NULL DEFAULT now(),
> testing_suspended boolean NOT NULL DEFAULT false,
> metadata_tag character varying(256) COLLATE pg_catalog."default" DEFAULT
> NULL::character varying,
> attempt_number integer,
> attempt_context_id integer,
> CONSTRAINT pk_ewf_sqa_global_context PRIMARY KEY (key),
> CONSTRAINT fk_ewf_sqa_global_context_attempt_context_id_ewf_sqa_at_0183
> FOREIGN KEY (attempt_context_id)
> REFERENCES public.ewf_sqa_attempt_context (key) MATCH SIMPLE
> ON UPDATE CASCADE
> ON DELETE RESTRICT,
> CONSTRAINT fk_ewf_sqa_global_context_metadata_tag_ewf_empower_metadata
> FOREIGN KEY (metadata_tag)
> REFERENCES public.ewf_empower_metadata (tag) MATCH SIMPLE
> ON UPDATE CASCADE
> ON DELETE RESTRICT
> ```
>
>
> --
> 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
[sqlalchemy] Re: `Base.prepare()` doesn't backfill missing columns and relationships in AutomapBase-derived models
I just noticed a TYPO - but google groups doesn't let me edit my post. In a couple of places in my post, I accidentally entered `sqa_global_context` as the table name instead of `ewf_sqa_global_context`. But in actual code that I am executing, it's `ewf_sqa_global_context` everywhere. -- 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/09007648-48b7-4d3f-bb84-7b9cc4e1a35bo%40googlegroups.com.
[sqlalchemy] `Base.prepare()` doesn't backfill missing columns and relationships in AutomapBase-derived models
Dear all,
I am using:
sqlalchemy==1.3.18
psycopg2==2.8.4
connection url schema: "postgresql+psycopg2://..."
postgres 10.x
when I define an explicit AutomapBase-derived model for
'sqa_global_context' table with only the primary key, I expected that
running `Base.metadata.reflect(bind=database.ENGINE,
only=['sqa_global_context']); Base.prepare()` would backfill missing
columns and relationships in that table. However, after running
`Base.prepare(engine, reflect=True)`, the missing columns and relationships
are not populated in my table.
My code looks like this:
```
_AutomapBase = automap.automap_base()
class SqaGlobalContext(_AutomapBase):
"""Type template for the SqaGlobalContext table."""
__tablename__ = 'sqa_global_context'
key = sa.Column(
sa.Integer, primary_key=True, server_default=sa.text('0'))
_AutomapBase.metadata.reflect(bind=ENGINE,
only=['ewf_sqa_global_context', ])
_AutomapBase.prepare()
```
The reason I use `_AutomapBase.metadata.reflect(ENGINE, only=[...])`
followed by `_AutomapBase.prepare()` instead of just
`_AutomapBase.prepare(ENGINE, reflect=True)` is because
`_AutomapBase.prepare(ENGINE, reflect=True)` ends up reflecting **all** the
tables in the database and I wanted to avoid the unnecessary overhead of
reflecting the many additional tables that are managed by another subsystem
not of interest to my application.
So, after running the above code, I expected `SqaGlobalContext` model to be
populated with additional columns (`created_at`, `updated_at`, etc.) and
relationships based on foreign keys (see below for actual table DDL). But
the missing columns and relationships didn't get added:
```
dir(SqaGlobalContext)
['__abstract__', '__class__', '__delattr__', '__dict__', '__dir__',
'__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__',
'__hash__', '__init__', '__init_subclass__', '__le__', '__lt__',
'__mapper__', '__module__', '__ne__', '__new__', '__reduce__',
'__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__',
'__subclasshook__', '__table__', '__tablename__', '__weakref__',
'_decl_class_registry', '_sa_class_manager', '_sa_decl_prepare',
'_sa_raise_deferred_config', 'classes', 'key', 'metadata', 'prepare']
```
As you can see from `dir(SqaGlobalContext)` - the additional columns didn't
get populated in `SqaGlobalContext`. So, how can I make this work without
reflecting the all the tables in the database?
Alembic outputs this when generating this table:
```
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE ewf_sqa_global_context (
key INTEGER DEFAULT 0 NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
testing_suspended BOOLEAN DEFAULT false NOT NULL,
metadata_tag VARCHAR(256) DEFAULT NULL,
attempt_number INTEGER DEFAULT NULL,
attempt_context_id INTEGER DEFAULT NULL,
CONSTRAINT pk_ewf_sqa_global_context PRIMARY KEY (key),
CONSTRAINT fk_ewf_sqa_global_context_metadata_tag_ewf_empower_metadata
FOREIGN KEY(metadata_tag) REFERENCES ewf_empower_metadata (tag) ON DELETE
RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_ewf_sqa_global_context_attempt_context_id_ewf_sqa_at_0183
FOREIGN KEY(attempt_context_id) REFERENCES ewf_sqa_attempt_context (key) ON
DELETE RESTRICT ON UPDATE CASCADE
```
Actual Schema in database.
```
CREATE TABLE public.ewf_sqa_global_context
(
key integer NOT NULL DEFAULT 0,
created_at timestamp with time zone NOT NULL DEFAULT now(),
updated_at timestamp with time zone NOT NULL DEFAULT now(),
testing_suspended boolean NOT NULL DEFAULT false,
metadata_tag character varying(256) COLLATE pg_catalog."default"
DEFAULT NULL::character varying,
attempt_number integer,
attempt_context_id integer,
CONSTRAINT pk_ewf_sqa_global_context PRIMARY KEY (key),
CONSTRAINT fk_ewf_sqa_global_context_attempt_context_id_ewf_sqa_at_0183
FOREIGN KEY (attempt_context_id)
REFERENCES public.ewf_sqa_attempt_context (key) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT fk_ewf_sqa_global_context_metadata_tag_ewf_empower_metadata
FOREIGN KEY (metadata_tag)
REFERENCES public.ewf_empower_metadata (tag) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT
```
--
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/280b0e8e-99e7-4c53-af65-297ad1ed5ba4o%40googlegroups.com.
[sqlalchemy] sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) ([Errno 13] Permission denied)")
I'm trying to connect to mysql using below
engine = create_engine("mysql+pymysql://user:pwd@localhost/test")
connection = engine.connect()
it works while in local and when deployed the code in one of our linux box
, am getting
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003,
"Can't connect to MySQL server on 'xxx.xx.xx.xxx' ([Errno 13] Permission
denied)")
I checked the user privileges and the user has all the necessary privileges.
using the same user and am able to connect from python terminal from the
same linux box but it doesnt work using the python code.
Any suggestions if am missing anything?
--
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/d70ac922-857f-426a-aca2-81940327575do%40googlegroups.com.
