[sqlalchemy] Failing to connect to MongoDB BIconnector - a restricted MySQL implementation

2017-05-12 Thread Jonathan Underwood 
Hi,

I am trying to do use SQLAlchemy with the MongoDB BI connector, which 
presents itself as a MySQL server. However, the supported operations 
correspond only to the SQL-99 SELECT operations (i.e. it's a read only 
server) . See:

https://docs.mongodb.com/bi-connector/master/supported-operations/

Issuing the following set of commands results in the subsequent backtrace 
shown below. Is there any way to specify that SQLALchemy should not attempt 
to write to the connected database? Is that actually the cause of the 
backtrace?

Many thanks,
Jonathan


from sqlalchemy import create_engine
engine = create_engine('mysql://USERNAME:passw...@bicon.domain.com:3307')
engine.table_names()


/opt/superset27/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py:74: 
SAWarning: An exception has occurred during handling of a previous 
exception.  The previous exception is:
  (1046, 'No database selected')

  "is:\n %s %s\n" % (self._exc_info[0], self._exc_info[1]))
No handlers could be found for logger "sqlalchemy.pool.QueuePool"
---
OperationalError  Traceback (most recent call last)
 in ()
> 1 engine.table_names()

/opt/superset27/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in 
table_names(self, schema, connection)
   2126 if not schema:
   2127 schema = self.dialect.default_schema_name
-> 2128 return self.dialect.get_table_names(conn, schema)
   2129
   2130 def has_table(self, table_name, schema=None):

 in get_table_names(self, connection, schema, **kw)

/opt/superset27/lib/python2.7/site-packages/sqlalchemy/engine/reflection.pyc 
in cache(fn, self, con, *args, **kw)
 40 info_cache = kw.get('info_cache', None)
 41 if info_cache is None:
---> 42 return fn(self, con, *args, **kw)
 43 key = (
 44 fn.__name__,

/opt/superset27/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.pyc 
in get_table_names(self, connection, schema, **kw)
   1714 rp = connection.execute(
   1715 "SHOW FULL TABLES FROM %s" %
-> 1716 
self.identifier_preparer.quote_identifier(current_schema))
   1717
   1718 return [row[0]

/opt/superset27/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in 
execute(self, object, *multiparams, **params)
937 """
938 if isinstance(object, util.string_types[0]):
--> 939 return self._execute_text(object, multiparams, params)
940 try:
941 meth = object._execute_on_connection

/opt/superset27/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in 
_execute_text(self, statement, multiparams, params)
   1095 statement,
   1096 parameters,
-> 1097 statement, parameters
   1098 )
   1099 if self._has_events or self.engine._has_events:

/opt/superset27/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in 
_execute_context(self, dialect, constructor, statement, parameters, *args)
   1187 parameters,
   1188 cursor,
-> 1189 context)
   1190
   1191 if self._has_events or self.engine._has_events:

/opt/superset27/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in 
_handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1393 self._safe_close_cursor(cursor)
   1394 with util.safe_reraise(warn_only=True):
-> 1395 self._autorollback()
   1396
   1397 if newraise:

/opt/superset27/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.pyc 
in __exit__(self, type_, value, traceback)
 74 "is:\n %s %s\n" % (self._exc_info[0], 
self._exc_info[1]))
 75 self._exc_info = None   # remove potential circular 
references
---> 76 compat.reraise(type_, value, traceback)
 77
 78

/opt/superset27/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in 
_handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1393 self._safe_close_cursor(cursor)
   1394 with util.safe_reraise(warn_only=True):
-> 1395 self._autorollback()
   1396
   1397 if newraise:

/opt/superset27/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in 
_autorollback(self)
822 def _autorollback(self):
823 if not self._root.in_transaction():
--> 824 self._root._rollback_impl()
825
826 def close(self):

/opt/superset27/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in 
_rollback_impl(self)
701 self.engine.dialect.do_rollback(self.connection)
702 except BaseException as e:
--> 703 self._handle_dbapi_exception(e, None, None, None, 
None)
704 finally:
705

Re: [sqlalchemy] Composite column with null property

2017-05-12 Thread Simon King 
On Thu, May 11, 2017 at 6:18 PM,   wrote:
> Hello,
>
> I have a Money composite column, comprised of an `amount` (Decimal) and a
> `currency` (String). Sometimes the amount needs to be NULL, but then I get
> an instance of Money(None, 'GBP'). Is there any way to force the composite
> to return None in this case?

I'm not sure if that's possible. As an alternative, you could do
something like this:

class YourObject(Base):
_money = composite(Money, amount, currency)
@property
def money(self):
if self._money.amount is not None:
return self._money
return None

Hope that helps,

Simon

-- 
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] BUG in union with limit in PostgreSQL

2017-05-12 Thread Jarek 
Hello!

It looks that SQLAlchemy doesn't properly handle union with limits in
the following scenario:

res1 = Session.query( Messages ).order_by( Messages.ts ).limit(100)
res2 = Session.query( Messages1 ).order_by( Messages1.ts ).limit(100)
res3 = res1.union_all( res2 )

SQLAlchemy creates the following final query:

SELECT  FROM Messages order by ts limit 100 
  UNION ALL SELECT  FROM Messages1 order by ts limit 100

Which fails with:

ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near
"UNION"

To fix this, both queries should be enclosed in parenthesis:

(SELECT  FROM Messages order by ts limit 100 )
  UNION ALL ( SELECT  FROM Messages1 order by ts limit 100 )

Best regards
Jarek




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

Re: [sqlalchemy] BUG in union with limit in PostgreSQL

2017-05-12 Thread mike bayer 



On 05/12/2017 04:55 AM, Jarek wrote:

Hello!

It looks that SQLAlchemy doesn't properly handle union with limits in
the following scenario:

res1 = Session.query( Messages ).order_by( Messages.ts ).limit(100)
res2 = Session.query( Messages1 ).order_by( Messages1.ts ).limit(100)
res3 = res1.union_all( res2 )

SQLAlchemy creates the following final query:

SELECT  FROM Messages order by ts limit 100
   UNION ALL SELECT  FROM Messages1 order by ts limit 100



that's not at all what I get and that's not how Query.union_all() does 
it, it wraps the inner things inside a subquery, works fine.  MCVE:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)


s = Session(e)

A1 = aliased(A)
res1 = s.query(A).order_by(A.id).limit(100)
res2 = s.query(A1).order_by(A1.id).limit(100)
res3 = res1.union_all(res2)

res3.all()


query:

SELECT anon_1.a_id AS anon_1_a_id
FROM ((SELECT a.id AS a_id
FROM a ORDER BY a.id
 LIMIT %(param_1)s) UNION ALL (SELECT a_1.id AS a_1_id
FROM a AS a_1 ORDER BY a_1.id
 LIMIT %(param_2)s)) AS anon_1



please provide accurate details, thanks





Which fails with:

ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near
"UNION"

To fix this, both queries should be enclosed in parenthesis:

(SELECT  FROM Messages order by ts limit 100 )
   UNION ALL ( SELECT  FROM Messages1 order by ts limit 100 )

Best regards
Jarek






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

Re: [sqlalchemy] Composite column with null property

2017-05-12 Thread mike bayer 
you would need to use the "on load" events to detect this and replace 
the value with None using set_committed_value()


from sqlalchemy.orm import set_committed_value
from sqlalchemy import event

@event.listens_for(MyClass, "load")
@event.listens_for(MyClass, "refresh")
def _replace_currency(obj, context, *arg):
if obj._money.amount is None:
set_committed_value(obj, '_money', None)


however, note that above, we are changing the value of "currency" from 
what it actually is in the database.  When this object is returned, the 
composite here might conflict with what the "currency" attribute says.


You might be better off changing the semantics of your app either that 
a. your Money type represents "None" meaningfully, like Money.is_empty, 
something like that


b. Simon's idea below is likely a better idea, that is, don't persist 
"None, GBP" like that, just set it up as None on the persist side




On 05/12/2017 04:42 AM, Simon King wrote:

On Thu, May 11, 2017 at 6:18 PM,   wrote:

Hello,

I have a Money composite column, comprised of an `amount` (Decimal) and a
`currency` (String). Sometimes the amount needs to be NULL, but then I get
an instance of Money(None, 'GBP'). Is there any way to force the composite
to return None in this case?


I'm not sure if that's possible. As an alternative, you could do
something like this:

class YourObject(Base):
 _money = composite(Money, amount, currency)
 @property
 def money(self):
 if self._money.amount is not None:
 return self._money
 return None

Hope that helps,

Simon



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

Re: [sqlalchemy] BUG in union with limit in PostgreSQL

2017-05-12 Thread Jarek 
Hello!

Sorry, I was using old sqlalchemy, after upgrade it works fine.

best regards
Jarek


Dnia 2017-05-12, pią o godzinie 08:54 -0400, mike bayer pisze:
> 
> On 05/12/2017 04:55 AM, Jarek wrote:
> > Hello!
> > 
> > It looks that SQLAlchemy doesn't properly handle union with limits in
> > the following scenario:
> > 
> > res1 = Session.query( Messages ).order_by( Messages.ts ).limit(100)
> > res2 = Session.query( Messages1 ).order_by( Messages1.ts ).limit(100)
> > res3 = res1.union_all( res2 )
> > 
> > SQLAlchemy creates the following final query:
> > 
> > SELECT  FROM Messages order by ts limit 100
> >UNION ALL SELECT  FROM Messages1 order by ts limit 100
> 
> 
> that's not at all what I get and that's not how Query.union_all() does 
> it, it wraps the inner things inside a subquery, works fine.  MCVE:
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
> 
> Base = declarative_base()
> 
> 
> class A(Base):
>  __tablename__ = 'a'
>  id = Column(Integer, primary_key=True)
> 
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
> Base.metadata.create_all(e)
> 
> 
> s = Session(e)
> 
> A1 = aliased(A)
> res1 = s.query(A).order_by(A.id).limit(100)
> res2 = s.query(A1).order_by(A1.id).limit(100)
> res3 = res1.union_all(res2)
> 
> res3.all()
> 
> 
> query:
> 
> SELECT anon_1.a_id AS anon_1_a_id
> FROM ((SELECT a.id AS a_id
> FROM a ORDER BY a.id
>   LIMIT %(param_1)s) UNION ALL (SELECT a_1.id AS a_1_id
> FROM a AS a_1 ORDER BY a_1.id
>   LIMIT %(param_2)s)) AS anon_1
> 
> 
> 
> please provide accurate details, thanks
> 
> 
> 
> > 
> > Which fails with:
> > 
> > ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near
> > "UNION"
> > 
> > To fix this, both queries should be enclosed in parenthesis:
> > 
> > (SELECT  FROM Messages order by ts limit 100 )
> >UNION ALL ( SELECT  FROM Messages1 order by ts limit 100 )
> > 
> > Best regards
> > Jarek
> > 
> > 
> > 
> > 
> 


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