[sqlalchemy] Re: control over metadata reflect

[mdob]

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.

Re: [sqlalchemy] Determination of string lengths

[Mike Bayer]

On 12/10/2015 12:08 PM, Jonathan Vanasco wrote:
> `sqlalchemy.func` does not map anything.  It is a namespace for a
> factory generator.   anything you access with it becomes a function of
> that caller's name.
> 
> for example:
> 
>  filter( func.foo(table.column) > 1 )
> 
> produces
> 
>   WHERE foo(table.column) > 1
> 
> sqlalchemy generates the `foo` function dynamically though the `func`
> namespace.
> 
> In your example, `func.length` creates the sql "LENGTH()" not "LEN()".
>  It works because your backend supports "LENGTH" not "LEN".  Most
> databases use LENGTH (postgres, mysql, oracle, sqlite).  Only MsSQL uses
> LEN, and firebird has a completely different approach with CHAR_LENGTH,
> BIT_LENGTH, etc.
> 
> I don't think any more portability has ever been needed, because the
> functions are either:
> 
> * standardized across most databases due to common standards
> * highly specfiic to a single database
> 
> Trying to create a system that standardizes how every database handles
> ancillary internal function "concepts" would be overwhelming and of
> little real utility.   

Well, I can see how it would be useful for general categories of
functions - string functions, math functions, and especially date
functions, which would be a huge job as all databases handle dates very
differently.  But at the very least it would probably less confusing
if these "translated" functions were in a different namespace besides
func., because, if some of the functions in func. translate and others
don't, it's not consistent and the user has no idea what will happen.
So first off, if we added that feature, people at this point wouldn't
even know about it and it wouldn't get enough test coverage.  Then
secondly, it's of course a huge maintenance issue especially for date
functions and such.

Right now, when you really have a SQL function that you need to
translate across backends, we supply the @compiles approach.  See the
examples at
http://docs.sqlalchemy.org/en/rel_1_0/core/compiler.html#further-examples.








> 
> If you really wanted to pursue this, I'd imagine you would work on the
> SQL compliler or just inspect the db connection when you generate the
> query and conditionally use different forms.  
> 
> -- 
> 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.

-- 
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] table_per_relation ORM example and declarative objects

[Mike Bayer]

On 12/10/2015 10:21 AM, Michal Petrucha wrote:
> Hello alchemists,
> 
> There's something that's been bugging me for a while now. I even 
> asked about it on [stackoverflow][1], but it didn't get much 
> attention there. It's been suggested to me on IRC that this
> mailing list might be a better place for this question.
> 
> When you take a look at the [table_per_relation ORM example][2], 
> you can see that the argument passed to `ForeignKey` [here][3] is 
> the “raw” “tablename.column”. However, since the entire point of 
> declarative is to use higher-level constructs to abstract away
> some of the lower-level SQL details, it would make sense to me to
> use `cls.id` here instead. (I want this here declarative field to
> point to this other declarative field, instead of saying, I want
> this declarative field to point to this SQL column of that SQL
> table.)

the first thing I don't understand is that the code you refer to is
locating the correct name dynamically.  So why does it matter?   You
aren't typing in the name of the table or class yourself.


Second thing is, you can pass the class-bound column to the
ForeignKey, sure, just as an object, not a string:

ForeignKey(WhateverClass.id)

it just requires that you have WhateverClass already available.





> 
> The problem is, when you try to do that, you'll get the following 
> error:
> 
> (env-tmp)konk@parahippus /tmp $  python table_per_related.py 
> 2015-10-20 13:05:44,366 INFO sqlalchemy.engine.base.Engine SELECT 
> CAST('test plain returns' AS VARCHAR(60)) AS anon_1 2015-10-20 
> 13:05:44,366 INFO sqlalchemy.engine.base.Engine () 2015-10-20 
> 13:05:44,367 INFO sqlalchemy.engine.base.Engine SELECT CAST('test 
> unicode returns' AS VARCHAR(60)) AS anon_1 2015-10-20 13:05:44,367 
> INFO sqlalchemy.engine.base.Engine () 2015-10-20 13:05:44,367 INFO 
> sqlalchemy.engine.base.Engine PRAGMA table_info("supplier_address")
> 2015-10-20 13:05:44,367 INFO sqlalchemy.engine.base.Engine ()
> 2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine PRAGMA
> table_info("customer") 2015-10-20 13:05:44,368 INFO
> sqlalchemy.engine.base.Engine () 2015-10-20 13:05:44,368 INFO
> sqlalchemy.engine.base.Engine PRAGMA table_info("supplier")
> 2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine ()
> 2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine PRAGMA 
> table_info("customer_address") 2015-10-20 13:05:44,368 INFO 
> sqlalchemy.engine.base.Engine () Traceback (most recent call last):
> File "table_per_related.py", line 81, in  
> Base.metadata.create_all(engine) File 
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/schema.py",
>
> 
line 3431, in create_all tables=tables) File
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
>
> 
line 1726, in _run_visitor conn._run_visitor(visitorcallable,
> element, **kwargs) File 
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
>
> 
line 1357, in _run_visitor **kwargs).traverse_single(element) File
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py",
>
> 
line 120, in traverse_single return meth(obj, **kw) File
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/ddl.py", 
> line 713, in visit_metadata self.traverse_single(table, 
> create_ok=True) File 
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py",
>
> 
line 120, in traverse_single return meth(obj, **kw) File
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/ddl.py", 
> line 732, in visit_table 
> self.connection.execute(CreateTable(table)) File 
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
>
> 
line 841, in execute return meth(self, multiparams, params) File
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/ddl.py", 
> line 69, in _execute_on_connection return 
> connection._execute_ddl(self, multiparams, params) File 
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
>
> 
line 889, in _execute_ddl compiled = ddl.compile(dialect=dialect)
> File "", line 1, in  File 
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/elements.py",
>
> 
line 493, in compile return self._compiler(dialect, bind=bind,
> **kw) File 
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/ddl.py", 
> line 27, in _compiler return dialect.ddl_compiler(dialect, self, 
> **kw) File 
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py",
>
> 
line 199, in __init__ self.string = self.process(self.statement,
> **compile_kwargs) File 
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py",
>
> 
line 222, in process return obj._compiler_dispatch(self, **kwargs)
> File 
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py",
>
> 
line 80, in _compiler_dispatch return meth(self, **kw) File
> "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py",
>
> 
line 2415, in visit_create_table const =
> self.create_table_constraints(table) Fil

Re: [sqlalchemy] Re: control over metadata reflect

[Mike Bayer]

On 12/10/2015 08:06 AM, mdob wrote:
> I think I got it.
> 
> |
> ||metadata =MetaData()
> ||insp =reflection.Inspector.from_engine(engine)
> fortable_name ininsp.get_table_names(self.db_schema):
> columns =[col['name']forcol
> ininsp.get_columns(table_name,self.db_schema)ifnotisinstance(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.

there's a column_reflect event:

http://docs.sqlalchemy.org/en/rel_1_0/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect

but it does not currently have an option to cause the reflection to skip
the target column (that could be doable).

There's also an exclude_columns argument for Table which should be
easier to use here since you're doing an exclude, not include.


> Maybe if Table could accept output of insp.reflecttable it would be a
> way to ease that.

you can do that.  Take your Inspector object and just run
inspector.reflecttable(your_table, None).  Whatever info it has already
loaded was cached.   But you'll note that reflection also loads up
constraints and indexes so there's a lot more SQL to go for reflection.



> 
> 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.

-- 
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] Determination of string lengths

[Jonathan Vanasco]

`sqlalchemy.func` does not map anything.  It is a namespace for a factory 
generator.   anything you access with it becomes a function of that 
caller's name.

for example:

 filter( func.foo(table.column) > 1 )

produces

  WHERE foo(table.column) > 1

sqlalchemy generates the `foo` function dynamically though the `func` 
namespace.

In your example, `func.length` creates the sql "LENGTH()" not "LEN()".  It 
works because your backend supports "LENGTH" not "LEN".  Most databases use 
LENGTH (postgres, mysql, oracle, sqlite).  Only MsSQL uses LEN, and 
firebird has a completely different approach with CHAR_LENGTH, BIT_LENGTH, 
etc.

I don't think any more portability has ever been needed, because the 
functions are either:

* standardized across most databases due to common standards
* highly specfiic to a single database

Trying to create a system that standardizes how every database handles 
ancillary internal function "concepts" would be overwhelming and of little 
real utility.   

If you really wanted to pursue this, I'd imagine you would work on the SQL 
compliler or just inspect the db connection when you generate the query and 
conditionally use different forms.  

-- 
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] copy tables with circular dependency

[Mike Bayer]

On 12/10/2015 08:02 AM, Leily Zafari wrote:
> Hello,
> I want to copy some tables from one database to another using
> SQLAlchemy. The tables have circular dependency which is resolved by
> use_alter attribute.
>  
> 
> class Product(Base):
> __tablename__ = 'products'
> Int_Class_ID = Column(Integer, primary_key=True)
> components = relationship("Component",
> passive_deletes=True,cascade = "all,delete-orphan",lazy='dynamic' )
>  
> class Component(Base):
> __tablename__ = 'components'
> Int_Class_ID = Column(Integer, primary_key=True)
> product_id = Column(Integer, ForeignKey('products.Int_Class_ID'
> , ondelete='cascade'),nullable=True)
> connection_id =
> Column(Integer,ForeignKey('connections.Int_Class_ID' ,
> ondelete='cascade',use_alter=True,name='connection_component'))
> ports = relationship("Port", passive_deletes=True,cascade =
> "all,delete-orphan",lazy='dynamic' )
> Name = Column(Text)
>  
> class Port(Base):
> __tablename__ = 'ports'
> Int_Class_ID = Column(Integer, primary_key=True)
> component_id = Column(Integer,
> ForeignKey('components.Int_Class_ID' , ondelete='cascade'))
> connections = relationship("Connection",
> passive_deletes=True,cascade = "all,delete-orphan",lazy='dynamic' )
> Name = Column(Text)
>  
> class Connection(Base):
> __tablename__ = 'connections'
> Int_Class_ID = Column(Integer, primary_key=True)
> port_id = Column(Integer, ForeignKey('ports.Int_Class_ID' ,
> ondelete='cascade'))
> components = relationship("Component", post_update=True,
> passive_deletes=True,cascade = "all,delete-orphan",lazy='dynamic' )
> Name = Column(Text)
> 
>  
> When I create tables with their columns and insert the data from the old
> tables to the new tables, I have an IntegrityError, due to ForeignKey
> constraint on the 'Component' table.
> I suppose this is due to the circular dependency. So I tried to drop all
> constraints, migrate the data and then add the constraints. Until the
> data migration is successful, but I do not succeed to add the
> constraints to the tables.
>  
> 
> table.append_constraint(Constraint)
> 
> seems not to affect the database. No SQL commands are emitted.
> What is the best way to copy such tables using SQLAlchemy?
> Any help is appreciated. 

OK well you somehow "dropped all constraints", which meant you had to
emit an "ALTER TABLE  DROP CONSTRAINT "
command, so if you have existing tables and you want to add those
constraints back, you'd need to emit "ALTER TABLE  ADD
CONSTRAINT " to do so.
table.append_constraint() only impacts the in-Python definition of the
Table.

If you're looking for helpers that know how to emit the ALTER
statements, you can check out Alembic: http://alembic.readthedocs.org/.




>  
> 
> -- 
> 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.

-- 
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] table_per_relation ORM example and declarative objects

[Michal Petrucha]

Hello alchemists,

There's something that's been bugging me for a while now. I even asked
about it on [stackoverflow][1], but it didn't get much attention
there. It's been suggested to me on IRC that this mailing list might
be a better place for this question.

When you take a look at the [table_per_relation ORM example][2], you
can see that the argument passed to `ForeignKey` [here][3] is the
“raw” “tablename.column”. However, since the entire point of
declarative is to use higher-level constructs to abstract away some of
the lower-level SQL details, it would make sense to me to use `cls.id`
here instead. (I want this here declarative field to point to this
other declarative field, instead of saying, I want this declarative
field to point to this SQL column of that SQL table.)

The problem is, when you try to do that, you'll get the following
error:

(env-tmp)konk@parahippus /tmp $  python table_per_related.py 
2015-10-20 13:05:44,366 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-10-20 13:05:44,366 INFO sqlalchemy.engine.base.Engine ()
2015-10-20 13:05:44,367 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-10-20 13:05:44,367 INFO sqlalchemy.engine.base.Engine ()
2015-10-20 13:05:44,367 INFO sqlalchemy.engine.base.Engine PRAGMA 
table_info("supplier_address")
2015-10-20 13:05:44,367 INFO sqlalchemy.engine.base.Engine ()
2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine PRAGMA 
table_info("customer")
2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine ()
2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine PRAGMA 
table_info("supplier")
2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine ()
2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine PRAGMA 
table_info("customer_address")
2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
  File "table_per_related.py", line 81, in 
Base.metadata.create_all(engine)
  File "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", 
line 3431, in create_all
tables=tables)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 
1726, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 
1357, in _run_visitor
**kwargs).traverse_single(element)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py", line 
120, in traverse_single
return meth(obj, **kw)
  File "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/ddl.py", 
line 713, in visit_metadata
self.traverse_single(table, create_ok=True)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py", line 
120, in traverse_single
return meth(obj, **kw)
  File "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/ddl.py", 
line 732, in visit_table
self.connection.execute(CreateTable(table))
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 841, 
in execute
return meth(self, multiparams, params)
  File "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/ddl.py", 
line 69, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 889, 
in _execute_ddl
compiled = ddl.compile(dialect=dialect)
  File "", line 1, in 
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 
493, in compile
return self._compiler(dialect, bind=bind, **kw)
  File "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/ddl.py", 
line 27, in _compiler
return dialect.ddl_compiler(dialect, self, **kw)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 
199, in __init__
self.string = self.process(self.statement, **compile_kwargs)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 
222, in process
return obj._compiler_dispatch(self, **kwargs)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py", line 80, 
in _compiler_dispatch
return meth(self, **kw)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 
2415, in visit_create_table
const = self.create_table_constraints(table)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 
2452, in create_table_constraints
for constraint in constraints
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 
2450, in 
return ", \n\t".join(p for p in
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchem

Re: [sqlalchemy] Determination of string lengths

[SF Markus Elfring]

> I don't know which database you are using,
> but in postgresql there certainly is a function called "length"

How do you think about to avoid the direct reuse of functions
which are specific for a few database software implementations?

Is there any more portability possible around string functions?

Regards,
Markus

-- 
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] Determination of string lengths

['Robert Forkel' via sqlalchemy]

I don't know which database you are using, but in postgresql there
certainly is a function called "length" (see
http://www.postgresql.org/docs/9.1/static/functions-string.html)

On Thu, Dec 10, 2015 at 2:04 PM, SF Markus Elfring <
elfr...@users.sourceforge.net> wrote:

> > As stated in the docs:
> > "Note that any name not known to func generates the function name as is
> > - there is no restriction on what SQL functions can be called,
> > known or unknown to SQLAlchemy, built-in or user defined."
>
> I get an error message like "Function len(text) does not exist." if I try
> something out like the following on a string field.
>
> …
>   for length, \
>   incidence in session.query(func.len(position.label),
>  func.count(position.label)
> ).group_by(func.len(position.label)) \
>  .order_by(func.len(position.label)):
> …
>
>
> Is it interesting that the following approach seems to work instead?
>
> …
>   for length, \
>   incidence in session.query(func.length(position.label),
>  func.count(position.label)
>
> ).group_by(func.length(position.label)) \
>
>  .order_by(func.length(position.label)):
> …
>
>
> How is the function "length" mapped to the SQL function "LEN"?
>
> Regards,
> Markus
>
> --
> 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.
>

-- 
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

[mdob]

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.

Re: [sqlalchemy] Determination of string lengths

[SF Markus Elfring]

> As stated in the docs:
> "Note that any name not known to func generates the function name as is
> - there is no restriction on what SQL functions can be called,
> known or unknown to SQLAlchemy, built-in or user defined."

I get an error message like "Function len(text) does not exist." if I try
something out like the following on a string field.

…
  for length, \
  incidence in session.query(func.len(position.label),
 func.count(position.label)
).group_by(func.len(position.label)) \
 .order_by(func.len(position.label)):
…


Is it interesting that the following approach seems to work instead?

…
  for length, \
  incidence in session.query(func.length(position.label),
 func.count(position.label)
).group_by(func.length(position.label)) \
 .order_by(func.length(position.label)):
…


How is the function "length" mapped to the SQL function "LEN"?

Regards,
Markus

-- 
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] copy tables with circular dependency

[Leily Zafari]

Hello,
I want to copy some tables from one database to another using SQLAlchemy. 
The tables have circular dependency which is resolved by use_alter 
attribute.
 

class Product(Base):
__tablename__ = 'products'
Int_Class_ID = Column(Integer, primary_key=True)
components = relationship("Component", passive_deletes=True,cascade = 
"all,delete-orphan",lazy='dynamic' )
 
class Component(Base):
__tablename__ = 'components'
Int_Class_ID = Column(Integer, primary_key=True)
product_id = Column(Integer, ForeignKey('products.Int_Class_ID' , 
ondelete='cascade'),nullable=True)
connection_id = Column(Integer,ForeignKey('connections.Int_Class_ID' , 
ondelete='cascade',use_alter=True,name='connection_component'))
ports = relationship("Port", passive_deletes=True,cascade = 
"all,delete-orphan",lazy='dynamic' )
Name = Column(Text)
 
class Port(Base):
__tablename__ = 'ports'
Int_Class_ID = Column(Integer, primary_key=True)
component_id = Column(Integer, ForeignKey('components.Int_Class_ID' , 
ondelete='cascade'))
connections = relationship("Connection", passive_deletes=True,cascade = 
"all,delete-orphan",lazy='dynamic' )
Name = Column(Text)
 
class Connection(Base):
__tablename__ = 'connections'
Int_Class_ID = Column(Integer, primary_key=True)
port_id = Column(Integer, ForeignKey('ports.Int_Class_ID' , 
ondelete='cascade'))
components = relationship("Component", post_update=True, 
passive_deletes=True,cascade = "all,delete-orphan",lazy='dynamic' )
Name = Column(Text)

 
When I create tables with their columns and insert the data from the old 
tables to the new tables, I have an IntegrityError, due to ForeignKey 
constraint on the 'Component' table.
I suppose this is due to the circular dependency. So I tried to drop all 
constraints, migrate the data and then add the constraints. Until the data 
migration is successful, but I do not succeed to add the constraints to the 
tables.
 

table.append_constraint(Constraint)

seems not to affect the database. No SQL commands are emitted.
What is the best way to copy such tables using SQLAlchemy?
Any help is appreciated. 
 

-- 
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] Determination of string lengths

['Robert Forkel' via sqlalchemy]

As stated in the docs:

"Note that any name not known to func

generates the function name as is - there is no restriction on what SQL
functions can be called, known or unknown to SQLAlchemy, built-in or user
defined."

I guess there won't be a waiting queue (or better descriptions) for this
category of SQL functions in the sqla docs.


On Thu, Dec 10, 2015 at 12:06 PM, SF Markus Elfring <
elfr...@users.sourceforge.net> wrote:

> Thanks for the link to information I was looking for:
>
> http://stackoverflow.com/questions/15743121/how-to-filter-in-sqlalchemy-by-string-length#answer-15743220
>
> Why is the method "length" not mentioned in the documentation chapter
> "SQL and Generic Functions" so far?
> http://docs.sqlalchemy.org/en/rel_1_1/core/functions.html
>
> Are there any related functions for which a better description is also
> in a waiting queue?
>
> Regards,
> Markus
>
> --
> 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.
>

-- 
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] Determination of string lengths

[SF Markus Elfring]

Thanks for the link to information I was looking for:
http://stackoverflow.com/questions/15743121/how-to-filter-in-sqlalchemy-by-string-length#answer-15743220

Why is the method "length" not mentioned in the documentation chapter
"SQL and Generic Functions" so far?
http://docs.sqlalchemy.org/en/rel_1_1/core/functions.html

Are there any related functions for which a better description is also
in a waiting queue?

Regards,
Markus

-- 
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

[mdob]

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.

Re: [sqlalchemy] Table object to mapped class (model)

[Simon King]

On Thu, Dec 10, 2015 at 5:35 AM, Nana Okyere  wrote:

> I'm using the  sqlalchemy-datatables library to be able to display
> tabular data with the jquery datatables library. One of the parameters that
> I need to create a DataTables object is a mapped class (model) as
> illustrated on line 50 of the flask example here:
> https://github.com/Pegase745/sqlalchemy-datatables/blob/master/examples/flask_tut/flask_tut/__init__.py
> . In that example, User is a mapped class, a model; columns is a list of 
> ColumnDT
> objects.
>
> My problem is, instead of the mapped class, I have a reflected table
> object. A reflected table object is not the same as a mapped class. I'm
> wondering if there's a way to make a mapped class out of the table object.
> I contacted the developer of the sqlalchemy-datatables package and he
> suggested something about automap. So I read everything on
> http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html but I
> still don't know what to do. The table I've reflected is a simple table
> with no foreign keys. It only has some string and integer columns. So is
> there a way for me to get/make a mapped class out of a reflected Table
> object? In my application when, I pass the Table object in place of the
> mapped class, I get error messages. Please help if you can. Thank you.
>
> There are 2 ways to map a class to an existing Table object. You can
either use the declarative extension, and use the "__table__" attribute
instead of "__tablename__":

http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/declarative/table_config.html#using-a-hybrid-approach-with-table

Or you can use classical mapping rather than declarative, which involves
using the sqlalchemy.orm.mapper function to associate a Table with a class:

http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_styles.html#classical-mappings

Hope that helps,

Simon

-- 
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.