[sqlalchemy] Re: mysql innodb table insert problem

[lilo]

If I do manual insert into sql server like INSERT INTO lookup
(username, shardname) VALUES ('0', 'shard1');, all works fine.  But
sqlalchemy doesn't insert for whatever reason into innodb table.

Here is my shard session:

create_session_lookup = sessionmaker(class_=ShardedSession,
autoflush=True, transactional=True)

I have shard session set to transactional.  Does this conflict with
innodb transaction?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: mysql innodb table insert problem

[lilo]

Thanks,  I got it to work now.  But why did it work for myisam table
in the first place. Shouldn't session scope problem also have affected
the inserts for myisam table.  Insert into myisam table worked because
it does not support transactions?

On Jul 11, 4:03 pm, Rick Morrison [EMAIL PROTECTED] wrote:
  I have shard session set to transactional.  Does this conflict with
  innodb transaction?

 No, but it means your inner sess.begin() and sess.commit() are now within
 the scope of an outer transaction, so your inner sess.commit() has no
 effect. Since you immediately issue a sess.clear() after your ineffective
 sess.commit(), when the outer transaction finally gets a chance to commit,
 the changes are now gone. If you're going to be handling transaction state
 yourself, then don't use a transactional session.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] mysql innodb table insert problem

[lilo]

I have set mysql tables to be innodb by default.  Data inserted using
sqlalchemy models never written to mysql innodb talbe.  Innodb table
is empty.  If I try to insert data into the myisam tables, all the
data get written to those tables.  Here is the log of sqlalchemy
insertion on innodb tables.  For every insert, I see BEGIN and there
aren't any corresponding commit each insert.

Sqlalchemy log:

INFO:sqlalchemy.engine.base.Engine.0x..cL:BEGIN
INFO:sqlalchemy.engine.base.Engine.0x..cL:INSERT INTO lookup
(username, shardname) VALUES (%s, %s)
INFO:sqlalchemy.engine.base.Engine.0x..cL:['0', 'shard1']
INFO:sqlalchemy.engine.base.Engine.0x..4c:BEGIN
INFO:sqlalchemy.engine.base.Engine.0x..4c:INSERT INTO lookup
(username, shardname) VALUES (%s, %s)
INFO:sqlalchemy.engine.base.Engine.0x..4c:['1', 'shard2']

==
#!/usr/bin/env python

import datetime, os
from sqlalchemy import *
from sqlalchemy import exceptions, sql
from sqlalchemy.orm import *
from sqlalchemy.orm.shard import ShardedSession
from sqlalchemy.sql import operators

from sqlalchemy import create_engine

from blog_engine import *
from lookup import Lookup
from post import Post
from post_config import sesslk

from elixir import *
import md5

from lookup_config import *

def load_data():
load_data_lookup()

def load_data_lookup():
session = None
setup_all()

for i in  range(DATA):

username1 = u%d % (i)
hasha = md5.new()
hasha.update(%s % username1)
valuea = hasha.digest()
remhexa = valuea.encode(hex)


rema = long(remhexa, 16)% SHARD

m1 = Lookup(username=%d % (i),
shardname=shard_lookup_dict['%s' % rema])
sess = create_session_lookup()
sess.begin()
sess.save(m1)
sess.commit()
sess.clear()

if __name__ == '__main__':
load_data()

===
from elixir import *

from sqlalchemy.orm.shard import ShardedSession
from datetime import datetime
from my_metadata import a_metadata

import time

__metadata__ = a_metadata
__session__ = None

class Lookup(Entity):
using_options(tablename='lookup')
id = Field(Integer(),  primary_key = True)
username = Field(String(30),  nullable = False, unique=True)
shardname = Field(String(100), nullable = False)

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] sqlalchemy connection pooling and mysql last_insert_id()

[lilo]

According my mysql, LAST_INSERT_ID() is connection specific, so there
is no problem from race conditions.  If I insert a record into a
autoincremented table and do last_insert_id() on it, would there be a
possibility where another insert happen just before selecting
last_insert_id().  This won't be a problem with mysql if there isn't
any connection pooling.  Since sqlalchemy has support for connection
pooling, would there be a chance where connection is shared with
another insert just before selecting last_insert_id()?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: sharding id_chooser query_chooser

[lilo]

For example, you have user, post, comment table.  Sharding is done by
user_id and shard_lookup is done via lookup table.  If one is going to
create a post, you would lookup the user_id in the lookup table and
insert the post entry into the shard where the user_id belongs to.
How would you translate this type of behaviour in sqlalchemy.

eg.
shardN - table1, ..., tableM
#each tables have field called username, and username field from user
table is not a foreign key to post or comment table.  There are no
foreign keys.
shard1 - user, post, comment
shard2 - user, post, comment

lookup_table has the following field:

username
shardname

Now, if you want to create and save a post entry, you would look it up
in lookup_table with the username to find the shardname and save it on
that shard.

Since shard lookup is done based on username for all three tables
(user, post, comment), you can have one common function,
def shard_chooser(mapper, instance, clause=None), for all three
tables.

What is the case for the other ShardSession related functions for the
user, post, comment tables, would each table would have their own
verison of

def id_chooser(query, ident)
def query_chooser(query)

?  And also not sure where and how you would associate lookup table,
username and shardname mapping, into these functions.

On Jun 27, 6:06 am, King Simon-NFHD78 [EMAIL PROTECTED]
wrote:
 Lilo wrote:

  My understanding of this query_chooser is that it's used when you want
  to execute orm's sql rather than raw sql.

  I don't quite understand what is visit_binary function do from
  attribute_shard.py example.  What does it mean binary.operator,
  binary.left, binary.right.clause and query._criterion?

  The sharding design behind our application is that we have a master
  lookup table and shards.  What shard to execute sql is based on
  querying master lookup table.

  taken from sqlalchemy attribute_shard.py example:

  def query_chooser(query):
  ids = []

  # here we will traverse through the query's criterion, searching
  # for SQL constructs.  we'll grab continent names as we find them
  # and convert to shard ids
  class FindContinent(sql.ClauseVisitor):
  def visit_binary(self, binary):
  if binary.left is weather_locations.c.continent:
  if binary.operator == operators.eq:
  ids.append(shard_lookup[binary.right.value])
  elif binary.operator == operators.in_op:
  for bind in binary.right.clauses:
  ids.append(shard_lookup[bind.value])

  FindContinent().traverse(query._criterion)
  if len(ids) == 0:
  return ['north_america', 'asia', 'europe', 'south_america']
  else:
  return ids

  thank you.

 Hi,

 (I'm probably going to get the details wrong here, but hopefully the
 general idea will be right)

 SQLAlchemy represents SQL expressions as objects, a bit like a parse
 tree. For example, there are classes that represent tables, joins,
 functions and so on. It uses a Visitor pattern
 (http://en.wikipedia.org/wiki/Visitor_pattern) to traverse these
 structures.

 A binary clause is an SQL expression with an operator, a left half and a
 right half. For example, in the clause 'id = 5', binary.left  is 'id',
 binary.right is '5', and binary.operator is '=' (or rather,
 operators.eq, which is the object that represents '=').

 The query_chooser function above uses a Visitor to look through all the
 SQL expressions that make up the query that is about to be executed.
 Because the only overridden method is 'visit_binary', anything other
 than binary clauses are ignored.

 The method body could be written in long-hand as:

 If the left part of the expression is 'weather_locations.continent':
If the expression is 'continent = XXX':
   add the shard for continent XXX
Else if the expression is 'continent IN (XXX, YYY)':
   add the shards for XXX and YYY

 (operators.in_op corresponds to the 'IN' operator, and
 binary.right.clauses contains the right-hand-side of that expression)

 The fallback case (if len(ids) == 0) happens if the visitor failed to
 find any expressions that it could handle, in which case all the shards
 will be queried.

 I don't understand your situation well enough to know how to adapt the
 example. If your master lookup table is basically doing the same job as
 the shard_lookup dictionary in the example, then you could replace
 shard_lookup above with a function call that does the query.

 I hope that helps,

 Simon
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: sharding id_chooser query_chooser

[lilo]

My understanding of this query_chooser is that it's used when you want
to execute orm's sql rather than raw sql.

I don't quite understand what is visit_binary function do from
attribute_shard.py example.  What does it mean binary.operator,
binary.left, binary.right.clause and query._criterion?

The sharding design behind our application is that we have a master
lookup table and shards.  What shard to execute sql is based on
querying master lookup table.

taken from sqlalchemy attribute_shard.py example:

def query_chooser(query):
ids = []

# here we will traverse through the query's criterion, searching
# for SQL constructs.  we'll grab continent names as we find them
# and convert to shard ids
class FindContinent(sql.ClauseVisitor):
def visit_binary(self, binary):
if binary.left is weather_locations.c.continent:
if binary.operator == operators.eq:
ids.append(shard_lookup[binary.right.value])
elif binary.operator == operators.in_op:
for bind in binary.right.clauses:
ids.append(shard_lookup[bind.value])

FindContinent().traverse(query._criterion)
if len(ids) == 0:
return ['north_america', 'asia', 'europe', 'south_america']
else:
return ids

thank you.

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] sharding id_chooser query_chooser

[lilo]

I am trying to understand what id_chooser and query_chooser do.
Id_chooser basically uses a instance primary key to determine what
shard the intance should be saved to?  My primary keys(globally
unique) are made of up more than one fields.  Would that be a problem
with Id_chooser?

How/when would do you query_chooser?

Can you set id_chooser and query_chooser to be None in:

create_session.configure(shards = {blah}, shard_chooser =
shard_chooser, id_chooser = None, query_chooser = None) ?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] sharding database elixir metadata.drop_all and metadata.create_all problem

[lilo]

I have tried this command:
a.a_metadata.drop_all(bind=db)
a.a_metadata.create_all(bind=db)

and they don't create any tables and I don't get any error at all.

With, setup_all(True, bind=db), creates table A and B on each engine.
I just want table A on m1,m2 and table B on n1,n2.  I have spent quite
some time searching and going over the docs but I can't figure out the
problem is.

=
### file c.py

#!/usr/bin/env python

from sqlalchemy import create_engine

import b
import a

m1 = create_engine(mysql://m1:[EMAIL PROTECTED]:3306/m1, echo=True)
m2 = create_engine(mysql://m2:[EMAIL PROTECTED]:3306/m2, echo=True)

n1 = create_engine(mysql://n1:[EMAIL PROTECTED]:3306/n1, echo=True)
n2 = create_engine(mysql://n2:[EMAIL PROTECTED]:3306/n2, echo=True)

# create tables
for db in (m1, m2):
a.a_metadata.drop_all(bind=db)
a.a_metadata.create_all(bind=db)

#setup_all(True, bind=db)

for db in (n1, n2):
 # setup_all(True, bind=db)
b.b_metadata.drop_all(bind=db)
b.b_metadata.create_all(bind=db)

===
### file a.py

from elixir import *

from datetime import datetime

a_metadata = metadata
__metada__ = a_metadata

class A(Entity):
using_options(tablename='a', auto_primarykey = False)
aname = Field(String(30),  primary_key = True, nullable = False,
unique=True)

=
### file b.py

from elixir import *

from datetime import datetime

b_metadata = metadata
__metada__ = b_metadata

class B(Entity):
using_options(tablename='b', auto_primarykey = False)
bname = Field(String(30),  primary_key = True, nullable = False,
unique=True)

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---