RE: [sqlalchemy] can't adapt type 'centroid'
Thanks Mike, I tried it but it seems this mailing list is not working, someone knows if it does? From: mike...@zzzcomputing.com Subject: Re: [sqlalchemy] can't adapt type 'centroid' Date: Mon, 26 Nov 2012 20:43:04 -0500 To: sqlalchemy@googlegroups.com trying to get the attention of the geoalchemy guy here, maybe try their list: https://groups.google.com/forum/?fromgroups#!forum/geoalchemy -- 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.
[sqlalchemy] Oracle dblink reflection
Hi, i have a very bad issue on SA using Oracle database link.
I have 2 Oracle (11g) database instances, one used for my app, in other
stored some data, which i need to use.
I've created a dblink:
CREATE DATABASE LINK link_name CONNECT TO SID IDENTIFIED BY password
USING 'localhost:1521/ORCL'
And synonym for table that i need:
CREATE SYNONYM swells FOR table_name@link_name
There is a restriction: i can only read data from this table, i can't
affect another application by doing something wrong.
So, i trying to reflect on this table:
from sqlalchemy import *
engine = create_engine('oracle://user:password@ip:1521/instance',
encoding = windows-1251, echo='debug')
meta = MetaData(bind=engine)
t = Table(swells, meta,
Column('col1', String, key='col1'),
Column('col2', String, key='col2'),
Column('col3', String, key='col3'),
Column('col_pk', Integer, Sequence(ucol_pk_seq), primary_key=True,
key='id'),
oracle_resolve_synonyms=True,
autoload=True,
autoload_with=engine
)
And i'm getting this error:
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00942: таблица или
представление пользователя не существует
'SELECT column_name, data_type, char_length, data_precision, data_scale,
nullable, data_default FROM ALL_TAB_COLUMNSSELENA_WELLS WHERE table_name =
:table_name AND owner = :owner ORDER BY column_id' {'owner':
owner_name, 'table_name': table_name}
I'm little cofused... The first of all, the owner of this table is
different from my user (this obvious), and ALL_TAB_COLUMNSSELENA_WELLS
should be ALL_TAB_COLUMN@SSELENA_WELLS (cause this is the way to load
linked objects in Oracle).
I've searched in source of SA, found this in dialects/oracle/base.py (from
line 631 in get_columns):
@reflection.cachedef get_columns(self, connection, table_name, schema=None,
**kw):kw arguments can be:
oracle_resolve_synonymsdblinkresolve_synonyms =
kw.get('oracle_resolve_synonyms', False)dblink = kw.get('dblink', '')
info_cache = kw.get('info_cache')(table_name, schema, dblink,
synonym) = \self._prepare_reflection_args(connection, table_name,
schema, resolve_synonyms, dblink,
info_cache=info_cache)columns = []
c = connection.execute(sql.text(SELECT column_name,
data_type, data_length, data_precision, data_scale, nullable,
data_default FROM ALL_TAB_COLUMNS%(dblink)s WHERE table_name
= :table_name AND owner = :owner % {'dblink': dblink}),
table_name=table_name, owner=schema)
The first, nobody used the reflection on dblink before? If this is a bug,
what should i do? I can change the _prepare_reflection_args function in
base.py, adding the @ to dblink and remove owner, but is this a right way?
So, i'm stuck... And sorry for my bad english...
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/qcYnSCxzsk0J.
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.
[sqlalchemy] SQLAlchemy generates multiple connections
Hi all, We are trying to implement a locking mechanism which relies on the incoming/used connections returned/used by SQLAlchemy. The problem we ran into is that SQLAlchemy uses a new database connection after calling expire_all() if we use the autocommit=True functionality. We expected that only one connection is used in one session. More precisely: *IF* you use *autocommit=True* * IF * you call call *expire_all* * AND AFTER THIS* generate a query with more than one affected tables via *filter_by* * THEN* SQLAlchemy will use multiple connections If we don't use poolclass=AssertionPool then the output (see code below/attachment) is: sqlite3.Connection object at 0x1a46030 sqlite3.Connection object at 0x1a46030 sqlite3.Connection object at 0x1a46030 *sqlite3.Connection object at 0x1a54858* The last connection generated by session.query(User).filter_by(country=spain, id=1).one() is clearly a new one / differs from the old one. If we use poolclass=AssertionPool then the output is: sqlite3.Connection object at 0x1d75030 sqlite3.Connection object at 0x1d75030 sqlite3.Connection object at 0x1d75030 Traceback (most recent call last): File sql_bug.py, line 97, in module session.query(User).filter_by(country=spain, id=1).one() File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/query.py, line 2184, in one ret = list(self) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/query.py, line 2227, in __iter__ return self._execute_and_instances(context) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/query.py, line 2243, in _execute_and_instances result = conn.execute(querycontext.statement, self._params) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/engine/base.py, line 1449, in execute params) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/engine/base.py, line 1584, in _execute_clauseelement compiled_sql, distilled_params File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/engine/base.py, line 1651, in _execute_context None, None) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/engine/base.py, line 1647, in _execute_context context = constructor(dialect, self, conn, *args) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/engine/default.py, line 442, in _init_compiled grp,m in enumerate(parameters)] File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py, line 334, in construct_params pd[self.bind_names[bindparam]] = bindparam.effective_value File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/sql/expression.py, line 2695, in effective_value return self.callable() File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py, line 425, in lambda bind_to_col[bindparam._identifying_key]) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py, line 1641, in _get_state_attr_by_column return state.manager[prop.key].impl.get(state, dict_, passive=passive) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py, line 451, in get value = callable_(passive) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/state.py, line 285, in __call__ self.manager.deferred_scalar_loader(self, toload) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py, line 1709, in _load_scalar_attributes only_load_props=attribute_names) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/query.py, line 2513, in _load_on_ident return q.one() File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/query.py, line 2184, in one ret = list(self) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/query.py, line 2227, in __iter__ return self._execute_and_instances(context) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/query.py, line 2241, in _execute_and_instances close_with_result=True) File sql_bug.py, line 18, in debug_connection_from_session conn = m(*args, **kwargs) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/query.py, line 2231, in _connection_from_session **kw) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/session.py, line 730, in connection close_with_result=close_with_result) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/orm/session.py, line 736, in _connection_for_bind return engine.contextual_connect(**kwargs) File /opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/engine/base.py, line 2490, in contextual_connect self.pool.connect(), File
RE: [sqlalchemy] can't adapt type 'centroid'
it does, thanks From: gameji...@hotmail.com To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] can't adapt type 'centroid' Date: Tue, 27 Nov 2012 08:47:53 + Thanks Mike, I tried it but it seems this mailing list is not working, someone knows if it does? From: mike...@zzzcomputing.com Subject: Re: [sqlalchemy] can't adapt type 'centroid' Date: Mon, 26 Nov 2012 20:43:04 -0500 To: sqlalchemy@googlegroups.com trying to get the attention of the geoalchemy guy here, maybe try their list: https://groups.google.com/forum/?fromgroups#!forum/geoalchemy -- 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.
[sqlalchemy] Custom SQL construct for postgres multirow insert
Hello,
I'd like to create a construct for postgres's multirow insert. I asked this
on IRC
and got some great help from supplicant, however I have a few things
missing:
- proper conversion of Python types to SQL (None to null).
- handle binding of values like the rest of SQLAlchemy
This is the code supplicant came up with:
class MultirowInsert(Executable, ClauseElement):
def __init__(self, table, values):
self.table = table
self.values = values
@compiles(MultirowInsert)
def visit_multirow_insert(element, compiler, **kw):
preparer = compiler.preparer
columns = None
values_clauses = []
for value in element.values:
if columns is None:
columns = value.keys() # each value must be a dict
local_values = []
for col in columns:
local_values.append(str(sqlescape(value[col])))
local_values_clause = (%s) % , .join(local_values)
values_clauses.append(local_values_clause)
values_clause = (VALUES %s) % , .join(values_clauses)
columns_clause =
columns = [preparer.quote(c, '') for c in columns]
columns_clause = ( + ,.join(columns) + )
return INSERT INTO %s %s %s % (
compiler.process(element.table, asfrom=True),
columns_clause,
values_clause,
)
ins = MultirowInsert(table, [{'c' : '1', 'c' : '2'}])
str(ins)
'INSERT INTO mytable (c) (VALUES (\'1\'), (\'2\'))'
But trying to pass None as one of the values produces something strange
(use something other than sqlescape to convert values?):
str(MultirowInsert(table, [{'c' : None}])
'INSERT INTO mytable (c) (VALUES (psycopg2.extensions.NoneAdapter object
at 0x25d0cd0))'
Ideally I'd like for it to work like the other SQLAlchemy constructs, i.e.
engine.execute(MultirowInsert(table).values(data).returning(table.primary_key.columns.values()))
Any pointers to examples/specifics in the docs will be appreciated.
Thanks.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/tuQwxz2lmAwJ.
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.
[sqlalchemy] [alembic] data migration and select expression
Hi,
I'm successfully using Alembic for my schema migration, but I fail
when it comes to data migration.
For example I would like to add new lines in an association table
which foreign key values would be set using a `select` expression.
Here is the kind of migration I want to be able to do:
```
# revision identifiers, used by Alembic.
revision = '17a6073d49ac'
down_revision = '22e1c34c8efd'
from alembic import op
import sqlalchemy as sa
metadata = sa.MetaData()
l = sa.Table('liste', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('type_liste', sa.Unicode(32)),
sa.Column('valeur', sa.Unicode(256)),
schema='app',
)
ll = sa.Table('liste_liste', metadata,
sa.Column('id_liste_parent', sa.Integer,
sa.ForeignKey('app.liste.id'), primary_key=True),
sa.Column('id_liste_child', sa.Integer,
sa.ForeignKey('app.liste.id'), primary_key=True),
schema='app')
def _listeid_select(liste, valeur):
return sa.select(
[l.c.id],
sa.and_(l.c.type_liste==liste, l.c.valeur==valeur)
).limit(1)
def upgrade():
op.bulk_insert(ll, [{
'id_liste_parent': _listeid_select('ListeNatureUE', 'poteau'),
'id_liste_child': _listeid_select('ListePlanUE', valeur)
} for valeur in [u'circulaire', u'ovale', u'rectangulaire'']])
def downgrade():
pass
```
But it does not work, I cannot use a `select` expression here.
How would you do such data migrations with alembic?
Ideally it would work with both online and offline mode.
Thanks,
Bruno
--
Bruno Binet
Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac, Cedex
Mail : bruno.bi...@camptocamp.com
http://www.camptocamp.com
--
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.
[sqlalchemy] Sqlalchemy issue on Windows Server 2008 R2 sp1
Hi everyone, as the title says I'm trying to run sqlalchemy (pyodbc) with
SQL Server 2008 on Windows Server 2008 R2 sp1 but I'm failing with the
database connection. Here is a small script I'm using to test the
connection:
from sqlalchemy import *
from sqlalchemy.engine import reflection
url = mssql+pyodbc://user:password@my.server.address/server_test
e = create_engine(url)
insp = reflection.Inspector.from_engine(e)
print insp.get_table_names()
If I the script I get the following error:
Traceback (most recent call last):
File connection.py, line 6, in module
File C:\Python27\lib\site-packages\sqlalchemy\engine\reflection.py,
line 118
, in from_engine
return Inspector(bind)
File C:\Python27\lib\site-packages\sqlalchemy\engine\reflection.py,
line 94,
in __init__
bind.connect().close()
File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line
2316, in
connect
return self._connection_cls(self, **kwargs)
File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 872,
in _
_init__
self.__connection = connection or engine.raw_connection()
File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line
2402, in
raw_connection
return self.pool.unique_connection()
File C:\Python27\lib\site-packages\sqlalchemy\pool.py, line 169, in
unique_c
onnection
return _ConnectionFairy(self).checkout()
File C:\Python27\lib\site-packages\sqlalchemy\pool.py, line 371, in
__init__
rec = self._connection_record = pool._do_get()
File C:\Python27\lib\site-packages\sqlalchemy\pool.py, line 697, in
_do_get
con = self._create_connection()
File C:\Python27\lib\site-packages\sqlalchemy\pool.py, line 174, in
_create_
connection
return _ConnectionRecord(self)
File C:\Python27\lib\site-packages\sqlalchemy\pool.py, line 256, in
__init__
self.connection = self.__connect()
File C:\Python27\lib\site-packages\sqlalchemy\pool.py, line 316, in
__connec
t
connection = self.__pool._creator()
File C:\Python27\lib\site-packages\sqlalchemy\engine\strategies.py,
line 80,
in connect
return dialect.connect(*cargs, **cparams)
File C:\Python27\lib\site-packages\sqlalchemy\engine\default.py, line
280, i
n connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (Error) ('08001', '[08001] [Microsoft][ODBC SQL
Serve
r Driver][DBNETLIB]Invalid connection. (14) (SQLDriverConnect); [01000]
[Microso
ft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Invalid Instance()).
(14)')
None None
The connection tested with SQL Server Management Studio works fine, so it
should not be an authentication problem.
Has anyone an idea of what I'm doing wrong?
Thanks in advance!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/g0c-MFla6pAJ.
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.
[sqlalchemy] Creating a feed related to different object type
Hello everybody,
It's about brainstorming on an elegant solution. I previously posted this
question on the Flask mailing list, and I got advised to post it on
SQLAlchemy list... which make more sense I admit. So here is my issue:
I have N different classes:
class Comment(Models):
author_id = Integer
comment = String
creation_date = Date
class Picture(Models):
author_id = Integer
image = File
creation_date = Date
...
now let say, I have a follow feature, allowing a user X to get updates
when Y (the user followed by X) does something (creepy...).
So far I came up with something like that:
class FeedItem(Model)
table = String
key = Integer
creation_date = Date
def url(self):
#get object by querying self.table with self.key
object = self.get_an_object(table=self.table, key=self.key)
return object.view_url
and then add this property to Comment and Picture classes:
@property
def view_url(self):
return url_for('view_function_name', self.id)
- What would be your way of dealing with this kind of open/generic
relationship items?
- How would you manage automatic deletion of a FeedItem when the object it
points to get destroyed? (I'm thinking attaching function on delete event
to classes)
- Would you create as many FeedItem per follower, or use a Table to link
them to followers, therefore deleting a FeedItem would automatically delete
the relation record from the Table.?
Thank you,
--
Brice
--
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.
Re: [sqlalchemy] Creating a feed related to different object type
hi,
i used to do something like this, i.e. adding information about urls,
views, etc. to sqlalchemy models, but found this to be inflexibel. Now
I keep URL-related information in the web app's routing component, and
to solve problems like the one you pose, I use zca adapters [1] (which
is easier when using pyramid, because you already have a component
registry available). So with this technology you'd register the same
FeedItem class as adapter for the various sqlalchemy models, which
means that at adaption time, the object to adapt will be passed to
you.
regards
robert
[1] http://www.muthukadan.net/docs/zca.html
On Tue, Nov 27, 2012 at 3:58 PM, Brice Leroy bbrriic...@gmail.com wrote:
Hello everybody,
It's about brainstorming on an elegant solution. I previously posted this
question on the Flask mailing list, and I got advised to post it on
SQLAlchemy list... which make more sense I admit. So here is my issue:
I have N different classes:
class Comment(Models):
author_id = Integer
comment = String
creation_date = Date
class Picture(Models):
author_id = Integer
image = File
creation_date = Date
...
now let say, I have a follow feature, allowing a user X to get updates
when Y (the user followed by X) does something (creepy...).
So far I came up with something like that:
class FeedItem(Model)
table = String
key = Integer
creation_date = Date
def url(self):
#get object by querying self.table with self.key
object = self.get_an_object(table=self.table, key=self.key)
return object.view_url
and then add this property to Comment and Picture classes:
@property
def view_url(self):
return url_for('view_function_name', self.id)
- What would be your way of dealing with this kind of open/generic
relationship items?
- How would you manage automatic deletion of a FeedItem when the object it
points to get destroyed? (I'm thinking attaching function on delete event to
classes)
- Would you create as many FeedItem per follower, or use a Table to link
them to followers, therefore deleting a FeedItem would automatically delete
the relation record from the Table.?
Thank you,
--
Brice
--
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.
--
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.
Re: [sqlalchemy] SQLAlchemy generates multiple connections
On Nov 27, 2012, at 5:53 AM, Daniel Rentsch wrote: Hi all, We are trying to implement a locking mechanism which relies on the incoming/used connections returned/used by SQLAlchemy. The problem we ran into is that SQLAlchemy uses a new database connection after calling expire_all() if we use the autocommit=True functionality. We expected that only one connection is used in one session. More precisely: IF you use *autocommit=True* IF you call call *expire_all* AND AFTER THIS generate a query with more than one affected tables via *filter_by* THEN SQLAlchemy will use multiple connections A Session using autocommit=True will pull a new connection from the connection pool for each individual execute operation, or each flush(). If you want only one connection used per session, you either need to stick with the recommended autocommit=False (the connection will be per-transaction, actually), or bind the Session to a single connection directly (sess = Session(bind=some_connection)). -- 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.
Re: [sqlalchemy] Oracle dblink reflection
I don't have access to a DBLINK environment, so here are some things:
1. tell me the output of this query:
SELECT owner, table_owner, table_name, db_link, synonym_name FROM all_synonyms
WHERE synonym_name='swells'
2. attempt this patch:
diff -r 79b0b8c5131b lib/sqlalchemy/dialects/oracle/base.py
--- a/lib/sqlalchemy/dialects/oracle/base.pySat Nov 24 16:14:58 2012 -0500
+++ b/lib/sqlalchemy/dialects/oracle/base.pyTue Nov 27 10:40:50 2012 -0500
@@ -841,6 +841,8 @@
actual_name = self.denormalize_name(table_name)
if not dblink:
dblink = ''
+elif dblink[0] != '@':
+dblink = @ + dblink
if not owner:
owner = self.denormalize_name(schema or self.default_schema_name)
return (actual_name, owner, dblink, synonym)
Let me know here, or on http://www.sqlalchemy.org/trac/ticket/2619 - thanks.
On Nov 27, 2012, at 5:35 AM, Alexey Ismailov wrote:
Hi, i have a very bad issue on SA using Oracle database link.
I have 2 Oracle (11g) database instances, one used for my app, in other
stored some data, which i need to use.
I've created a dblink:
CREATE DATABASE LINK link_name CONNECT TO SID IDENTIFIED BY password
USING 'localhost:1521/ORCL'
And synonym for table that i need:
CREATE SYNONYM swells FOR table_name@link_name
There is a restriction: i can only read data from this table, i can't affect
another application by doing something wrong.
So, i trying to reflect on this table:
from sqlalchemy import *
engine = create_engine('oracle://user:password@ip:1521/instance',
encoding = windows-1251, echo='debug')
meta = MetaData(bind=engine)
t = Table(swells, meta,
Column('col1', String, key='col1'),
Column('col2', String, key='col2'),
Column('col3', String, key='col3'),
Column('col_pk', Integer, Sequence(ucol_pk_seq), primary_key=True,
key='id'),
oracle_resolve_synonyms=True,
autoload=True,
autoload_with=engine
)
And i'm getting this error:
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00942: таблица или
представление пользователя не существует
'SELECT column_name, data_type, char_length, data_precision, data_scale,
nullable, data_default FROM ALL_TAB_COLUMNSSELENA_WELLS WHERE table_name =
:table_name AND owner = :owner ORDER BY column_id' {'owner': owner_name,
'table_name': table_name}
I'm little cofused... The first of all, the owner of this table is different
from my user (this obvious), and ALL_TAB_COLUMNSSELENA_WELLS should be
ALL_TAB_COLUMN@SSELENA_WELLS (cause this is the way to load linked objects in
Oracle).
I've searched in source of SA, found this in dialects/oracle/base.py (from
line 631 in get_columns):
@reflection.cache
def get_columns(self, connection, table_name, schema=None, **kw):
kw arguments can be:
oracle_resolve_synonyms
dblink
resolve_synonyms = kw.get('oracle_resolve_synonyms', False)
dblink = kw.get('dblink', '')
info_cache = kw.get('info_cache')
(table_name, schema, dblink, synonym) = \
self._prepare_reflection_args(connection, table_name, schema,
resolve_synonyms, dblink,
info_cache=info_cache)
columns = []
c = connection.execute(sql.text(
SELECT column_name, data_type, data_length, data_precision,
data_scale,
nullable, data_default FROM ALL_TAB_COLUMNS%(dblink)s
WHERE table_name = :table_name AND owner = :owner %
{'dblink': dblink}),
table_name=table_name, owner=schema)
The first, nobody used the reflection on dblink before? If this is a bug,
what should i do? I can change the _prepare_reflection_args function in
base.py, adding the @ to dblink and remove owner, but is this a right way?
So, i'm stuck... And sorry for my bad english...
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/qcYnSCxzsk0J.
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.
--
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.
Re: [sqlalchemy] Sqlalchemy issue on Windows Server 2008 R2 sp1
I'd first recommend creating a real ODBC datasource, since that's how ODBC is
meant to be used. Then you can try connecting to it using ODBC test tools
(such as on my Mac here, I have a program called iodbctest which tests an
ODBC datasource) and then perhaps creating a plain pyodbc connection to it.
On Nov 27, 2012, at 9:30 AM, Massi wrote:
Hi everyone, as the title says I'm trying to run sqlalchemy (pyodbc) with SQL
Server 2008 on Windows Server 2008 R2 sp1 but I'm failing with the database
connection. Here is a small script I'm using to test the connection:
from sqlalchemy import *
from sqlalchemy.engine import reflection
url = mssql+pyodbc://user:password@my.server.address/server_test
e = create_engine(url)
insp = reflection.Inspector.from_engine(e)
print insp.get_table_names()
If I the script I get the following error:
Traceback (most recent call last):
File connection.py, line 6, in module
File C:\Python27\lib\site-packages\sqlalchemy\engine\reflection.py, line
118
, in from_engine
return Inspector(bind)
File C:\Python27\lib\site-packages\sqlalchemy\engine\reflection.py, line
94,
in __init__
bind.connect().close()
File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 2316,
in
connect
return self._connection_cls(self, **kwargs)
File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 872,
in _
_init__
self.__connection = connection or engine.raw_connection()
File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 2402,
in
raw_connection
return self.pool.unique_connection()
File C:\Python27\lib\site-packages\sqlalchemy\pool.py, line 169, in
unique_c
onnection
return _ConnectionFairy(self).checkout()
File C:\Python27\lib\site-packages\sqlalchemy\pool.py, line 371, in
__init__
rec = self._connection_record = pool._do_get()
File C:\Python27\lib\site-packages\sqlalchemy\pool.py, line 697, in
_do_get
con = self._create_connection()
File C:\Python27\lib\site-packages\sqlalchemy\pool.py, line 174, in
_create_
connection
return _ConnectionRecord(self)
File C:\Python27\lib\site-packages\sqlalchemy\pool.py, line 256, in
__init__
self.connection = self.__connect()
File C:\Python27\lib\site-packages\sqlalchemy\pool.py, line 316, in
__connec
t
connection = self.__pool._creator()
File C:\Python27\lib\site-packages\sqlalchemy\engine\strategies.py, line
80,
in connect
return dialect.connect(*cargs, **cparams)
File C:\Python27\lib\site-packages\sqlalchemy\engine\default.py, line
280, i
n connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (Error) ('08001', '[08001] [Microsoft][ODBC SQL
Serve
r Driver][DBNETLIB]Invalid connection. (14) (SQLDriverConnect); [01000]
[Microso
ft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Invalid Instance()).
(14)')
None None
The connection tested with SQL Server Management Studio works fine, so it
should not be an authentication problem.
Has anyone an idea of what I'm doing wrong?
Thanks in advance!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/g0c-MFla6pAJ.
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.
--
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.
Re: [sqlalchemy] [alembic] data migration and select expression
On Nov 27, 2012, at 8:23 AM, Bruno Binet wrote:
Hi,
I'm successfully using Alembic for my schema migration, but I fail
when it comes to data migration.
For example I would like to add new lines in an association table
which foreign key values would be set using a `select` expression.
Here is the kind of migration I want to be able to do:
```
# revision identifiers, used by Alembic.
revision = '17a6073d49ac'
down_revision = '22e1c34c8efd'
from alembic import op
import sqlalchemy as sa
metadata = sa.MetaData()
l = sa.Table('liste', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('type_liste', sa.Unicode(32)),
sa.Column('valeur', sa.Unicode(256)),
schema='app',
)
ll = sa.Table('liste_liste', metadata,
sa.Column('id_liste_parent', sa.Integer,
sa.ForeignKey('app.liste.id'), primary_key=True),
sa.Column('id_liste_child', sa.Integer,
sa.ForeignKey('app.liste.id'), primary_key=True),
schema='app')
def _listeid_select(liste, valeur):
return sa.select(
[l.c.id],
sa.and_(l.c.type_liste==liste, l.c.valeur==valeur)
).limit(1)
def upgrade():
op.bulk_insert(ll, [{
'id_liste_parent': _listeid_select('ListeNatureUE', 'poteau'),
'id_liste_child': _listeid_select('ListePlanUE', valeur)
} for valeur in [u'circulaire', u'ovale', u'rectangulaire'']])
def downgrade():
pass
```
But it does not work, I cannot use a `select` expression here.
How would you do such data migrations with alembic?
Ideally it would work with both online and offline mode.
We don't have direct support for INSERT..SELECT yet, you can use this recipe :
http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#compiling-sub-elements-of-a-custom-expression-construct
--
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.
Re: [sqlalchemy] Custom SQL construct for postgres multirow insert
On Nov 27, 2012, at 8:10 AM, Idan Kamara wrote:
Hello,
I'd like to create a construct for postgres's multirow insert. I asked this
on IRC
and got some great help from supplicant, however I have a few things missing:
- proper conversion of Python types to SQL (None to null).
- handle binding of values like the rest of SQLAlchemy
This is the code supplicant came up with:
class MultirowInsert(Executable, ClauseElement):
def __init__(self, table, values):
self.table = table
self.values = values
@compiles(MultirowInsert)
def visit_multirow_insert(element, compiler, **kw):
preparer = compiler.preparer
columns = None
values_clauses = []
for value in element.values:
if columns is None:
columns = value.keys() # each value must be a dict
local_values = []
for col in columns:
local_values.append(str(sqlescape(value[col])))
local_values_clause = (%s) % , .join(local_values)
values_clauses.append(local_values_clause)
values_clause = (VALUES %s) % , .join(values_clauses)
columns_clause =
columns = [preparer.quote(c, '') for c in columns]
columns_clause = ( + ,.join(columns) + )
return INSERT INTO %s %s %s % (
compiler.process(element.table, asfrom=True),
columns_clause,
values_clause,
)
ins = MultirowInsert(table, [{'c' : '1', 'c' : '2'}])
str(ins)
'INSERT INTO mytable (c) (VALUES (\'1\'), (\'2\'))'
But trying to pass None as one of the values produces something strange (use
something other than sqlescape to convert values?):
str(MultirowInsert(table, [{'c' : None}])
'INSERT INTO mytable (c) (VALUES (psycopg2.extensions.NoneAdapter object
at 0x25d0cd0))'
uh ok he is wading into odd territory there, you don't have any imports above
but I'm assuming sqlescape is postgresql's escaping function which is why
you're getting it's adapter objects stuck into your query.
an insert with many values() I'd not be bypassing psycopg2's usual bound
parameter mechanisms and I'd be using a naming scheme, such as:
INSERT INTO table (a, b, c) VALUES (%(a1)s, %(b1)s, %(c1)s), (%(a2)s, %(b2)s,
%(c2)s), ...
you'd then need to perform the necessary naming on the values passed to the
construct, which also, I'm assuming that code example you have is not real
because {'c':'1', 'c':'2'} clearly blows away one of the values:
myinsert(table).values({'a':1, 'b':2, 'c':3}, {'a':4, 'b':5, 'c':6}, ...)
one reason we don't yet have these fancier INSERT constructs yet is because the
mechanics of INSERT are very hard. I'd recommend walking through the source in
sqlalchemy/sql/compiler.py visit_insert() and _get_colparams() (which is the
real control center for INSERT and UPDATE and is a little bit of a monster).
I will accept well-considered patches to compiler.py that allow for insert() to
support multiple values() directly.
--
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.
Re: [sqlalchemy] Custom SQL construct for postgres multirow insert
On Tue, Nov 27, 2012 at 11:00:25AM -0500, Michael Bayer wrote:
On Nov 27, 2012, at 8:10 AM, Idan Kamara wrote:
Hello,
I'd like to create a construct for postgres's multirow insert. I asked this
on IRC
and got some great help from supplicant, however I have a few things
missing:
- proper conversion of Python types to SQL (None to null).
- handle binding of values like the rest of SQLAlchemy
This is the code supplicant came up with:
class MultirowInsert(Executable, ClauseElement):
def __init__(self, table, values):
self.table = table
self.values = values
@compiles(MultirowInsert)
def visit_multirow_insert(element, compiler, **kw):
preparer = compiler.preparer
columns = None
values_clauses = []
for value in element.values:
if columns is None:
columns = value.keys() # each value must be a dict
local_values = []
for col in columns:
local_values.append(str(sqlescape(value[col])))
local_values_clause = (%s) % , .join(local_values)
values_clauses.append(local_values_clause)
values_clause = (VALUES %s) % , .join(values_clauses)
columns_clause =
columns = [preparer.quote(c, '') for c in columns]
columns_clause = ( + ,.join(columns) + )
return INSERT INTO %s %s %s % (
compiler.process(element.table, asfrom=True),
columns_clause,
values_clause,
)
ins = MultirowInsert(table, [{'c' : '1', 'c' : '2'}])
str(ins)
'INSERT INTO mytable (c) (VALUES (\'1\'), (\'2\'))'
But trying to pass None as one of the values produces something strange
(use something other than sqlescape to convert values?):
str(MultirowInsert(table, [{'c' : None}])
'INSERT INTO mytable (c) (VALUES (psycopg2.extensions.NoneAdapter object
at 0x25d0cd0))'
uh ok he is wading into odd territory there, you don't have any imports above
but I'm assuming sqlescape is postgresql's escaping function which is why
you're getting it's adapter objects stuck into your query.
Well like I said I wouldn't really do it this way. This is the complete
code I provided: http://fpaste.org/AOCr/
Which is basically just a hack around me mushing this together:
http://fpaste.org/RoBJ/
But the correct way to get the quoted value is with ``.getquoted()``,
like this:
sqlescape(value[col]).getquoted()
an insert with many values() I'd not be bypassing psycopg2's usual bound
parameter mechanisms and I'd be using a naming scheme, such as:
INSERT INTO table (a, b, c) VALUES (%(a1)s, %(b1)s, %(c1)s), (%(a2)s, %(b2)s,
%(c2)s), ...
psycopg2 is just quoting them internally anyway, but this is probably
more performant.
you'd then need to perform the necessary naming on the values passed to the
construct, which also, I'm assuming that code example you have is not real
because {'c':'1', 'c':'2'} clearly blows away one of the values:
myinsert(table).values({'a':1, 'b':2, 'c':3}, {'a':4, 'b':5, 'c':6}, ...)
one reason we don't yet have these fancier INSERT constructs yet is because
the mechanics of INSERT are very hard. I'd recommend walking through the
source in sqlalchemy/sql/compiler.py visit_insert() and _get_colparams()
(which is the real control center for INSERT and UPDATE and is a little bit
of a monster). I will accept well-considered patches to compiler.py that
allow for insert() to support multiple values() directly.
--
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.
--
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.
Re: [sqlalchemy] Custom SQL construct for postgres multirow insert
On Nov 27, 2012, at 11:18 AM, Ryan Kelly wrote: an insert with many values() I'd not be bypassing psycopg2's usual bound parameter mechanisms and I'd be using a naming scheme, such as: INSERT INTO table (a, b, c) VALUES (%(a1)s, %(b1)s, %(c1)s), (%(a2)s, %(b2)s, %(c2)s), ... psycopg2 is just quoting them internally anyway, but this is probably more performant. more importantly, it's DBAPI and database agnostic. -- 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.
Re: [sqlalchemy] Creating a feed related to different object type
Thank you for the advice Robert. As I'm using flask I'm not sure how
adaptable the ZCA would be. I'll try to understand the logic behind it and
see if I can replicate it within my models.
On Tue, Nov 27, 2012 at 7:12 AM, Robert Forkel xrotw...@googlemail.comwrote:
hi,
i used to do something like this, i.e. adding information about urls,
views, etc. to sqlalchemy models, but found this to be inflexibel. Now
I keep URL-related information in the web app's routing component, and
to solve problems like the one you pose, I use zca adapters [1] (which
is easier when using pyramid, because you already have a component
registry available). So with this technology you'd register the same
FeedItem class as adapter for the various sqlalchemy models, which
means that at adaption time, the object to adapt will be passed to
you.
regards
robert
[1] http://www.muthukadan.net/docs/zca.html
On Tue, Nov 27, 2012 at 3:58 PM, Brice Leroy bbrriic...@gmail.com wrote:
Hello everybody,
It's about brainstorming on an elegant solution. I previously posted this
question on the Flask mailing list, and I got advised to post it on
SQLAlchemy list... which make more sense I admit. So here is my issue:
I have N different classes:
class Comment(Models):
author_id = Integer
comment = String
creation_date = Date
class Picture(Models):
author_id = Integer
image = File
creation_date = Date
...
now let say, I have a follow feature, allowing a user X to get updates
when Y (the user followed by X) does something (creepy...).
So far I came up with something like that:
class FeedItem(Model)
table = String
key = Integer
creation_date = Date
def url(self):
#get object by querying self.table with self.key
object = self.get_an_object(table=self.table, key=self.key)
return object.view_url
and then add this property to Comment and Picture classes:
@property
def view_url(self):
return url_for('view_function_name', self.id)
- What would be your way of dealing with this kind of open/generic
relationship items?
- How would you manage automatic deletion of a FeedItem when the object
it
points to get destroyed? (I'm thinking attaching function on delete
event to
classes)
- Would you create as many FeedItem per follower, or use a Table to link
them to followers, therefore deleting a FeedItem would automatically
delete
the relation record from the Table.?
Thank you,
--
Brice
--
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.
--
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.
--
--
Brice
--
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.
