date:20170620

[sqlalchemy] Re: Can't connect to SQLite databases

2017-06-20 Thread python howto



On Saturday, October 22, 2011 at 7:03:59 AM UTC-4, João Silva wrote:
>
> Hello everyone,
>
> I'm having a problem connecting to SQLite database files with
> SQLAlchemy. :memory: databases work just fine, but whenever I'm trying
> to access a file, the following happens:
>
>
>   Python 2.7.2 (default, Jun 12 2011, 14:24:46) [MSC v.1500 64 bit
> (AMD64)] on win
>   32
>   Type "help", "copyright", "credits" or "license" for more information.
>   >>> from sqlalchemy import create_engine
>   >>> import os.path
>   >>> os.path.exists('test.db')
>   False
>   >>> engine = create_engine('sqlite:///test.db')
>   >>> engine.connect
>   
>   >>> engine.connect()
>   Traceback (most recent call last):
> File "", line 1, in 
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
>   ne\base.py", line 2310, in connect
>   return self._connection_cls(self, **kwargs)
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
>   ne\base.py", line 872, in __init__
>   self.__connection = connection or engine.raw_connection()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
>   ne\base.py", line 2396, in raw_connection
>   return self.pool.unique_connection()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
>   .py", line 169, in unique_connection
>   return _ConnectionFairy(self).checkout()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
>   .py", line 370, in __init__
>   rec = self._connection_record = pool._do_get()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
>   .py", line 757, in _do_get
>   return self._create_connection()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
>   .py", line 174, in _create_connection
>   return _ConnectionRecord(self)
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
>   .py", line 255, in __init__
>   self.connection = self.__connect()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
>   .py", line 315, in __connect
>   connection = self.__pool._creator()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
>   ne\strategies.py", line 80, in connect
>   return dialect.connect(*cargs, **cparams)
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
>   ne\default.py", line 275, in connect
>   return self.dbapi.connect(*cargs, **cparams)
>   sqlalchemy.exc.OperationalError: (OperationalError) unable to open
> database file
>None None
>
>
> The same happens after having created the database using the sqlite3
> module and then trying to connect to it via SQLAlchemy.
>
>   >>> import sqlite3
>   >>> conn = sqlite3.connect('test.db')
>   >>> conn.execute('create table bla(id int primary key);')
>   
>   >>> conn.close()
>   >>> os.path.exists('test.db')
>   True
>   >>> engine = create_engine('sqlite:///test.db')
>   >>> engine.connect()
>   Traceback (most recent call last):
> File "", line 1, in 
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
>   ne\base.py", line 2310, in connect
>   return self._connection_cls(self, **kwargs)
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
>   ne\base.py", line 872, in __init__
>   self.__connection = connection or engine.raw_connection()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
>   ne\base.py", line 2396, in raw_connection
>   return self.pool.unique_connection()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
>   .py", line 169, in unique_connection
>   return _ConnectionFairy(self).checkout()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
>   .py", line 370, in __init__
>   rec = self._connection_record = pool._do_get()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
>   .py", line 757, in _do_get
>   return self._create_connection()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
>   .py", line 174, in _create_connection
>   return _ConnectionRecord(self)
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
>   .py", line 255, in __init__
>   self.connection = self.__connect()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
>   .py", line 315, in __connect
>   connection = self.__pool._creator()
> File 
> "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
>   ne\strategies.py", line 80,

[sqlalchemy] increasing memory on simple query

2017-06-20 Thread Luca Zulian

Hi,
I am currently playing around with SQLAlchemy a bit, and I found a strange 
behaviour about memory usage.
I'm using sqlalchemy with version 1.1.10 running on python 2.7.13

# -*- coding: utf-8 -*-


from __future__ import absolute_import, unicode_literals


from contextlib import contextmanager


import memory_profiler
from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


Base = declarative_base()


e = create_engine('mysql://root:root@127.0.0.1:3306/MyDb?charset=utf8')




class Currency(Base):
__tablename__ = "currency"
id = Column(Integer, primary_key=True)


Base.metadata.create_all(e)
Session = sessionmaker(autoflush=True, bind=e)




@contextmanager
def session_scope():
"""Provide a transactional scope around a series of operations."""
session = Session()
try:
yield session
session.commit()
except:
session.rollback()
raise
finally:
session.close()




@memory_profiler.profile(precision=6)
def foo():
with session_scope() as session2:
result = session2.query(Currency).filter_by(id=1).first()
print(result.id)




while True:
foo()

It seems that the memory it's never freed, and that continuously increase. 
The output of memory_profiler is as follow:
Line #Mem usageIncrement   Line Contents





40  53.875000 MiB   0.00 MiB   @memory_profiler.profile(precision=6)

41 def foo():

42  53.875000 MiB   0.00 MiB   with session_scope() as session2:

43  53.886719 MiB   0.011719 MiB   result = session2.query(
Currency).filter_by(id=1).first()

44  53.886719 MiB   0.00 MiB   print(result.id)




Line #Mem usageIncrement   Line Contents



40  53.953125 MiB   0.00 MiB   @memory_profiler.profile(precision=6)

41 def foo():

42  53.953125 MiB   0.00 MiB   with session_scope() as session2:

43  53.957031 MiB   0.003906 MiB   result = session2.query(
Currency).filter_by(id=1).first()



44  53.957031 MiB   0.00 MiB   print(result.id)


Is there a way to solve this problem? I've tried with session.expunge_all() 
but it seem not working as expected. Or is there a problem with my code?

Thanks all

-- 
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] Re: ImportError: cannot import name postgresql

2017-06-20 Thread mike bayer

it seems likely that your apache server is using a different Python path 
/ virtualenv and you need to have SQLAlchemy installed in that environment.


On 06/20/2017 12:53 AM, shrey.chau...@invicto.in wrote:


this is the stack trace :
[Mon Jun 19 18:11:01.431528 2017] [:error] [pid 18592] [remote 
10.11.12.15:128] Traceback (most recent call last):
[Mon Jun 19 18:11:01.431548 2017] [:error] [pid 18592] [remote 
10.11.12.15:128]   File 
"/var/lib/enclouden-orchestrator/enclouden-orchestrator.wsgi", line 10, 
in 
[Mon Jun 19 18:11:01.431616 2017] [:error] [pid 18592] [remote 
10.11.12.15:128] imports =  
__import__("app",fromlist=['create_and_initialize_app'])
[Mon Jun 19 18:11:01.431627 2017] [:error] [pid 18592] [remote 
10.11.12.15:128]   File "/var/lib/enclouden-orchestrator/app.py", line 
6, in 
[Mon Jun 19 18:11:01.431677 2017] [:error] [pid 18592] [remote 
10.11.12.15:128] from database import db, track_session_deletes
[Mon Jun 19 18:11:01.431691 2017] [:error] [pid 18592] [remote 
10.11.12.15:128]   File 
"/var/lib/enclouden-orchestrator/database/__init__.py", line 10, in 
[Mon Jun 19 18:11:01.431728 2017] [:error] [pid 18592] [remote 
10.11.12.15:128] from . import events, instances, pools, users, roles
[Mon Jun 19 18:11:01.431748 2017] [:error] [pid 18592] [remote 
10.11.12.15:128]   File 
"/var/lib/enclouden-orchestrator/database/events.py", line 5, in 
[Mon Jun 19 18:11:01.431802 2017] [:error] [pid 18592] [remote 
10.11.12.15:128] from .instances import Instance, OpenstackProject
[Mon Jun 19 18:11:01.431816 2017] [:error] [pid 18592] [remote 
10.11.12.15:128]   File 
"/var/lib/enclouden-orchestrator/database/instances.py", line 2, in 
[Mon Jun 19 18:11:01.431893 2017] [:error] [pid 18592] [remote 
10.11.12.15:128] from .types import GUID
[Mon Jun 19 18:11:01.431903 2017] [:error] [pid 18592] [remote 
10.11.12.15:128]   File 
"/var/lib/enclouden-orchestrator/database/types.py", line 5, in 
[Mon Jun 19 18:11:01.431950 2017] [:error] [pid 18592] [remote 
10.11.12.15:128] from sqlalchemy.dialects import postgresql
[Mon Jun 19 18:11:01.431968 2017] [:error] [pid 18592] [remote 
10.11.12.15:128] ImportError: cannot import name postgresql




and yes this is mod_wsgi and daemon mode is present
this is the wsgi file

WSGIDaemonProcess ecnorchestrator user=apache processes=10 threads=1
WSGIScriptAlias /enclouden/orchestrator 
/var/lib/enclouden-orchestrator/enclouden-orchestrator.wsgi

WSGIScriptReloading On
WSGIPassAuthorization On


 WSGIProcessGroup ecnorchestrator
 WSGIApplicationGroup %{GLOBAL}
 Order deny,allow
 Require all granted
 AddOutputFilterByType DEFLATE application/json





--
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 - 
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] Is this a valid inheritance configuration ?

2017-06-20 Thread yoch . melka

Thank you Mike !

Le mardi 20 juin 2017 21:23:01 UTC+3, Mike Bayer a écrit :
>
>
>
> On 06/20/2017 02:08 PM, yoch@gmail.com  wrote: 
> > Hi, 
> > 
> > I wish to have a two-levels inheritance, but I don't know how to 
> proceed. 
> > The docs says that "only one discriminator column or SQL expression may 
> > be configured for the entire inheritance hierarchy". 
> > 
> > I tried with this example (mixing joined and single inheritances) : 
> > 
> > | 
> > classPerson(Base): 
> >  __tablename__ ='person' 
> > 
> >  id =Column(Integer,primary_key=True) 
> >  type =Column(String(50),nullable=False) 
> >  name =Column(String(50)) 
> > 
> >  __mapper_args__ ={ 
> > 'polymorphic_identity':'person', 
> > 'polymorphic_on':type 
> > } 
> > 
> > classManager(Person): 
> >  __tablename__ ='manager' 
> > 
> >  id =Column(ForeignKey('person.id'),primary_key=True) 
> >  manager_name =Column(String(30)) 
> > 
> >  __mapper_args__ ={ 
> > 'polymorphic_identity':'manager', 
> > } 
> > 
> > classEngineerBase(Person): 
> >  __tablename__ ='engineer' 
> > 
> >  id =Column(ForeignKey('person.id'),primary_key=True) 
> >  engineer_name =Column(String(30)) 
> > 
> > classEngineerType1(EngineerBase): 
> > 
> >  __mapper_args__ ={ 
> > 'polymorphic_identity':'engineer_t1', 
> > } 
> > 
> > classEngineerType2(EngineerBase): 
> > 
> >  __mapper_args__ ={ 
> > 'polymorphic_identity':'engineer_t2', 
> > } 
> > | 
> > 
> > It seems to work, but it this correct ? 
>
> it's correct, you only have one "polymorphic_on" set up, and that's what 
> that means. 
>
>
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > You received this message because you are subscribed to the Google 
> > Groups "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> > an email to sqlalchemy+...@googlegroups.com  
> > . 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Only return single entity from query

2017-06-20 Thread Ryan Weinstein



On Monday, June 19, 2017 at 6:40:04 PM UTC-7, Jonathan Vanasco wrote:
>
> You're not joining anything onto the `rProductCategoryHistory` table, so 
> depending on your DB you may be getting populated rows for rProduct that 
> don't match anything.
>
> you probably want something like this...
>
> query = db.session.query(rProduct)\
> .join(rProductHistoricalDetails, 
> rProduct.most_recent_historical_details_id==rProductHistoricalDetails.id)\
> .join(rProductReviewsHistoricalDetails, 
> rProduct.most_recent_historical_reviews_entry==rProductReviewsHistoricalDetails.id)\
> .order_by(rProductHistoricalDetails.time_updated.desc())
>
> unless you missed joining rProductCategoryHistory.
>
> you should try writing a raw sql query that gets the right data.
>


This gets the right data:

SELECT * FROM r_product_category_history_list JOIN r_product_list on 
r_product_list.r_id=r_product_category_history_list.r_id JOIN 
r_product_reviews_historical_details_list ON 
r_product_list.most_recent_historical_reviews_id=r_product_reviews_historical_details_list.id
 
\G


But the problem is I want that data in my rProduct model 

-- 
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] Re: Only return single entity from query

2017-06-20 Thread Ryan Weinstein

Thanks for the reply Mike. SQLAlchemy is a fantastic ORM and you've done an 
amazing job with it.

You're suggestion hasn't fixed my issue though, perhaps there's something 
fundamental I'm misunderstanding?

Here is the query I'm testing:

r_product_category_history_list = 
db.Model.metadata.tables['r_product_category_history_list'] query = 
db.session.query(rProduct, r_product_category_history_list.c.time_updated)\ 
.join(rProductCategoryHistory)\ 
.order_by(desc(rProductCategoryHistory.time_updated))


It should be returning two rProduct's but it only returns one

On Tuesday, June 20, 2017 at 10:20:51 AM UTC-7, Mike Bayer wrote:
>
>
>
> On 06/20/2017 01:04 PM, Ryan Weinstein wrote: 
> > 
> > 
> > On Tuesday, June 20, 2017 at 9:53:10 AM UTC-7, Jonathan Vanasco wrote: 
> > 
> > you're almost there. 
> > 
> > flip a few lines around to rewrite the query to select from 
> > product...  e.g. something like 
> > 
> > SELECT r_product_list.* FROM r_product_list 
> > JOIN r_product_category_history_list on 
> > r_product_list.r_id=r_product_category_history_list.r_id 
> > JOIN r_product_reviews_historical_details_list on 
> > r_product_list.most_recent_historical_reviews_id=
> r_product_reviews_historical_details_list.id 
> >  
> > 
> > then it should be clear how to convert to the sqlalchemy syntax. 
> > 
> > 
> > The problem is when I do that it doesn't return the correct number of 
> > instances. I need one instance of rProduct per entry into 
> > r_product_category_history_list. 
>
> the ORM deduplicates full entities (e.g. mapped objects) when returned 
> from a Query object.  This is so that eager loading schemes don't return 
> dupes.  If you need each object associated with something distinct, add 
> that to the query: 
>
> session.query(Product, product_category_list.some_column) 
>
>
>
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > You received this message because you are subscribed to the Google 
> > Groups "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> > an email to sqlalchemy+...@googlegroups.com  
> > . 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Only return single entity from query

2017-06-20 Thread Ryan Weinstein



On Tuesday, June 20, 2017 at 9:53:10 AM UTC-7, Jonathan Vanasco wrote:
>
> you're almost there.
>
> flip a few lines around to rewrite the query to select from product... 
>  e.g. something like
>
> SELECT r_product_list.* FROM r_product_list
> JOIN r_product_category_history_list on 
> r_product_list.r_id=r_product_category_history_list.r_id
> JOIN r_product_reviews_historical_details_list on 
> r_product_list.most_recent_historical_reviews_id=
> r_product_reviews_historical_details_list.id 
>
> then it should be clear how to convert to the sqlalchemy syntax.
>

The problem is when I do that it doesn't return the correct number of 
instances. I need one instance of rProduct per entry into 
r_product_category_history_list. 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Only return single entity from query

2017-06-20 Thread Ryan Weinstein



On Tuesday, June 20, 2017 at 9:53:10 AM UTC-7, Jonathan Vanasco wrote:
>
> you're almost there.
>
> flip a few lines around to rewrite the query to select from product... 
>  e.g. something like
>
> SELECT r_product_list.* FROM r_product_list
> JOIN r_product_category_history_list on 
> r_product_list.r_id=r_product_category_history_list.r_id
> JOIN r_product_reviews_historical_details_list on 
> r_product_list.most_recent_historical_reviews_id=
> r_product_reviews_historical_details_list.id 
>
> then it should be clear how to convert to the sqlalchemy syntax.
>


Here are my models btw:v


class rProduct(Product):
__tablename__ =  'r_product_list'
id = db.Column(ForeignKey(u'product_list.id'))
r_id = db.Column(String(64), nullable=False, index=True, 
primary_key=True)

category_history = relationship("rProductCategoryHistory")
image = db.Column(String(512))
category_history_entry = relationship("rProductCategoryHistory", 
uselist=False)
history = relationship("rProductHistoricalDetails", 
back_populates='r_product', foreign_keys='rProductHistoricalDetails.r_id')

most_recent_historical_details_id = db.Column(Integer,
ForeignKey('r_product_historical_details_list.id'))
most_recent_historical_details_entry = 
relationship("rProductHistoricalDetails", uselist=False, 
foreign_keys=[most_recent_historical_details_id])

most_recent_historical_reviews_id = db.Column(Integer,
ForeignKey('r_product_reviews_historical_details_list.id'))
most_recent_historical_reviews_entry = 
relationship("rProductReviewsHistoricalDetails", uselist=False, 
foreign_keys=[most_recent_historical_reviews_id])

__mapper_args__ = {
'polymorphic_identity':'r_product'
}


class rProductCategoryHistory(db.Model):
__tablename__ =  'r_product_category_history_list'
id = db.Column(Integer, primary_key=True)
r_id = db.Column(String(64), ForeignKey('r_product_list.r_id'), 
nullable=False)
product = relationship("rProduct", uselist=False)

in_new = db.Column(db.Boolean(1), default=False, server_default="0")
in_trending = db.Column(db.Boolean(1), default=False, 
server_default="0")
in_top_finds = db.Column(db.Boolean(1), default=False, 
server_default="0")
in_deals = db.Column(db.Boolean(1), default=False, server_default="0")
is_tsv = db.Column(db.Boolean(1), default=False, server_default="0")
is_big_deal = db.Column(db.Boolean(1), default=False, 
server_default="0")

availability = db.Column(String(64))

time_updated = db.Column(TIMESTAMP, default=func.now(),
onupdate=func.now(), server_default=text("CURRENT_TIMESTAMP"))



class rProductHistoricalDetails(db.Model):
__tablename__ = 'r_product_historical_details_list'
id = db.Column(Integer, primary_key=True)

r_id = db.Column(String(64), ForeignKey('r_product_list.r_id'), 
nullable=False)
r_product = relationship("rProduct", uselist=False, foreign_keys=[r_id])
# product = relationship("rProduct", uselist=False, 
back_populates=[r_id])

r_price = db.Column( Float ) 
sale_price = db.Column( Float ) 
video = db.Column(String(512))

time_updated = db.Column(TIMESTAMP, default=func.now(),
onupdate=func.now(), server_default=text("CURRENT_TIMESTAMP"))

class rProductReviewsHistoricalDetails(db.Model):
__tablename__ = 'r_product_reviews_historical_details_list'
id = db.Column(Integer, primary_key=True)
r_id = db.Column(String(64), ForeignKey('r_product_list.r_id'), 
nullable=False)
r_product = relationship("rProduct", uselist=False, foreign_keys=[r_id])
total_reviews = db.Column(db.Integer())
average_rating = db.Column(db.Float())

time_updated = db.Column(TIMESTAMP, default=func.now(),
onupdate=func.now(), server_default=text("CURRENT_TIMESTAMP"))

 

-- 
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] Re: Only return single entity from query

2017-06-20 Thread mike bayer




On 06/20/2017 01:04 PM, Ryan Weinstein wrote:



On Tuesday, June 20, 2017 at 9:53:10 AM UTC-7, Jonathan Vanasco wrote:

you're almost there.

flip a few lines around to rewrite the query to select from
product...  e.g. something like

SELECT r_product_list.* FROM r_product_list
JOIN r_product_category_history_list on
r_product_list.r_id=r_product_category_history_list.r_id
JOIN r_product_reviews_historical_details_list on

r_product_list.most_recent_historical_reviews_id=r_product_reviews_historical_details_list.id


then it should be clear how to convert to the sqlalchemy syntax.


The problem is when I do that it doesn't return the correct number of 
instances. I need one instance of rProduct per entry into 
r_product_category_history_list.


the ORM deduplicates full entities (e.g. mapped objects) when returned 
from a Query object.  This is so that eager loading schemes don't return 
dupes.  If you need each object associated with something distinct, add 
that to the query:


session.query(Product, product_category_list.some_column)





--
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 - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Only return single entity from query

2017-06-20 Thread Jonathan Vanasco

you're almost there.

flip a few lines around to rewrite the query to select from product... 
 e.g. something like

SELECT r_product_list.* FROM r_product_list
JOIN r_product_category_history_list on 
r_product_list.r_id=r_product_category_history_list.r_id
JOIN r_product_reviews_historical_details_list on 
r_product_list.most_recent_historical_reviews_id=r_product_reviews_historical_details_list.id
 

then it should be clear how to convert to the sqlalchemy syntax.

-- 
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] Is this a valid inheritance configuration ?

2017-06-20 Thread yoch . melka

Hi,

I wish to have a two-levels inheritance, but I don't know how to proceed.
The docs says that "only one discriminator column or SQL expression may be 
configured for the entire inheritance hierarchy".

I tried with this example (mixing joined and single inheritances) :

class Person(Base):
__tablename__ = 'person'

id = Column(Integer, primary_key=True)
type = Column(String(50), nullable=False)
name = Column(String(50))

__mapper_args__ = {
'polymorphic_identity': 'person',
'polymorphic_on': type
}

class Manager(Person):
__tablename__ = 'manager'

id = Column(ForeignKey('person.id'), primary_key=True)
manager_name = Column(String(30))

__mapper_args__ = {
'polymorphic_identity': 'manager',
}

class EngineerBase(Person):
__tablename__ = 'engineer'

id = Column(ForeignKey('person.id'), primary_key=True)
engineer_name = Column(String(30))

class EngineerType1(EngineerBase):

__mapper_args__ = {
'polymorphic_identity': 'engineer_t1',
}

class EngineerType2(EngineerBase):

__mapper_args__ = {
'polymorphic_identity': 'engineer_t2',
}

It seems to work, but it this correct ?

-- 
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] Is this a valid inheritance configuration ?

2017-06-20 Thread mike bayer




On 06/20/2017 02:08 PM, yoch.me...@gmail.com wrote:

Hi,

I wish to have a two-levels inheritance, but I don't know how to proceed.
The docs says that "only one discriminator column or SQL expression may 
be configured for the entire inheritance hierarchy".


I tried with this example (mixing joined and single inheritances) :

|
classPerson(Base):
 __tablename__ ='person'

 id =Column(Integer,primary_key=True)
 type =Column(String(50),nullable=False)
 name =Column(String(50))

 __mapper_args__ ={
'polymorphic_identity':'person',
'polymorphic_on':type
}

classManager(Person):
 __tablename__ ='manager'

 id =Column(ForeignKey('person.id'),primary_key=True)
 manager_name =Column(String(30))

 __mapper_args__ ={
'polymorphic_identity':'manager',
}

classEngineerBase(Person):
 __tablename__ ='engineer'

 id =Column(ForeignKey('person.id'),primary_key=True)
 engineer_name =Column(String(30))

classEngineerType1(EngineerBase):

 __mapper_args__ ={
'polymorphic_identity':'engineer_t1',
}

classEngineerType2(EngineerBase):

 __mapper_args__ ={
'polymorphic_identity':'engineer_t2',
}
|

It seems to work, but it this correct ?


it's correct, you only have one "polymorphic_on" set up, and that's what 
that means.





--
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 - 
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] Re: Only return single entity from query

2017-06-20 Thread mike bayer

You'd need to turn on echo='debug' on create_engine() and look at the 
rows actually being sent to see what's happening.

On 06/20/2017 01:59 PM, Ryan Weinstein wrote:
Thanks for the reply Mike. SQLAlchemy is a fantastic ORM and you've done 
an amazing job with it.

You're suggestion hasn't fixed my issue though, perhaps there's 
something fundamental I'm misunderstanding?

Here is the query I'm testing:

|
 r_product_category_history_list = 
db.Model.metadata.tables['r_product_category_history_list'] query = 
db.session.query(rProduct, 
r_product_category_history_list.c.time_updated)\ 
.join(rProductCategoryHistory)\ 
.order_by(desc(rProductCategoryHistory.time_updated))

|

It should be returning two rProduct's but it only returns one

On Tuesday, June 20, 2017 at 10:20:51 AM UTC-7, Mike Bayer wrote:

On 06/20/2017 01:04 PM, Ryan Weinstein wrote:
 >
 >
 > On Tuesday, June 20, 2017 at 9:53:10 AM UTC-7, Jonathan Vanasco
wrote:
 >
 > you're almost there.
 >
 > flip a few lines around to rewrite the query to select from
 > product...  e.g. something like
 >
 > SELECT r_product_list.* FROM r_product_list
 > JOIN r_product_category_history_list on
 > r_product_list.r_id=r_product_category_history_list.r_id
 > JOIN r_product_reviews_historical_details_list on
 >
r_product_list.most_recent_historical_reviews_id=r_product_reviews_historical_details_list.id

 > >
 >
 > then it should be clear how to convert to the sqlalchemy syntax.
 >
 >
 > The problem is when I do that it doesn't return the correct
number of
 > instances. I need one instance of rProduct per entry into
 > r_product_category_history_list.

the ORM deduplicates full entities (e.g. mapped objects) when returned
from a Query object.  This is so that eager loading schemes don't
return
dupes.  If you need each object associated with something distinct, add
that to the query:

session.query(Product, product_category_list.some_column)

 >
 > --
 > SQLAlchemy -
 > The Python SQL Toolkit and Object Relational Mapper
 >
 > http://www.sqlalchemy.org/
 >
 > To post example code, please provide an MCVE: Minimal, Complete, and
 > Verifiable Example. See http://stackoverflow.com/help/mcve
 for a full
 > description.
 > ---
 > You received this message because you are subscribed to the Google
 > Groups "sqlalchemy" group.
 > To unsubscribe from this group and stop receiving emails from it,
send
 > an email to sqlalchemy+...@googlegroups.com 
 > .
 > To post to this group, send email to sqlal...@googlegroups.com

 > .
 > Visit this group at https://groups.google.com/group/sqlalchemy
.
 > For more options, visit https://groups.google.com/d/optout
.

--
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 - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Can't connect to SQLite databases

[sqlalchemy] increasing memory on simple query

Re: [sqlalchemy] Re: ImportError: cannot import name postgresql

Re: [sqlalchemy] Is this a valid inheritance configuration ?

[sqlalchemy] Re: Only return single entity from query

Re: [sqlalchemy] Re: Only return single entity from query

[sqlalchemy] Re: Only return single entity from query

[sqlalchemy] Re: Only return single entity from query

Re: [sqlalchemy] Re: Only return single entity from query

[sqlalchemy] Re: Only return single entity from query

[sqlalchemy] Is this a valid inheritance configuration ?

Re: [sqlalchemy] Is this a valid inheritance configuration ?

Re: [sqlalchemy] Re: Only return single entity from query

13 matches

Site Navigation

Mail list logo

Footer information