[sqlalchemy] limiting reflect and automap to single schema only
Hi Everyone,
SQLAlchemy nicely reflects tables and builds model using automap. When
providing the parameter shema to reflect, tables from that schema are
discovered. I've noticed that tables from other schemas will also be
included if there is a foreign key to the other schema.
It's all good but what should I do to reflect and automap *only* tables
from the schema i.e. ignore tables and relationships to other schemas?
I've done it by removing unwanted:
- foreign keys that refer to other schemas from table objects
- foreign keys constraints that refer to other schemas from table objects
- foreign keys that refer to other schemas from column objects
I've attached the script that does what I've explained above.
It seems to work and no errors when reflect, automap and making queries
even with joins but is it OK to do it this way?
Much appreciate any comments.
Cheers,
Michal
--
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/5984bc30-5325-4053-b42e-694f96af9516n%40googlegroups.com.
import functools
from sqlalchemy import (
create_engine,
MetaData,
PrimaryKeyConstraint,
ForeignKeyConstraint
)
from sqlalchemy.ext.automap import automap_base
def is_target_in_schema(target_full_name, schema):
"""Checks if target_full_name is in the schema.
The target_full_name is dot separated [schema].[table].[column]
It may be [table].[column] when
"""
if schema is None:
schema = ''
try:
referred_schema = target_full_name.split('.')[-3]
except IndexError:
referred_schema = ''
return schema == referred_schema
def pk_and_local_fk_constraints(c, db_schema=None):
"""Function for filter.
Filter out foreign key constraints that point to different schema.
"""
if isinstance(c, PrimaryKeyConstraint):
return True
if isinstance(c, ForeignKeyConstraint):
target_fullname = c.elements[0].target_fullname
return is_target_in_schema(target_fullname, db_schema)
return False
connstr = 'mssql://test:test@127.0.0.1:1433/test?driver=FreeTDS_Version=7.0'
engine = create_engine(connstr)
db_schema = 'Customer'
metadata = MetaData()
metadata.reflect(engine, schema=db_schema, resolve_fks=False)
for table in metadata.tables.values():
# removes fk from columns that refer outside the schema
for col in table.columns:
col.foreign_keys = {
fk for fk in col.foreign_keys
if is_target_in_schema(fk.target_fullname, db_schema)
}
# removes fk that refer outside the schema
table.foreign_keys = {
fk for fk in table.foreign_keys
if is_target_in_schema(fk.target_fullname, db_schema)
}
# keep only pk constraints and fk constraints within the schema
table.constraints = set(
filter(
functools.partial(
pk_and_local_fk_constraints, db_schema=db_schema
),
table.constraints
)
)
Base = automap_base(metadata=metadata)
Base.prepare(engine)
classes = [(c.__name__) for c in Base.classes]
classes.sort()
print(classes)
print('Dealers' in classes)
# output, Dealers not present in reflected model
# ['People', 'Service', 'Vehicles']
# False
[sqlalchemy] boud parameter to NCHAR column in Oracle
Hi everyone,
There's IMO unusual behavior in Oracle when using bound parameters on NCHAR
column.
from sqlalchemy import create_engine
from sqlalchemy.sql import text
e = create_engine('oracle://chinook:p4ssw0rd@localhost/xe')
result = e.execute(
text("select * from nchartable where id = '1'"),
).fetchall()
print 'hardcoded:', list(result)
result = e.execute(
text('select * from nchartable where id = :id'),
{'id': '1'}
).fetchall()
print 'trimmed:', list(result)
result = e.execute(
text('select * from nchartable where id = :id'),
{'id': '1 '} # padded with spaces
).fetchall()
print 'padded:', list(result)
output
hardcoded: [(u'1 ',)]
trimmed: []
padded: [(u'1 ',)]
When value is hardcoded or bound parameter is padded the statement works as
expected, but it doesn't when value is trimmed. Although the value in where
statement is just like in hardcoded case.
As explained on cxOracle mailing list
https://sourceforge.net/p/cx-oracle/mailman/message/36775002/ it's a
deliberate decision to bind values str to VARCHAR2 and unicode to
NVARCHAR2. Unfortunately that doesn't work correctly for filtering on CHAR
and NCHAR.
I know it's more cxOracle matter but how do you deal with this in
SQLAlchemy? Expecially when working with text SQL statement which may be
complex e.g. use stored procedures.
Thanks for any clues,
Michal
--
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/475bbd60-f3d8-486b-a640-5fd58d679af6%40googlegroups.com.
[sqlalchemy] why MySQL DOUBLE is asdecimal=True by default
Just out of curiosity, why it was decided that MySQL DOUBLE, which is approximation, will be presented in ORM as Decimal by default instead of float? MySQL DOUBLE - https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html SQLAlchemy DOUBLE - https://docs.sqlalchemy.org/en/13/dialects/mysql.html#sqlalchemy.dialects.mysql.DOUBLE Thanks, Michal -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] binding parameters in quotes
Hi,
Is it correct behavior that parameter placeholders *in quotes* e.g. SELECT *
FROM Artist WHERE Name LIKE "%:pattern%" are recognized as valid parameters?
from sqlalchemy.sql import text
from sqlalchemy.dialects import sqlite
from sqlalchemy import create_engine
engine = create_engine('sqlite:home/mike/Chinook.sqlite', echo=True)
s = 'SELECT * FROM Artist WHERE Name LIKE "%:pattern%"'
q = text(s)
c = q.compile()
print c.params
{'pattern': None}
If parameter is provided
engine.execute(q, {'pattern': 'foo'})
engine echo:
2019-03-20 12:44:14,668 INFO sqlalchemy.engine.base.Engine SELECT * FROM Artist
WHERE Name LIKE '%?'
2019-03-20 12:44:14,669 INFO sqlalchemy.engine.base.Engine ('foo',)
and error is raised
ProgrammingError: (sqlite3.ProgrammingError) Incorrect number of bindings
supplied. The current statement uses 0, and there are 1 supplied. [SQL:
u'SELECT * FROM Artist WHERE Name LIKE "%?%"'] [parameters: ('foo',)]
(Background on this error at: http://sqlalche.me/e/f405)
W hen executed without parameters
engine.execute(q, {'pattern': 'foo'})
different error is raised
StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required for
bind parameter 'pattern' [SQL: u'SELECT * FROM Artist WHERE Name LIKE "%?%"']
(Background on this error at: http://sqlalche.me/e/cd3x)
I t feels to me like finding parameters in
https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/compiler.py#L895
should exclude placeholders inside quotes.
Michal
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] raw sql execute and engine convert_unicode
Hi,
I was looking into create_engine options enocding and convert_unicode and
it seems it works for ORM but not for executing raw sql.
Is that intentional or I'm missing something?
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.sql import text
def get_first_name(engine):
row = engine.execute(text('SELECT FirstName FROM Customer LIMIT
1')).fetchone()
print row[0]
Base = automap_base()
Base.prepare(engine, reflect=True)
Customer = Base.classes.Customer
c1 = Session(engine).query(Customer).first()
print c1.FirstName
print 5*'--'
engine = create_engine('mysql://chinook:chinook@127.0.0.1:3309/Chinook')
unicode_engine =
create_engine('mysql://chinook:chinook@127.0.0.1:3309/Chinook',
encoding='latin1', convert_unicode=True)
get_first_name(engine)
get_first_name(unicode_engine)
The output was
Lu�s
Lu�s
--
Lu�s
Luís
--
Kind regards,
Michal
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] StatementError _exec_default() takes exactly 3 arguments (4 given) since version 1.2.0b3
Hi,
I've upgraded SQLAlchemy from 1.0.18 to latest 1.2.8 and I get errors that
_exec_default() received wrong number of parameters. I've pinpointed that
problem appears since version 1.2.0b3.
Looks like inside method get_insert_default
https://github.com/zzzeek/sqlalchemy/blob/rel_1_2/lib/sqlalchemy/engine/default.py#L1283
the *self._exec_default *is not actually method
of sqlalchemy.engine.default.DefaultExecutionContext.
Any ideas what is going on?
Traceback:
2018-05-30 21:24:38,977 ERROR [waitress] (exceptions.TypeError)
_exec_default() takes exactly 3 arguments (4 given) [SQL: u'INSERT INTO
timesheet (user_id, project_id, date) VALUES (?, ?, ?)'] [parameters: [{u
'project_id': 1, u'user_id': 1}]]
Traceback (most recent call last):
File "/home/mike/projects/slashdb/slashdb/utils.py", line 430, in
func_wrapper
return func(*args, **kwargs)
File "/home/mike/projects/slashdb/slashdb/data_discovery_views.py", line
90, in post_view
locations = data_proxy.create(create_data)
File
"/home/mike/projects/slashdb/slashdb/models/sqlalchemy_sdb/dataproxy.py",
line 415, in create
self._add_all(new_resources)
File "/home/mike/projects/slashdb/slashdb/models/sqlalchemy_sdb/utils.py",
line 32, in wrapper
return dataproxy_method(dataproxy, *args, **kwargs)
File
"/home/mike/projects/slashdb/slashdb/models/sqlalchemy_sdb/dataproxy.py",
line 399, in _add_all
self.db_session.flush()
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/orm/session.py"
, line 2254, in flush
self._flush(objects)
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/orm/session.py"
, line 2380, in _flush
transaction.rollback(_capture_exception=True)
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py"
, line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/orm/session.py"
, line 2344, in _flush
flush_context.execute()
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py"
, line 391, in execute
rec.execute(self)
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py"
, line 556, in execute
uow
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py"
, line 181, in save_obj
mapper, table, insert)
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py"
, line 866, in _emit_insert_statements
execute(statement, params)
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/engine/base.py"
, line 948, in execute
return meth(self, multiparams, params)
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/sql/elements.py"
, line 269, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/engine/base.py"
, line 1060, in _execute_clauseelement
compiled_sql, distilled_params
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/engine/base.py"
, line 1132, in _execute_context
None, None)
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/engine/base.py"
, line 1413, in _handle_dbapi_exception
exc_info
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/util/compat.py"
, line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/engine/base.py"
, line 1127, in _execute_context
context = constructor(dialect, self, conn, *args)
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/engine/default.py"
, line 650, in _init_compiled
self._process_executesingle_defaults()
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/engine/default.py"
, line 1342, in _process_executesingle_defaults
val = self.get_insert_default(c)
File
"/home/mike/envs/slashdb/lib/python2.7/site-packages/sqlalchemy/engine/default.py"
, line 1287, in get_insert_default
return self._exec_default(column, column.default, column.type)
StatementError: (exceptions.TypeError) _exec_default() takes exactly 3
arguments (4 given) [SQL: u'INSERT INTO timesheet (user_id, project_id,
date) VALUES (?, ?, ?)'] [parameters: [{u'project_id': 1, u'user_id': 1}]]
Python 2.7.13
pip freeze
atomicwrites==1.1.5
attrs==18.1.0
backports.functools-lru-cache==1.5
beautifulsoup4==4.6.0
funcsigs==1.0.2
hupper==1.3
ibm-db==2.0.8
ibm-db-sa==0.3.3
lxml==4.2.1
Mako==1.0.7
MarkupSafe==1.0
mock==2.0.0
more-itertools==4.2.0
MySQL-python==1.2.5
nose==1.3.7
passlib==1.7.1
Paste==2.0.3
PasteDeploy==1.5.2
PasteScript==2.0.2
pbr==4.0.3
plaster==1.0
plaster-pastedeploy==0.5
pluggy==0.6.0
portalocker==1.2.1
psycopg2==2.7.4
py==1.5.3
pyodbc==4.0.23
pyramid==1.9.2
[sqlalchemy] automap multithreading
Hi SQLAlchemy Group, I've been experimenting with automap (in SQLAlchemy 1.0.20) a bit and I wonder how safe is to automap multiple databases in threads because I run into random errors of classes not being mapped: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Class 'sqlalchemy.ext.automap.Manufacturer' is not mapped I was a bit surprised the class was added to automap module which could cause conflicts when automapping databases simultaneously in threads. Is this intentional that the automapped class is added to automap module or I messed something up? I was digging in sqlalchem/ext/automap.py and I found that a class method AutomapBase.prepare uses class sqlalchemy.ext.declarative.base._DeferredMapperConfig to store configs. This class would be shared between threads. Is it possible one thread flashes configs and other breaks automap process on other thread? I can't reproduce the problem when secluding automap from my application so it's hard to give code example. I'm sorry that question is not very precise but maybe you could give me any hint where to look? Kind regards, Michal -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] complex primary key with server_default timestamp
Just curious. Let's say we have a complex primary key of user_id (integer),
project_id (integer) and date (timestamp). After adding and committing we
don't have the PK and we won't be able to update it. Is that right?
If it was auto-increment integer then it would probably be fine. PK would
be fetched using last_inster_id() in mysql or similar method in other
dialects.
from sqlalchemy import create_engine, Column, Integer, TIMESTAMP,
FetchedValue
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
engine =
create_engine('mysql+mysqldb://root:@192.168.1.12:3306/test?charset=utf8',
echo=True)
class Timesheet(Base):
__tablename__ = 'timesheet'
user_id = Column(Integer, primary_key=True)
project_id = Column(Integer, primary_key=True)
date = Column(TIMESTAMP(), primary_key=True, nullable=False,
server_default=FetchedValue())
session = sessionmaker(engine)()
t1 = Timesheet(user_id=1, project_id=1)
session.add(t1)
session.commit()
print t1.date
2017-05-15 11:46:09,411 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'sql_mode'
2017-05-15 11:46:09,411 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 11:46:09,412 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2017-05-15 11:46:09,412 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 11:46:09,412 INFO sqlalchemy.engine.base.Engine show collation
where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2017-05-15 11:46:09,413 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 11:46:09,413 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS CHAR(60)) AS anon_1
2017-05-15 11:46:09,413 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 11:46:09,414 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS CHAR(60)) AS anon_1
2017-05-15 11:46:09,414 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 11:46:09,414 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin
AS anon_1
2017-05-15 11:46:09,414 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 11:46:09,415 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-05-15 11:46:09,416 INFO sqlalchemy.engine.base.Engine INSERT INTO
timesheet (user_id, project_id) VALUES (%s, %s)
2017-05-15 11:46:09,416 INFO sqlalchemy.engine.base.Engine (1, 1)
2017-05-15 11:46:09,416 INFO sqlalchemy.engine.base.Engine COMMIT
Traceback (most recent call last):
File "/home/mike/projects/sandbox/box7.py", line 21, in
print t1.date
File
"/home/mike/envs/slashdb9/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py",
line 237, in __get__
return self.impl.get(instance_state(instance), dict_)
File
"/home/mike/envs/slashdb9/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py",
line 578, in get
value = state._load_expired(state, passive)
File
"/home/mike/envs/slashdb9/local/lib/python2.7/site-packages/sqlalchemy/orm/state.py",
line 474, in _load_expired
self.manager.deferred_scalar_loader(self, toload)
File
"/home/mike/envs/slashdb9/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",
line 669, in load_scalar_attributes
raise orm_exc.ObjectDeletedError(state)
sqlalchemy.orm.exc.ObjectDeletedError: Instance '' has been deleted, or its row is otherwise not present.
2017-05-15 11:46:09,419 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-05-15 11:46:09,420 INFO sqlalchemy.engine.base.Engine SELECT
timesheet.user_id AS timesheet_user_id, timesheet.project_id AS
timesheet_project_id, timesheet.date AS timesheet_date
FROM timesheet
WHERE timesheet.user_id = %s AND timesheet.project_id = %s AND
timesheet.date IS NULL
2017-05-15 11:46:09,420 INFO sqlalchemy.engine.base.Engine (1, 1)
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: orm add() or table insert() without implicit_returning
Me again. Just wanted to say that engine = create_engine(connstr, implicit_returning=False, use_scope_identity =True, echo=True) actually works as expected. I was able to add new objects without select permission on table :) On Wednesday, August 10, 2016 at 5:33:30 PM UTC+2, mdob wrote: > > Hi Everyone, > > How to save new objects to database without SELECT permission on the table? > > I tried using implicit_returning and use_scope_identity in create_engine. > engine = create_engine(connstr, implicit_returning=False, > use_scope_identity=False, echo=True) > db_session = sessionmaker(engine)() > db_session.add_all(new_resources) > > No luck with that. There's always some sort of select on table that > returned row identity. > I found on StackOverflow that I could execute insert on table with > insert(inline=True) and list of raw data to send. > > self.db_session.execute(query_class.__table__.insert(inline=True), > new_resources_data) > > Though with that approach I need to extract data from ORM objects and > manually map them into dict with column names. > > Any suggestions? > -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] orm add() or table insert() without implicit_returning
Hi Everyone, How to save new objects to database without SELECT permission on the table? I tried using implicit_returning and use_scope_identity in create_engine. engine = create_engine(connstr, implicit_returning=False, use_scope_identity =False, echo=True) db_session = sessionmaker(engine)() db_session.add_all(new_resources) No luck with that. There's always some sort of select on table that returned row identity. I found on StackOverflow that I could execute insert on table with insert(inline=True) and list of raw data to send. self.db_session.execute(query_class.__table__.insert(inline=True), new_resources_data) Though with that approach I need to extract data from ORM objects and manually map them into dict with column names. Any suggestions? -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT
@event.listens_for(Table, 'column_reflect')
def receive_column_reflect(inspector, table, column_info):
if isinstance(column_info['type'], TIMESTAMP):
column_info['default'] = FetchedValue()
table = Table(table_name, metadata, autoload=True, autoload_with=engine,
include_columns=columns)
File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 416, in
__new__
File "build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py", line
60, in __exit__
File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 411, in
__new__
File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 484, in
_init
File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 496, in
_autoload
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1972,
in run_callable
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1477,
in run_callable
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 364
, in reflecttable
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py", line
568, in reflecttable
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py", line
618, in _reflect_column
File "", line 2, in text
File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 1425,
in _create_text
File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 1289,
in __init__
TypeError: expected string or buffer
Something's not right.
On Friday, November 14, 2014 at 3:48:59 PM UTC+1, Michael Bayer wrote:
>
> probably (maybe we should improve on our end, though). but when you’re
> autoloading, you can set this default up automatically using the
> column_reflect event:
>
>
> http://docs.sqlalchemy.org/en/rel_0_9/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect
>
> as you receive events here, look at the “type” to see if its TIMESTAMP,
> and if so, populate the column_info dictionary with column_info[‘default’]
> = FetchedValue().
>
>
> On Nov 14, 2014, at 7:04 AM, mdob <mike.do...@gmail.com >
> wrote:
>
> Hi,
>
> It's been some time since this topic was created. Has anything changed on
> that matter or manually setting column.server_default=FetchedValue() is
> still the best way to do it?
>
>
> Kind regards,
> Michał
>
> On Thursday, September 15, 2011 7:58:49 AM UTC+2, Matt Bodman wrote:
>>
>> Hi,
>>
>> I am autoloading tables from an MSSQL db. A lot of the tables have
>> the MSSQL TIMESTAMP column. So, when inserting to the table, I get an
>> IntegrityError:
>>
>> sqlalchemy.exc.IntegrityError: (IntegrityError) ('23000', '[23000]
>> [FreeTDS][SQL Server]Cannot insert an explicit value into a timestamp
>> column. Use INSERT with a column list to exclude the timestamp column,
>> or insert a DEFAULT into the timestamp column. (273) (SQLPrepare)'
>>
>> Is there a way around this without having to map every column
>> explicitly?
>>
>> Thanks,
>>
>> Matt
>>
>>
> --
> 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 post to this group, send email to sqlal...@googlegroups.com
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT
Maybe this should go into server_default. Because cole belowe worked fine.
for col in table.columns:
if isinstance(col.type, TIMESTAMP):
col.server_default = FetchedValue()
Updating column_info['server_default'] = FetchedValue() in event handler
didn't work.
On Friday, December 11, 2015 at 7:12:31 PM UTC+1, mdob wrote:
>
>
> @event.listens_for(Table, 'column_reflect')
> def receive_column_reflect(inspector, table, column_info):
> if isinstance(column_info['type'], TIMESTAMP):
> column_info['default'] = FetchedValue()
>
>
>
> table = Table(table_name, metadata, autoload=True, autoload_with=engine,
> include_columns=columns)
> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 416,
> in __new__
> File "build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py",
> line 60, in __exit__
> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 411,
> in __new__
> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 484,
> in _init
> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 496,
> in _autoload
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1972
> , in run_callable
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1477
> , in run_callable
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line
> 364, in reflecttable
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py",
> line 568, in reflecttable
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py",
> line 618, in _reflect_column
> File "", line 2, in text
> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line
> 1425, in _create_text
> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line
> 1289, in __init__
> TypeError: expected string or buffer
>
> Something's not right.
>
> On Friday, November 14, 2014 at 3:48:59 PM UTC+1, Michael Bayer wrote:
>>
>> probably (maybe we should improve on our end, though). but when you’re
>> autoloading, you can set this default up automatically using the
>> column_reflect event:
>>
>>
>> http://docs.sqlalchemy.org/en/rel_0_9/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect
>>
>> as you receive events here, look at the “type” to see if its TIMESTAMP,
>> and if so, populate the column_info dictionary with column_info[‘default’]
>> = FetchedValue().
>>
>>
>> On Nov 14, 2014, at 7:04 AM, mdob <mike.do...@gmail.com> wrote:
>>
>> Hi,
>>
>> It's been some time since this topic was created. Has anything changed on
>> that matter or manually setting column.server_default=FetchedValue() is
>> still the best way to do it?
>>
>>
>> Kind regards,
>> Michał
>>
>> On Thursday, September 15, 2011 7:58:49 AM UTC+2, Matt Bodman wrote:
>>>
>>> Hi,
>>>
>>> I am autoloading tables from an MSSQL db. A lot of the tables have
>>> the MSSQL TIMESTAMP column. So, when inserting to the table, I get an
>>> IntegrityError:
>>>
>>> sqlalchemy.exc.IntegrityError: (IntegrityError) ('23000', '[23000]
>>> [FreeTDS][SQL Server]Cannot insert an explicit value into a timestamp
>>> column. Use INSERT with a column list to exclude the timestamp column,
>>> or insert a DEFAULT into the timestamp column. (273) (SQLPrepare)'
>>>
>>> Is there a way around this without having to map every column
>>> explicitly?
>>>
>>> Thanks,
>>>
>>> Matt
>>>
>>>
>> --
>> 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 post to this group, send email to sqlal...@googlegroups.com.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>>
>>
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] control over metadata reflect
metadata = MetaData() metadata.reflect(engine) Base = automap_base(metadata=metadata) Base.prepare() That did a real nice job but I wanted to skip some columns from being mapped (binary types actually at the moment) I see metadata.tables['TableName'].columns to be ImmutableColumnCollection so there's probably no way to exclude column after reflect Is there a way to skip some columns from being reflected? Other option I can think of is insp = reflection.Inspector.from_engine(engine) and do the mapping to declarative manually. Any thoughts on that? -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: control over metadata reflect
I have tried column_reflect event and couldn't skip column. Like you said. I'll have to go a bit dipper and see how do events actually work. I tried at first to use exclude_columns but they're not acquired from kwargs in Table._init. The _autoload is called without exclude_columns argument which takes empty tuple as default https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/schema.py#L484 On Thursday, December 10, 2015 at 10:57:03 AM UTC+1, mdob wrote: > > metadata = MetaData() > metadata.reflect(engine) > > Base = automap_base(metadata=metadata) > Base.prepare() > > > That did a real nice job but I wanted to skip some columns from being > mapped (binary types actually at the moment) > I see metadata.tables['TableName'].columns to be ImmutableColumnCollection > so there's probably no way to exclude column after reflect > > Is there a way to skip some columns from being reflected? > > Other option I can think of is > > insp = reflection.Inspector.from_engine(engine) > and do the mapping to declarative manually. > > Any thoughts on that? > > > > > -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: control over metadata reflect
I think I got it. metadata = MetaData() insp = reflection.Inspector.from_engine(engine) for table_name in insp.get_table_names(self.db_schema): columns = [col['name'] for col in insp.get_columns(table_name, self. db_schema) if not isinstance(col['type'], IMAGE)] Table(table_name, metadata, autoload=True, autoload_with=engine, include_columns=columns) Base = automap_base(metadata=metadata) Base.prepare() It's bit pain that reflection is done twice (first inspector then by autoload in Table), but it does the job without ripping off SQLAlchemy code. Maybe if Table could accept output of insp.reflecttable it would be a way to ease that. Zzzeek, what do you think about it? Does it make sense? On Thursday, December 10, 2015 at 10:57:03 AM UTC+1, mdob wrote: > > metadata = MetaData() > metadata.reflect(engine) > > Base = automap_base(metadata=metadata) > Base.prepare() > > > That did a real nice job but I wanted to skip some columns from being > mapped (binary types actually at the moment) > I see metadata.tables['TableName'].columns to be ImmutableColumnCollection > so there's probably no way to exclude column after reflect > > Is there a way to skip some columns from being reflected? > > Other option I can think of is > > insp = reflection.Inspector.from_engine(engine) > and do the mapping to declarative manually. > > Any thoughts on that? > > > > > -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] sqlite filter datetime
Hi,
It seems I have different result when filtering sqlite database on datetime
column.
I don't get any results when filtering declarative way
Invoice.InvoiceDate == datetime.datetime(2007, 01, 01)
same when hen executing raw query with engine.execute
'select * from Invoice where InvoiceDate = 2007-01-01'
but it works when using sqlite datetime function in sql statement
'select * from Invoice where InvoiceDate = datetime(2007-01-01) '
My objective is to be able to filter declarative way and I'm not sure
either I am missing something or there's an issue with declarative
filtering. Any Ideas?
Below some testing code and here's link to chinook
databases http://chinookdatabase.codeplex.com/
Any help much appreciated.
Mike
import datetime
from sqlalchemy import create_engine, text, Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Invoice(Base):
__tablename__ = 'Invoice'
InvoiceId = Column(Integer, primary_key=True)
InvoiceDate = Column(DateTime)
def __repr__(self):
return 'Invoice {}'.format(self.InvoiceId)
engine = create_engine('sqlite:home/mike/chinook.sqlite')
def declarative():
Session = sessionmaker(bind=engine)
session = Session()
query = session.query(Invoice)
query = query.filter(Invoice.InvoiceDate == datetime.datetime(2007, 01,
01))
result = query.all()
for invoice in result:
print invoice.InvoiceId, invoice.InvoiceDate
print 'declarative:', len(result)
def core():
sql = text('select * from Invoice where InvoiceDate = 2007-01-01')
query = engine.execute(sql)
result = query.fetchall()
print 'core: ', len(result)
def core_fun():
sql = text('select * from Invoice where InvoiceDate =
datetime(2007-01-01) ')
query = engine.execute(sql)
result = query.fetchall()
print 'core with datetime fun: ', len(result)
declarative()
core()
core_fun()
Result
declarative: 0
core: 0
core with datetime fun: 1
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] sqlite filter datetime
The database is OK. I found out that calling func.datetime on value in
declarative filtering fixed query and returned expected result.
query = query.filter(Invoice.InvoiceDate == func.datetime(datetime.datetime(
2007, 1, 1)))
echo:
SELECT Invoice.InvoiceId AS Invoice_InvoiceId, Invoice.InvoiceDate
AS Invoice_InvoiceDate
FROM Invoice
WHERE Invoice.InvoiceDate = datetime(:datetime_1)
2015-06-04 15:13:58,297 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-06-04 15:13:58,298 INFO sqlalchemy.engine.base.Engine SELECT Invoice.
InvoiceId AS Invoice_InvoiceId, Invoice.InvoiceDate AS
Invoice_InvoiceDate
FROM Invoice
WHERE Invoice.InvoiceDate = datetime(?)
2015-06-04 15:13:58,298 INFO sqlalchemy.engine.base.Engine ('2007-01-01
00:00:00.00',)
declarative: 1
Now I'm thinking how I could make it more general for any SQLite Datetime
type.
Having read
http://docs.sqlalchemy.org/en/latest/core/custom_types.html#applying-sql-level-bind-result-processing
I'm trying with making custom type that would by default call that
func.datetime.
class SQLiteDateTime(DateTime):
def bind_expression(self, bindvalue):
return func.datetime(bindvalue, type_=self)
So far no luck but is it right approach for that problem?
Thanks,
Mike
On Thursday, June 4, 2015 at 1:50:58 PM UTC-4, Michael Bayer wrote:
On 6/4/15 11:57 AM, mdob wrote:
Hi,
It seems I have different result when filtering sqlite database on
datetime column.
I don't get any results when filtering declarative way
Invoice.InvoiceDate == datetime.datetime(2007, 01, 01)
same when hen executing raw query with engine.execute
'select * from Invoice where InvoiceDate = 2007-01-01'
but it works when using sqlite datetime function in sql statement
'select * from Invoice where InvoiceDate = datetime(2007-01-01) '
My objective is to be able to filter declarative way and I'm not sure
either I am missing something or there's an issue with declarative
filtering. Any Ideas?
the literal string 'datetime(2007-01-01)' is probably what is directly
present in the database.I'd guess some prior version of the application
or other weird event is responsible for this, I'd log into the database
using sqlite3 directly just to see if this is the case, and then look into
repairing that data.
Below some testing code and here's link to chinook databases
http://chinookdatabase.codeplex.com/
Any help much appreciated.
Mike
import datetime
from sqlalchemy import create_engine, text, Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Invoice(Base):
__tablename__ = 'Invoice'
InvoiceId = Column(Integer, primary_key=True)
InvoiceDate = Column(DateTime)
def __repr__(self):
return 'Invoice {}'.format(self.InvoiceId)
engine = create_engine('sqlite:home/mike/chinook.sqlite')
def declarative():
Session = sessionmaker(bind=engine)
session = Session()
query = session.query(Invoice)
query = query.filter(Invoice.InvoiceDate == datetime.datetime(2007,
01, 01))
result = query.all()
for invoice in result:
print invoice.InvoiceId, invoice.InvoiceDate
print 'declarative:', len(result)
def core():
sql = text('select * from Invoice where InvoiceDate = 2007-01-01')
query = engine.execute(sql)
result = query.fetchall()
print 'core: ', len(result)
def core_fun():
sql = text('select * from Invoice where InvoiceDate =
datetime(2007-01-01) ')
query = engine.execute(sql)
result = query.fetchall()
print 'core with datetime fun: ', len(result)
declarative()
core()
core_fun()
Result
declarative: 0
core: 0
core with datetime fun: 1
--
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: skipping MSSQL TIMESTAMP column on INSERT
Hi,
It's been some time since this topic was created. Has anything changed on
that matter or manually setting column.server_default=FetchedValue() is
still the best way to do it?
Kind regards,
Michał
On Thursday, September 15, 2011 7:58:49 AM UTC+2, Matt Bodman wrote:
Hi,
I am autoloading tables from an MSSQL db. A lot of the tables have
the MSSQL TIMESTAMP column. So, when inserting to the table, I get an
IntegrityError:
sqlalchemy.exc.IntegrityError: (IntegrityError) ('23000', '[23000]
[FreeTDS][SQL Server]Cannot insert an explicit value into a timestamp
column. Use INSERT with a column list to exclude the timestamp column,
or insert a DEFAULT into the timestamp column. (273) (SQLPrepare)'
Is there a way around this without having to map every column
explicitly?
Thanks,
Matt
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] calchipan
Hi, I was trying to use SQLAlchemy with Pandas - CALCHIPAN. Installed sqlalchemy, numpy and pandas from pip, then calchipan from source. SQLAlchemy==0.9.8 argparse==1.2.1 calchipan==0.1.0dev ipython==2.3.0 numpy==1.9.0 pandas==0.15.0 python-dateutil==2.2 pytz==2014.7 six==1.8.0 wsgiref==0.1.2 tried the example from https://bitbucket.org/zzzeek/calchipan/ and got TypeError: value parameter must be a scalar or dict, but you passed a list from pandas full traceback: http://pastie.org/9674778 Am I using wrong versions? -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] oracle reflect with duplicated tables (schema casing)
Hi,
I got into an interesting issue where I receive duplicated tables if I use
capital letters schema in reflect method.
Ubuntu 12.04 with packages
oracle-instantclient11.2-basiclite-11.2.0.3.0-1.i386.rpm,
oracle-instantclient11.2-devel-11.2.0.3.0-1.i386.rpm,
oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.i386.rpm installed using
alien; Python 2.7; SQLAlchemy 0.7.9 and 0.8.2; cx-Oracle 5.1.2
DB is Oracle 11.2 XE on Windows with two simple tables and relation between
them
Customer Address
Tables were created like: CREATE TABLE MIKE.CUSTOMER ... so they should
be case insensitive.
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('oracle://mike:pass@192.168.16.1/xe', echo=True)
Base = declarative_base(bind=engine)
Base.metadata.reflect(schema='mike')
tables = Base.metadata.tables.keys()
print tables
[u'mike.customer', u'mike.address']
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('oracle://mike:pass@192.168.16.1/xe', echo=True)
Base = declarative_base(bind=engine)
Base.metadata.reflect(schema='MIKE')
tables = Base.metadata.tables.keys()
print tables
[u'MIKE.customer', u'MIKE.address', u'mike.address']
What I found in sqlalchemy code is that table mike.address is mapped and
added to Base.metadata.tables dictionary when table MIKE.customer is being
mapped. I guess that's because mike.address parent table to MIKE.customer.
The thing is it's added lowercase. Next, MIKE.address is added in a normal
way.
Well I'm not sure if this is a bug or I'm missing something, that's why I
would like to know your opinion on that.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Seemingly unnecessary queries for object already in the session get executed when using create_eager option.
Consider a simple model like this:
Library 1---* Shelf 1---* Book
Accordingly our model has the following relationships declared:
{tu wkelj kod z relationship wlaczajac backrefy)
We will write a query with a join to get all three objects at once
SELECT *
FROM Library
JOIN Shelf ON Library.id = Shelf.library_id
JOIN Shelf ON Shelf.id = Book.shelf_id
WHERE Library.name = 'TheLibrary' AND Shelf.loc = 'A1' AND Book.title
= 'Ender''s Game'
q = session.query(Library)
q = q.join(Shelf)
q = q.join(Book)
q = q.filter(Library.name = TheLibrary)
q = q.filter(Shelf.loc = A1)
q = q.filter(Book.title = Ender's Game)
q = q.options(contains_eager('shelves', 'books'))
result = q.all()
Let's assume is only one record that meets the criteria (we have only
one book with this title on shelf A1). We expect to get a single
Library object with one-element collection of shelves, which has one
element colection of books. This is indeed what seems to come back.
However, upon access to a backref'd library _attribute_ in Shelf
object another database query again Library gets automatically
executed. This seems unneccesary because the related Library object is
already loaded into the session.
I've figured out that it has something to do with backref to from
Shelf to Library so I added a line:
q = q.options(contains_eager('shelves', 'Library'))
and now the supurios query does not run.
1. Are these backref paths required? It can get messy with deeper
relationships.
2. Indidentally accessing shelf attribute on Book DOES NOT result in a
query? Why this inconsitence or am I missing something?
3. How will create_eager behave with many-to-many relationship?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
