date:20160523

Re: [sqlalchemy] Using SQL_CALC_FOUND_ROWS

2016-05-23 Thread Mike Bayer

I answered this for someone some time ago, assuming FOUND_ROWS() is
local to a MySQL session (note this is not the same thing as a
SQLAlchemy session though typically these map in a 1-1 fashion) there
should be no issue, as long as you are using each connection in just one
application thread at a time (and if you are using ORM sessions, using
each Session in just one application thread at a time). Also of course
on that one Session / Connection you need to make sure to query
FOUND_ROWS() immediately after your query.

if you need this to be local to a cursor, you'd need to drop into direct
connection / cursor access see
http://docs.sqlalchemy.org/en/latest/core/connections.html#working-with-raw-dbapi-connections
but I have a feeling this is not necessary.

On 05/23/2016 04:34 PM, James Li wrote:

Hi All,
I want to use SQL_CALC_FOUND_ROWS with my select query and use a
following SELECT FOUND_ROWS() to get the total count in sqlalchemy. But
how do I make sure /SELECT FOUND_ROWS()/ returns the correct cached
value in the case of *concurrent* queries of /select
SQL_CALC_FOUND_ROWS/ happen? Is the total count cached somewhere in a
sqlalchemy session after /select SQL_CALC_FOUND_ROWS/ runs? Can I
control where it gets cached in my code?

Thanks!
-james

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

--
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] Using SQL_CALC_FOUND_ROWS

2016-05-23 Thread James Li

Hi All,
I want to use SQL_CALC_FOUND_ROWS with my select query and use a following 
SELECT FOUND_ROWS() to get the total count in sqlalchemy. But how do I make 
sure *SELECT FOUND_ROWS()* returns the correct cached value in the case of 
*concurrent* queries of *select SQL_CALC_FOUND_ROWS* happen? Is the total 
count cached somewhere in a sqlalchemy session after *select 
SQL_CALC_FOUND_ROWS* runs? Can I control where it gets cached in my code?

Thanks!
-james

-- 
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] Create Table DDL options before "("

2016-05-23 Thread Mike Bayer


I've added you to the "contributors" group so you should have access now.

On 05/23/2016 12:45 PM, Mark Sandan wrote:

No worries, I'll put up a reference to the PR and gerrit proposal in
case anybody wants to chime in. The change is proposed in git PR #275
 and in gerrit
 (which I'm still getting used too).


On Sunday, May 22, 2016 at 8:23:14 PM UTC-7, Mike Bayer wrote:

OK so we'll probably just add the hook you've proposed.

I've not had anytime to work on a computer for like five days straight
which puts me super behind, ill try to get to your PR soon.


On 05/19/2016 01:08 PM, Mark Sandan wrote:
> Sure, the Teradata database has create table ddl where you can
specify
> certain options that are separated by commas. We have a reference
manual
> available online specifying the full DDL here
>

>
(see
> page 383 for the full syntax).  Here is some example DDL:
>
> |
> CREATE TABLE t1 ,FALLBACK ,
>  NO BEFORE JOURNAL,
>  NO AFTER JOURNAL,
>  CHECKSUM = DEFAULT,
>  DEFAULT MERGEBLOCKRATIO
>  (
>   c1 VARCHAR(128) NOT NULL,
>   Id BYTE(4) NOT NULL,
>   OwnerId BYTE(4) NOT NULL
>  )
>UNIQUE PRIMARY INDEX ( c1 )
>  UNIQUE INDEX ( Id );
> |
>
>
>
> On Thursday, May 19, 2016 at 8:31:49 AM UTC-7, Mike Bayer wrote:
>
> saw your pull request, just curious what database / DDL is
this?  Just
> like to see the finished product that you're going for.
>
>
>
> On 05/18/2016 09:19 PM, Mark Sandan wrote:
> > Hi, I'm implementing a dialect for sqlalchemy and would like
to add
> > options before the '(' but after the table name in
visit_create. I
> know
> > I can just subclass visit_create in my ddl compiler but it
seems
> kind of
> > silly since I'd be making a small change. Something like the
> following:
> > |
> >
> >
> > defvisit_create_table(self,create):
> >   table =create.element
> >   preparer =self.preparer
> >
> >   text ="\nCREATE "
> >   iftable._prefixes:
> >  text +=" ".join(table._prefixes)+" "
> >   text +="TABLE "+preparer.format_table(table)+"
> "+table_options(table)+" ("
> >
> > |
> >
> > table_options would be a function in the ddl compiler
provided by the
> > dialect that takes dialect specific keywords and simply
appends comma
> > delimited values. I essentially need something like the
'prefixes'
> > keyword in Table but for after the table name and before the
left
> > parens. Any ideas?
> >
> > --
> > 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

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

Re: [sqlalchemy] Create Table DDL options before "("

2016-05-23 Thread Mark Sandan

No worries, I'll put up a reference to the PR and gerrit proposal in case 
anybody wants to chime in. The change is proposed in git PR #275 
 and in gerrit 
 (which I'm still getting used too).


On Sunday, May 22, 2016 at 8:23:14 PM UTC-7, Mike Bayer wrote:
>
> OK so we'll probably just add the hook you've proposed. 
>
> I've not had anytime to work on a computer for like five days straight 
> which puts me super behind, ill try to get to your PR soon. 
>
>
> On 05/19/2016 01:08 PM, Mark Sandan wrote: 
> > Sure, the Teradata database has create table ddl where you can specify 
> > certain options that are separated by commas. We have a reference manual 
> > available online specifying the full DDL here 
> > <
> https://www.google.com/url?sa=t=j==s=web=1=rja=8=0ahUKEwjZkreizubMAhVIXh4KHWp0DrAQFggdMAA=http%3A%2F%2Ftunweb.teradata.ws%2Ftunstudent%2FTeradataUserManuals%2FSQL_Reference_--_Data_Definition_Syntax_Example.pdf=AFQjCNFEIVA1TfbRXNV_hQfCVNBe7gZt3g=dPP1GmBUBEyB186Pkh6HmA>
>  
> (see 
> > page 383 for the full syntax).  Here is some example DDL: 
> > 
> > | 
> > CREATE TABLE t1 ,FALLBACK , 
> >  NO BEFORE JOURNAL, 
> >  NO AFTER JOURNAL, 
> >  CHECKSUM = DEFAULT, 
> >  DEFAULT MERGEBLOCKRATIO 
> >  ( 
> >   c1 VARCHAR(128) NOT NULL, 
> >   Id BYTE(4) NOT NULL, 
> >   OwnerId BYTE(4) NOT NULL 
> >  ) 
> >UNIQUE PRIMARY INDEX ( c1 ) 
> >  UNIQUE INDEX ( Id ); 
> > | 
> > 
> > 
> > 
> > On Thursday, May 19, 2016 at 8:31:49 AM UTC-7, Mike Bayer wrote: 
> > 
> > saw your pull request, just curious what database / DDL is this? 
>  Just 
> > like to see the finished product that you're going for. 
> > 
> > 
> > 
> > On 05/18/2016 09:19 PM, Mark Sandan wrote: 
> > > Hi, I'm implementing a dialect for sqlalchemy and would like to 
> add 
> > > options before the '(' but after the table name in visit_create. I 
> > know 
> > > I can just subclass visit_create in my ddl compiler but it seems 
> > kind of 
> > > silly since I'd be making a small change. Something like the 
> > following: 
> > > | 
> > > 
> > > 
> > > defvisit_create_table(self,create): 
> > >   table =create.element 
> > >   preparer =self.preparer 
> > > 
> > >   text ="\nCREATE " 
> > >   iftable._prefixes: 
> > >  text +=" ".join(table._prefixes)+" " 
> > >   text +="TABLE "+preparer.format_table(table)+" 
> > "+table_options(table)+" (" 
> > > 
> > > | 
> > > 
> > > table_options would be a function in the ddl compiler provided by 
> the 
> > > dialect that takes dialect specific keywords and simply appends 
> comma 
> > > delimited values. I essentially need something like the 'prefixes' 
> > > keyword in Table but for after the table name and before the left 
> > > parens. Any ideas? 
> > > 
> > > -- 
> > > 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 
> > . 
> > 
> > -- 
> > 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. 
>

-- 
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] zero_indexes broken for 2d-arrays?

2016-05-23 Thread Brian Cherinka

Thanks Mike.  That fixed it!.  

On Monday, May 23, 2016 at 10:18:15 AM UTC-4, Mike Bayer wrote:
>
>
>
> On 05/23/2016 10:12 AM, Brian Cherinka wrote: 
> > 
> > Hi, 
> > 
> > It seems like the ARRAY option zero_indexes=True is broken for 
> > 2-dimensional arrays.   Is this a bug that is fixed in 1.1?  I'm 
> > actually using the subclass ARRAY_D as a fix for the __getitem__ 
> > indexing.  It works for 1-D arrays. 
> > 
>
>
> if you're passing zero_indexes=True then that needs to be propagated to 
> the new ARRAY type being created inside of __getitem__.  The recipe 
> appears to miss this. 
>
> class ARRAY_D(ARRAY): 
>  class Comparator(ARRAY.Comparator): 
>  def __getitem__(self, index): 
>  super_ = super(ARRAY_D.Comparator, self).__getitem__(index) 
>  if not isinstance(index, slice) and self.type.dimensions > 1: 
>  super_ = type_coerce( 
>  super_, 
>  ARRAY_D( 
>  self.type.item_type, 
>  dimensions=self.type.dimensions - 1, 
>  zero_indexes=self.type.zero_indexes) 
>  ) 
>  return super_ 
>  comparator_factory = Comparator 
>
> > 
> > *1-D array* 
> > wave = Column(ARRAY_D(Float, zero_indexes=True)) 
> > SQL 
> > | 
> > selectw.wavelength[17]fromdatadb.wavelength asw; 
> >  wavelength 
> >  
> > 3634.96 
> > (1row) 
> > | 
> > 
> > ORM - instance and class side 
> > | 
> > wave =session.query(datadb.Wavelength).first() 
> > wave.wavelength[16] 
> > 3634.96 
> > 
> > session.query(datadb.Wavelength.wavelength[16]).one() 
> > (3634.96) 
> > | 
> > 
> > 
> > *2-D array* 
> > value = Column(ARRAY_D(Float, dimensions=2, zero_indexes=True)) 
> > SQL 
> > || 
> > | 
> > selecte.value[17][18]from dapdb.emline ase limit 1; 
> > 
> >value 
> > --- 
> >  4.962736845652115 
> > | 
> > 
> > ORM - instance and class side 
> > || 
> > | 
> > # correct value on instance side 
> > emline=session.query(dapdb.EmLine).first() 
> > emline.value[16][17] 
> > 4.962736845652115 
> > 
> > # expected correct indexing - wrong value 
> > session.query(dapdb.EmLine.value[16][17]).first() 
> > (4.8138361075679565) 
> > 
> > # both "1-indexed" - wrong value 
> > session.query(dapdb.EmLine.value[17][18]).first() 
> > (5.380134788537585) 
> > 
> > # first index is correct, but second is incremented by 1 - correct value 
> > session.query(dapdb.EmLine.value[16][18]).first() 
> > (4.962736845652115) 
> > | 
> > 
> > Cheers, Brian 
> > 
> > -- 
>
>

-- 
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] zero_indexes broken for 2d-arrays?

2016-05-23 Thread Mike Bayer




On 05/23/2016 10:12 AM, Brian Cherinka wrote:


Hi,

It seems like the ARRAY option zero_indexes=True is broken for
2-dimensional arrays.   Is this a bug that is fixed in 1.1?  I'm
actually using the subclass ARRAY_D as a fix for the __getitem__
indexing.  It works for 1-D arrays.




if you're passing zero_indexes=True then that needs to be propagated to 
the new ARRAY type being created inside of __getitem__.  The recipe 
appears to miss this.


class ARRAY_D(ARRAY):
class Comparator(ARRAY.Comparator):
def __getitem__(self, index):
super_ = super(ARRAY_D.Comparator, self).__getitem__(index)
if not isinstance(index, slice) and self.type.dimensions > 1:
super_ = type_coerce(
super_,
ARRAY_D(
self.type.item_type,
dimensions=self.type.dimensions - 1,
zero_indexes=self.type.zero_indexes)
)
return super_
comparator_factory = Comparator



*1-D array*
wave = Column(ARRAY_D(Float, zero_indexes=True))
SQL
|
selectw.wavelength[17]fromdatadb.wavelength asw;
 wavelength

3634.96
(1row)
|

ORM - instance and class side
|
wave =session.query(datadb.Wavelength).first()
wave.wavelength[16]
3634.96

session.query(datadb.Wavelength.wavelength[16]).one()
(3634.96)
|


*2-D array*
value = Column(ARRAY_D(Float, dimensions=2, zero_indexes=True))
SQL
||
|
selecte.value[17][18]from dapdb.emline ase limit 1;

   value
---
 4.962736845652115
|

ORM - instance and class side
||
|
# correct value on instance side
emline=session.query(dapdb.EmLine).first()
emline.value[16][17]
4.962736845652115

# expected correct indexing - wrong value
session.query(dapdb.EmLine.value[16][17]).first()
(4.8138361075679565)

# both "1-indexed" - wrong value
session.query(dapdb.EmLine.value[17][18]).first()
(5.380134788537585)

# first index is correct, but second is incremented by 1 - correct value
session.query(dapdb.EmLine.value[16][18]).first()
(4.962736845652115)
|

Cheers, Brian

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


--
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] zero_indexes broken for 2d-arrays?

2016-05-23 Thread Brian Cherinka


Hi, 

It seems like the ARRAY option zero_indexes=True is broken for 
2-dimensional arrays.   Is this a bug that is fixed in 1.1?  I'm actually 
using the subclass ARRAY_D as a fix for the __getitem__ indexing.  It works 
for 1-D arrays.


*1-D array*
wave = Column(ARRAY_D(Float, zero_indexes=True))
SQL
select w.wavelength[17] from datadb.wavelength as w;
 wavelength

3634.96
(1 row)

ORM - instance and class side
wave = session.query(datadb.Wavelength).first()
wave.wavelength[16]
3634.96

session.query(datadb.Wavelength.wavelength[16]).one()
(3634.96)


*2-D array*
value = Column(ARRAY_D(Float, dimensions=2, zero_indexes=True))
SQL
select e.value[17][18] from dapdb.emline as e limit 1;

   value
---
 4.962736845652115

ORM - instance and class side
# correct value on instance side
emline=session.query(dapdb.EmLine).first()
emline.value[16][17]
4.962736845652115

# expected correct indexing - wrong value
session.query(dapdb.EmLine.value[16][17]).first()
(4.8138361075679565)

# both "1-indexed" - wrong value
session.query(dapdb.EmLine.value[17][18]).first()
(5.380134788537585)

# first index is correct, but second is incremented by 1 - correct value
session.query(dapdb.EmLine.value[16][18]).first()
(4.962736845652115)

Cheers, Brian

-- 
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] Calculated relationships

2016-05-23 Thread Mike Bayer


I see that it's not on the web GUI either.  Yes, that's a google bug.

In https://support.google.com/groups/answer/2466386?hl=en , I click the 
"post and always allow future messages from author" button, same button 
I've been clicking for like eight years.Googling (heh) for any 
reported issue here doesn't immediately turn up anything.   Feel free to 
poke around.   I'll try to remember to press the "post" button 
individually first next time and see if it takes.




On 05/23/2016 12:55 AM, Jonathan Vanasco wrote:



On Sunday, May 22, 2016 at 11:45:23 PM UTC-4, Mike Bayer wrote:


Anyway I approve the message and the poster and it should show up in
the
group, I do get the released message emailed to me at least.


For whatever reasons, it's only going to you.  All the "user's first
post" threads for the past few months have been missing the first post.
 Google must have changed something on their end, because they used to
always show.

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


--
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] Using SQL_CALC_FOUND_ROWS

[sqlalchemy] Using SQL_CALC_FOUND_ROWS

Re: [sqlalchemy] Create Table DDL options before "("

Re: [sqlalchemy] Create Table DDL options before "("

Re: [sqlalchemy] zero_indexes broken for 2d-arrays?

Re: [sqlalchemy] zero_indexes broken for 2d-arrays?

[sqlalchemy] zero_indexes broken for 2d-arrays?

Re: [sqlalchemy] Calculated relationships

8 matches

Site Navigation

Mail list logo

Footer information