Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
Hi Michael,
I'm using a manual workaround, so this is only out of curiosity now.
Cf yr point 1:
1. Merging MoviesAndDirectorsAndGenres, with no primary key identifiers set,
sets it as pending in the Session. This now indicates that an INSERT into
all three tables will take place during flush, since the object had no
primary key when being merged.
not quite sure what 'primary key identifiers' are, but am assuming
that you mean explicitly setting the attributes that are mapped to the
join's composite primary key. let's try that:
new = MoviesAndDirectorsAndGenres()
new.movies_movie_id=8 #does not already exist in the movies table
new.movies_title='new movie'
new.directors_director_id=2 #already exists in the directors table
new.genres_genre_id=6 #already exists too
so, the new object now has a primary key - (8,2,6). this is a new pk
for the join, but it is made up of pks from each of the constituent
tables - 2 of which are existing, and 1 of which is new.
The call to merge fails in the same way:
2010-07-22 13:40:46,063 INFO sqlalchemy.engine.base.Engine.0x...de90
SELECT truncated...
FROM movies JOIN movie_directors ON movies.movie_id =
movie_directors.movie_id JOIN directors ON directors.director_id =
movie_directors.director_id JOIN genres ON genres.genre_id =
movies.genre_id) AS mdg
WHERE mdg.movies_movie_id = ? AND mdg.directors_director_id = ? AND
mdg.genres_genre_id = ?
2010-07-22 13:40:46,075 INFO sqlalchemy.engine.base.Engine.0x...de90 (8, 2, 6)
2010-07-22 13:40:46,079 INFO sqlalchemy.engine.base.Engine.0x...de90
INSERT INTO genres (genre_id, name, description) VALUES (?, ?, ?)
2010-07-22 13:40:46,081 INFO sqlalchemy.engine.base.Engine.0x...de90
(6, None, None)
2010-07-22 13:40:46,084 INFO sqlalchemy.engine.base.Engine.0x...de90 ROLLBACK
attempted to add new composite object, failed with (IntegrityError)
PRIMARY KEY must be unique u'INSERT INTO genres (genre_id, name,
description) VALUES (?, ?, ?)' (6, None, None)
If I understand correctly, calling session.merge() on a composite
object does not make several different decisions about whether to do
inserts or updates across each constituent table - it simply makes
one, global decision based on its own composite pk?
Perhaps more granular decision-making would be an interesting feature
for future versions? Or does it just get impossibly complex for
arbitrary selectables?
anyways, thanks for all your help.
Harry
On Tue, Jul 20, 2010 at 5:14 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 11:51 AM, Harry Percival wrote:
attached. feel free to ignore the 'sqlite' folder, which is only
needed for ironpython. the error definitely occurs in cpython 2.6.3.
1. Merging MoviesAndDirectorsAndGenres, with no primary key identifiers set,
sets it as pending in the Session. This now indicates that an INSERT into
all three tables will take place during flush, since the object had no
primary key when being merged.
2. Existing Genres, Directors, and Movies objects are attached to the new
MoviesAndDirectorsAndGenres object. These attachments specify a primary
join condition that requests that the primary key of each of the Genres,
Directors, and Movies be populated into each of the director_id, genre_id,
and movie_id attributes of the MoviesAndDirectorsAndGenres during a flush.
3. The flush takes place, the director_id, genre_id, and movie_id attributes
of the pending MoviesAndDirectorsAndGenres are populated with integer values
that happen to already exist in those tables, and the INSERT fails.
Since you don't really want an INSERT to take place here, I would recommend
using only Genres, Directors, and Movies objects.
It would be very helpful if you could not send any more zipfiles, only a
single .py script is needed for test case illustration. This makes my life
easier and also for the many people that will be reading your post in the
future.
rgds,
hp
On Tue, Jul 20, 2010 at 3:54 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Jul 20, 2010, at 10:46 AM, Michael Bayer wrote:
On Jul 20, 2010, at 6:05 AM, Harry Percival wrote:
I'm clear on that. I'm only using session.add to do an insert when I
know i definitely want to. But you'll see I used session.merge on the
composite object, yet it still attempts to do an insert for rows that
already exist in its constituent tables...
you are setting a primary key, and adding right here:
my apologies, thats not the INSERT that fails.
please attach a full reproducing test script.
On Mon, Jul 19, 2010 at 5:19 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Jul 19, 2010, at 12:04 PM, Harry Percival wrote:
Michael, thanks, as ever, for your help.
So, I think I've managed to specify the relationships:
j = join(movies_table,md_table).join(directors_table).join(genres_table)
js = j.select(use_labels=True).alias('mdg')
r0 = relationship(Movies,
primaryjoin
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
I'm clear on that. I'm only using session.add to do an insert when I
know i definitely want to. But you'll see I used session.merge on the
composite object, yet it still attempts to do an insert for rows that
already exist in its constituent tables...
On Mon, Jul 19, 2010 at 5:19 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 19, 2010, at 12:04 PM, Harry Percival wrote:
Michael, thanks, as ever, for your help.
So, I think I've managed to specify the relationships:
j = join(movies_table,md_table).join(directors_table).join(genres_table)
js = j.select(use_labels=True).alias('mdg')
r0 = relationship(Movies,
primaryjoin=(js.c.movies_movie_id==movies_table.c.movie_id),
foreign_keys=([js.c.movies_movie_id])
)
r1 = relationship(Directors,
primaryjoin=(js.c.directors_director_id==directors_table.c.director_id),
foreign_keys=([js.c.directors_director_id])
)
r2 = relationship(Genres,
primaryjoin=(js.c.genres_genre_id==genres_table.c.genre_id),
foreign_keys=([js.c.genres_genre_id])
)
mapper(MoviesAndDirectorsAndGenres,
js,
properties={'movie':r0,
'director':r1,
'genre':r2
},
passive_updates=False)
To test it, I'm trying to create a new composite object, based on a
new movie but existing director genre:
session=Session()
m_new = Movies()
m_new.id=8
m_new.title = 'new movie'
session.add(m_new)
d2 = session.query(Directors).get(2)
print '***director=',d2
g6 = session.query(Genres).get(6)
print '***genre=',g6
oo_new = MoviesAndDirectorsAndGenres()
oo_new.movie = m_new
oo_new.director = d2
oo_new.genre = g6
try:
# another_new = session.merge(new)
session.merge(oo_new)
but sqla is doing some really weird stuff:
2010-07-19 16:56:01,877 INFO sqlalchemy.engine.base.Engine.0x...37f0 BEGIN
2010-07-19 16:56:01,878 INFO sqlalchemy.engine.base.Engine.0x...37f0 INSERT
INTO
movies (movie_id, title, description, genre_id, release_date) VALUES
(?, ?, ?,?, ?)
2010-07-19 16:56:01,880 INFO sqlalchemy.engine.base.Engine.0x...37f0
(8, 'new movie', None, None, None)
2010-07-19 16:56:01,882 INFO sqlalchemy.engine.base.Engine.0x...37f0
SELECT directors.director_id AS directors_director_id, directors.name
AS directors_name
FROM directors
WHERE directors.director_id = ?
2010-07-19 16:56:01,885 INFO sqlalchemy.engine.base.Engine.0x...37f0 (2,)
***director= test_multitable.Directors object at 0x021E2030
2010-07-19 16:56:01,888 INFO sqlalchemy.engine.base.Engine.0x...37f0
SELECT genres.genre_id AS genres_genre_id, genres.name AS genres_name,
genres.description AS genres_description
FROM genres
WHERE genres.genre_id = ?
2010-07-19 16:56:01,891 INFO sqlalchemy.engine.base.Engine.0x...37f0 (6,)
***genre= test_multitable.Genres object at 0x021E21B0
2010-07-19 16:56:01,894 INFO sqlalchemy.engine.base.Engine.0x...37f0
INSERT INTO genres (genre_id, name, description) VALUES (?, ?, ?)
2010-07-19 16:56:01,895 INFO sqlalchemy.engine.base.Engine.0x...37f0
(6, None, None)
2010-07-19 16:56:01,898 INFO sqlalchemy.engine.base.Engine.0x...37f0 ROLLBACK
attempted to add new composite object, failed with (IntegrityError)
PRIMARY KEY must be unique u'INSERT INTO genres (genre_id, name,
description) VALUES (?, ?, ?)' (6, None, None)
Traceback (most recent call last):
File test_multitable.py, line 105, in module
session.commit()
File sqlalchemy\orm\session.py, line 653, in commit
etc etc
IntegrityError: (IntegrityError) PRIMARY KEY must be unique u'INSERT INTO
genres
(genre_id, name, description) VALUES (?, ?, ?)' (6, None, None)
it seems to trying to insert a new genre, even though I've told it to
use an existing one?
I suppose, once I've done the insert into the movies table, i could
retrieve the new composite object via a session.query, instead of
trying to do a session.merge. but this wouldn't work for creating a
new Director object, since I also need to create an entry into the
bridging table, something i'm hoping sqla could manage for me...
if you say x = new Foo() x.id = 7; session.add(x); that is an INSERT, no
matter what the ID is, and will fail as above if that primary key already
exists. If you OTOH say x = session.merge(x), it will be an INSERT or an
UPDATE depending on whether or not primary key id #7 exists in the database
already or not.
You can get an overview of what the various methods do at:
http://www.sqlalchemy.org/docs/session.html#id1
rgds,
hp
On Mon, Jul 19, 2010 at 3:31 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Jul 19, 2010, at 10:24 AM, Harry Percival wrote:
OK, so I will treat any classes mapped to a join of multiple tables as
being a read-only API, and manually manage the write-API using
relationship
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
Michael, thanks, as ever, for your help.
So, I think I've managed to specify the relationships:
j = join(movies_table,md_table).join(directors_table).join(genres_table)
js = j.select(use_labels=True).alias('mdg')
r0 = relationship(Movies,
primaryjoin=(js.c.movies_movie_id==movies_table.c.movie_id),
foreign_keys=([js.c.movies_movie_id])
)
r1 = relationship(Directors,
primaryjoin=(js.c.directors_director_id==directors_table.c.director_id),
foreign_keys=([js.c.directors_director_id])
)
r2 = relationship(Genres,
primaryjoin=(js.c.genres_genre_id==genres_table.c.genre_id),
foreign_keys=([js.c.genres_genre_id])
)
mapper(MoviesAndDirectorsAndGenres,
js,
properties={'movie':r0,
'director':r1,
'genre':r2
},
passive_updates=False)
To test it, I'm trying to create a new composite object, based on a
new movie but existing director genre:
session=Session()
m_new = Movies()
m_new.id=8
m_new.title = 'new movie'
session.add(m_new)
d2 = session.query(Directors).get(2)
print '***director=',d2
g6 = session.query(Genres).get(6)
print '***genre=',g6
oo_new = MoviesAndDirectorsAndGenres()
oo_new.movie = m_new
oo_new.director = d2
oo_new.genre = g6
try:
#another_new = session.merge(new)
session.merge(oo_new)
but sqla is doing some really weird stuff:
2010-07-19 16:56:01,877 INFO sqlalchemy.engine.base.Engine.0x...37f0 BEGIN
2010-07-19 16:56:01,878 INFO sqlalchemy.engine.base.Engine.0x...37f0 INSERT INTO
movies (movie_id, title, description, genre_id, release_date) VALUES
(?, ?, ?,?, ?)
2010-07-19 16:56:01,880 INFO sqlalchemy.engine.base.Engine.0x...37f0
(8, 'new movie', None, None, None)
2010-07-19 16:56:01,882 INFO sqlalchemy.engine.base.Engine.0x...37f0
SELECT directors.director_id AS directors_director_id, directors.name
AS directors_name
FROM directors
WHERE directors.director_id = ?
2010-07-19 16:56:01,885 INFO sqlalchemy.engine.base.Engine.0x...37f0 (2,)
***director= test_multitable.Directors object at 0x021E2030
2010-07-19 16:56:01,888 INFO sqlalchemy.engine.base.Engine.0x...37f0
SELECT genres.genre_id AS genres_genre_id, genres.name AS genres_name,
genres.description AS genres_description
FROM genres
WHERE genres.genre_id = ?
2010-07-19 16:56:01,891 INFO sqlalchemy.engine.base.Engine.0x...37f0 (6,)
***genre= test_multitable.Genres object at 0x021E21B0
2010-07-19 16:56:01,894 INFO sqlalchemy.engine.base.Engine.0x...37f0
INSERT INTO genres (genre_id, name, description) VALUES (?, ?, ?)
2010-07-19 16:56:01,895 INFO sqlalchemy.engine.base.Engine.0x...37f0
(6, None, None)
2010-07-19 16:56:01,898 INFO sqlalchemy.engine.base.Engine.0x...37f0 ROLLBACK
attempted to add new composite object, failed with (IntegrityError)
PRIMARY KEY must be unique u'INSERT INTO genres (genre_id, name,
description) VALUES (?, ?, ?)' (6, None, None)
Traceback (most recent call last):
File test_multitable.py, line 105, in module
session.commit()
File sqlalchemy\orm\session.py, line 653, in commit
etc etc
IntegrityError: (IntegrityError) PRIMARY KEY must be unique u'INSERT INTO genres
(genre_id, name, description) VALUES (?, ?, ?)' (6, None, None)
it seems to trying to insert a new genre, even though I've told it to
use an existing one?
I suppose, once I've done the insert into the movies table, i could
retrieve the new composite object via a session.query, instead of
trying to do a session.merge. but this wouldn't work for creating a
new Director object, since I also need to create an entry into the
bridging table, something i'm hoping sqla could manage for me...
rgds,
hp
On Mon, Jul 19, 2010 at 3:31 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 19, 2010, at 10:24 AM, Harry Percival wrote:
OK, so I will treat any classes mapped to a join of multiple tables as
being a read-only API, and manually manage the write-API using
relationship().
It doesn't look like I can define a relationship from the composite
mapped class to individual tables though?
#single table mappers:
mapper(Movies,movies_table)
mapper(Directors,directors_table)
mapper(Genres,genres_table)
j = join(movies_table,md_table).join(directors_table).join(genres_table)
r0 = relationship(Movies,
primaryjoin=(movies_table.c.movie_id==md_table.c.movie_id),
foreign_keys=([md_table.c.movie_id]))
r1 =
relationship(Directors)#,primaryjoin=(and_(movies_table.c.movie_id==md_table.c.movie_id,md_table.c.director_id==directors_table.c.director_id)))#,secondaryjoin=(md_table.c.director_id==directors_table.c.director_id),secondary=md_table)
r2 = relationship(Genres)
mapper(MoviesAndDirectorsAndGenres,
j.select(use_labels=True).alias('mdg'),
j.select(...).alias() is the selectable that you have to define all your
[sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
A new problem, which seems to occur in both IronPython and normal Python:
I have a database with tables for movies, directors, genres (and a
bridging table movie_directors)
I have a class mapped to a join of all three of the above
j = join(movies_table,md_table).join(directors_table).join(genres_table)
mapper(MoviesAndDirectorsAndGenres,j.select(use_labels=True).alias('moviesdirectorsgenres'))
this works fine. i can query the database ok using this mapper.
The tables already have some values in.
Now, I want to create a new composite object, that references some of
the existing values, but not all - say it's a new movie by an existing
director in an existing genre:
new = MoviesAndDirectorsAndGenres()
new.movies_movie_id=8 #does not already exist in the table
new.directors_director_id=2 #already exists in the table
new.genres_genre_id=6 #already exists too
session.add(new)
this fails with a constraint error / integrity error, because SQLA
tries to re-insert genre #6 even though it already exists. it also
happens if i use session.merge(new). it also happens if i fully
specify all of the attributes of each movie/director/genre correctly.
Is this expected behaviour? is there any way to get sqla to
intelligently only do inserts when necessary? is it something to do
with cascade configuration, and if so, how do i configure cascades on
a mapper that's not based on relationship() but based on join()?
thanks in advance!
HP
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
thanks Michael. I really appreciate your help.
How should use .merge()? I've tried both:
another_new = MoviesAndDirectorsAndGenres() #init another_new as blank row
another_new = session.merge(new) #attempt to merge with my 'new'
object that has desired attributes
and
new = session.merge(another_new) #attempt to merge blank object to my
new object with desired attributes
the former fails trying to add a genre row that already exists, the
latter fails trying to add an object with all null values.
the thing is, I'm really not trying to merge anything. I'm trying to
create a new object, which happens to be made up partially of new
entries in some tables, and existing entries in other tables.
genres_genre_id refers to the primary key of the genres table, and a
genre with genre_id=6 already exists.
am i going to have to map an ORM class to each of the composite
tables, and use references to them via relationship() instead of
hoping sql can figure it out by itself from the tables that make up
the join construct?
can supply a sqlite database and some source code if it helps?
rgds,
Harry
On Thu, Jul 15, 2010 at 9:48 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 15, 2010, at 2:52 PM, Harry Percival wrote:
A new problem, which seems to occur in both IronPython and normal Python:
I have a database with tables for movies, directors, genres (and a
bridging table movie_directors)
I have a class mapped to a join of all three of the above
j = join(movies_table,md_table).join(directors_table).join(genres_table)
mapper(MoviesAndDirectorsAndGenres,j.select(use_labels=True).alias('moviesdirectorsgenres'))
this works fine. i can query the database ok using this mapper.
The tables already have some values in.
Now, I want to create a new composite object, that references some of
the existing values, but not all - say it's a new movie by an existing
director in an existing genre:
new = MoviesAndDirectorsAndGenres()
new.movies_movie_id=8 #does not already exist in the table
new.directors_director_id=2 #already exists in the table
new.genres_genre_id=6 #already exists too
session.add(new)
this fails with a constraint error / integrity error, because SQLA
tries to re-insert genre #6 even though it already exists. it also
happens if i use session.merge(new). it also happens if i fully
specify all of the attributes of each movie/director/genre correctly.
Is this expected behaviour? is there any way to get sqla to
intelligently only do inserts when necessary? is it something to do
with cascade configuration, and if so, how do i configure cascades on
a mapper that's not based on relationship() but based on join()?
you should be using merge(), and you should ensure that the objects being
merged have the correct primary key values. If genre #6 refers to some
other column that isn't the primary key of that row, then you'd have to
ensure you put the correct primary key value on your object first before
merging it.
you can always check what decision merge() made by asking, obj in
session.new versus obj in session.dirty.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
--
--
Harry J.W. Percival
--
Italy Mobile: +39 389 095 8959
UK Mobile: +44 (0) 78877 02511
Skype: harry dot percival
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] problems with multi-table mapping on IronPython
thanks Michael. I took a look at README.unittests, but had some
difficulties running them with ironpython (ipy setup.py test fails, and
nosetests.exe doesn't allow you to specificy an interpreter). Still, i
found some help on the interwebs, as well as in the nose __init__.py.
eventually i was able to run the tests by running the following script:
import sys, os
#import ironclad #not needed. i think.
sys.path.append(r'C:\Python26\lib')
#now load Jeff Hardys sqlite dll which is in sqlite folder (sqlite not
supported on ipy)
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)),'sqlite'))
import clr
clr.AddReference('IronPython.SQLite')
#load plugin
from sqlalchemy.test.noseplugin import NoseSQLAlchemy
from nose import main
if __name__ == '__main__':
main(addplugins=[NoseSQLAlchemy()])
cf
http://stackoverflow.com/questions/3198500/how-to-use-nose-with-ironpython/3223278#3223278
Here's the summary:
*Ran 1862 tests in 141.873s
FAILED (SKIP=7, errors=1483, failures=52)
*
oh dear! and yet, it seemed to be working pretty well until i ran into this
problem with mutli-table mapping. Also, Jeff Hardy's porting of sqlite is
still in development, so it may be the cause of a few errors... might take a
look at
On Sat, Jul 10, 2010 at 6:11 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Jul 10, 2010, at 12:07 PM, Harry Percival wrote:
OK, just in case anyone else is foolishly trying to run SQLA on
ironpython, here's the workaround I've found:
instead of attempting to use the mapper directly on the join:
j = join(movies_table,md_table).join(directors_table)
mapper(MoviesAndDirectors,j) #ipy errors here
use a mapper on a select based on the join:
mapper(MoviesAndDirectors,j.select(use_labels=True).alias('moviesanddirectors'))
seems to be working ok for now.
one final, polite plea - how can i run the sqla test suite, to see what
other bugs might be lurking?
running the tests is fully described in README.unittests
cheers,
Harry
On Thu, Jul 8, 2010 at 10:52 AM, Harry Percival
harry.perci...@gmail.comwrote:
Here's the source code of my test - let me know if I'm doing anything
wrong here
from sqlalchemy import create_engine
from sqlalchemy.orm import mapper
from sqlalchemy.sql.expression import join
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import MetaData
import traceback
try:
import clr
import os
import sys
sys.path.append(os.path.join(os.path.abspath(os.path.curdir),'sqlite'))
clr.AddReference('Ironpython.Sqlite') #need this for sqlite to work
on ironpython. refers to a dll in zip file.
except:
#not ipy
pass
#from sqlalchemy.ext.sqlsoup import SqlSoup #sqlsoup also errors
engine = create_engine('sqlite:///moviedemo_simple.db3') #moviedemo file
also in zip file.
Session = sessionmaker(bind=engine)
class Movies(object):
pass
class Directors(object):
pass
class Genres(object):
pass
class MoviesAndDirectors(object):
pass
meta = MetaData()
meta.reflect(bind=engine)
all_tables = meta.tables
movies_table = all_tables['movies']
genres_table = all_tables['genres']
directors_table = all_tables['directors']
md_table = all_tables['movie_directors']
mapper(Movies,movies_table)
mapper(Directors,directors_table)
mapper(Genres,genres_table)
session = Session()
print session.query(Movies).all()[0]
print session.query(Directors).all()[0]
j = join(movies_table,md_table).join(directors_table)
try:
mapper(MoviesAndDirectors,j)#ipy errors here
mad1 = session.query(MoviesAndDirectors).all()[0]
print mad1
except Exception, e:
print 'caught exception',e
last_error = e
traceback.print_exc()
how can i run the sqlalchemy test suite? I see it needs nose, i've
installed that. but i'm not clear what command to run to launch tests.
rgds,
harry
On Tue, Jul 6, 2010 at 6:40 PM, Harry Percival
harry.perci...@gmail.comwrote:
Hi Michael,
thanks for replying - the reason I attached a zipfile is because sqlite
isn't supported natively on ironpython, so I've had to include the source
and a dll for it. So, if you did have time to open it up and take a peek,
I'd very much appreciate it.
Alternatively, how can I run the sqla unit tests?
On Tue, Jul 6, 2010 at 3:56 PM, Michael Bayer
mike...@zzzcomputing.comwrote:
On Jul 6, 2010, at 4:18 AM, Harry Percival wrote:
Hi,
I've got an error which occurs in ironpython but not in cpython. can
anyone replicate? See attached. I'm using IPY 2.6.
*string:1: DeprecationWarning: object.__init__() takes no parameters
for type _keyed_weakref
string:1: DeprecationWarning: object.__init__() takes no parameters
for type KeyedRef
Movies object at 0x0034
Directors object at 0x0038
caught exception 'NoneType' object has no attribute 'set'
Traceback (most recent call last):
File D:\workspace\resolver\test_multitable.py, line 54
Re: [sqlalchemy] problems with multi-table mapping on IronPython
OK, just in case anyone else is foolishly trying to run SQLA on ironpython,
here's the workaround I've found:
instead of attempting to use the mapper directly on the join:
j = join(movies_table,md_table).join(directors_table)
mapper(MoviesAndDirectors,j) #ipy errors here
use a mapper on a select based on the join:
mapper(MoviesAndDirectors,j.select(use_labels=True).alias('moviesanddirectors'))
seems to be working ok for now.
one final, polite plea - how can i run the sqla test suite, to see what
other bugs might be lurking?
cheers,
Harry
On Thu, Jul 8, 2010 at 10:52 AM, Harry Percival harry.perci...@gmail.comwrote:
Here's the source code of my test - let me know if I'm doing anything wrong
here
from sqlalchemy import create_engine
from sqlalchemy.orm import mapper
from sqlalchemy.sql.expression import join
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import MetaData
import traceback
try:
import clr
import os
import sys
sys.path.append(os.path.join(os.path.abspath(os.path.curdir),'sqlite'))
clr.AddReference('Ironpython.Sqlite') #need this for sqlite to work on
ironpython. refers to a dll in zip file.
except:
#not ipy
pass
#from sqlalchemy.ext.sqlsoup import SqlSoup #sqlsoup also errors
engine = create_engine('sqlite:///moviedemo_simple.db3') #moviedemo file
also in zip file.
Session = sessionmaker(bind=engine)
class Movies(object):
pass
class Directors(object):
pass
class Genres(object):
pass
class MoviesAndDirectors(object):
pass
meta = MetaData()
meta.reflect(bind=engine)
all_tables = meta.tables
movies_table = all_tables['movies']
genres_table = all_tables['genres']
directors_table = all_tables['directors']
md_table = all_tables['movie_directors']
mapper(Movies,movies_table)
mapper(Directors,directors_table)
mapper(Genres,genres_table)
session = Session()
print session.query(Movies).all()[0]
print session.query(Directors).all()[0]
j = join(movies_table,md_table).join(directors_table)
try:
mapper(MoviesAndDirectors,j)#ipy errors here
mad1 = session.query(MoviesAndDirectors).all()[0]
print mad1
except Exception, e:
print 'caught exception',e
last_error = e
traceback.print_exc()
how can i run the sqlalchemy test suite? I see it needs nose, i've
installed that. but i'm not clear what command to run to launch tests.
rgds,
harry
On Tue, Jul 6, 2010 at 6:40 PM, Harry Percival
harry.perci...@gmail.comwrote:
Hi Michael,
thanks for replying - the reason I attached a zipfile is because sqlite
isn't supported natively on ironpython, so I've had to include the source
and a dll for it. So, if you did have time to open it up and take a peek,
I'd very much appreciate it.
Alternatively, how can I run the sqla unit tests?
On Tue, Jul 6, 2010 at 3:56 PM, Michael Bayer
mike...@zzzcomputing.comwrote:
On Jul 6, 2010, at 4:18 AM, Harry Percival wrote:
Hi,
I've got an error which occurs in ironpython but not in cpython. can
anyone replicate? See attached. I'm using IPY 2.6.
*string:1: DeprecationWarning: object.__init__() takes no parameters
for type _keyed_weakref
string:1: DeprecationWarning: object.__init__() takes no parameters
for type KeyedRef
Movies object at 0x0034
Directors object at 0x0038
caught exception 'NoneType' object has no attribute 'set'
Traceback (most recent call last):
File D:\workspace\resolver\test_multitable.py, line 54, in module
mapper(MoviesAndDirectors,j)#ipy errors here
File D:\workspace\resolver\sqlalchemy\orm\__init__.py, line 818, in
mapper
return Mapper(class_, local_table, *args, **params)
File D:\workspace\resolver\sqlalchemy\orm\mapper.py, line 210, in
__init__
self._configure_properties()
File D:\workspace\resolver\sqlalchemy\orm\mapper.py, line 563, in
_configure_properties
self._configure_property(column_key,
File D:\workspace\resolver\sqlalchemy\orm\mapper.py, line 755, in
_configure_property
prop.instrument_class(self)
File D:\workspace\resolver\sqlalchemy\orm\properties.py, line 87, in
instrument_class
attributes.register_descriptor(
File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 1424,
in register_descriptor
manager.instrument_attribute(key, descriptor)
File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 1012,
in instrument_attribute
self.install_descriptor(key, inst)
File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 1054,
in install_descriptor
setattr(self.class_, key, inst)
File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 151, in
__set__
self.impl.set(instance_state(instance), instance_dict(instance),
value, None)
AttributeError: 'NoneType' object has no attribute 'set'
*
does this look like a bug with ironpython? if so, I'll report it to the
developers, but i need a little more help tracking down exactly what's going
Re: [sqlalchemy] problems with multi-table mapping on IronPython
Here's the source code of my test - let me know if I'm doing anything wrong
here
from sqlalchemy import create_engine
from sqlalchemy.orm import mapper
from sqlalchemy.sql.expression import join
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import MetaData
import traceback
try:
import clr
import os
import sys
sys.path.append(os.path.join(os.path.abspath(os.path.curdir),'sqlite'))
clr.AddReference('Ironpython.Sqlite') #need this for sqlite to work on
ironpython. refers to a dll in zip file.
except:
#not ipy
pass
#from sqlalchemy.ext.sqlsoup import SqlSoup #sqlsoup also errors
engine = create_engine('sqlite:///moviedemo_simple.db3') #moviedemo file
also in zip file.
Session = sessionmaker(bind=engine)
class Movies(object):
pass
class Directors(object):
pass
class Genres(object):
pass
class MoviesAndDirectors(object):
pass
meta = MetaData()
meta.reflect(bind=engine)
all_tables = meta.tables
movies_table = all_tables['movies']
genres_table = all_tables['genres']
directors_table = all_tables['directors']
md_table = all_tables['movie_directors']
mapper(Movies,movies_table)
mapper(Directors,directors_table)
mapper(Genres,genres_table)
session = Session()
print session.query(Movies).all()[0]
print session.query(Directors).all()[0]
j = join(movies_table,md_table).join(directors_table)
try:
mapper(MoviesAndDirectors,j)#ipy errors here
mad1 = session.query(MoviesAndDirectors).all()[0]
print mad1
except Exception, e:
print 'caught exception',e
last_error = e
traceback.print_exc()
how can i run the sqlalchemy test suite? I see it needs nose, i've
installed that. but i'm not clear what command to run to launch tests.
rgds,
harry
On Tue, Jul 6, 2010 at 6:40 PM, Harry Percival harry.perci...@gmail.comwrote:
Hi Michael,
thanks for replying - the reason I attached a zipfile is because sqlite
isn't supported natively on ironpython, so I've had to include the source
and a dll for it. So, if you did have time to open it up and take a peek,
I'd very much appreciate it.
Alternatively, how can I run the sqla unit tests?
On Tue, Jul 6, 2010 at 3:56 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Jul 6, 2010, at 4:18 AM, Harry Percival wrote:
Hi,
I've got an error which occurs in ironpython but not in cpython. can
anyone replicate? See attached. I'm using IPY 2.6.
*string:1: DeprecationWarning: object.__init__() takes no parameters
for type _keyed_weakref
string:1: DeprecationWarning: object.__init__() takes no parameters for
type KeyedRef
Movies object at 0x0034
Directors object at 0x0038
caught exception 'NoneType' object has no attribute 'set'
Traceback (most recent call last):
File D:\workspace\resolver\test_multitable.py, line 54, in module
mapper(MoviesAndDirectors,j)#ipy errors here
File D:\workspace\resolver\sqlalchemy\orm\__init__.py, line 818, in
mapper
return Mapper(class_, local_table, *args, **params)
File D:\workspace\resolver\sqlalchemy\orm\mapper.py, line 210, in
__init__
self._configure_properties()
File D:\workspace\resolver\sqlalchemy\orm\mapper.py, line 563, in
_configure_properties
self._configure_property(column_key,
File D:\workspace\resolver\sqlalchemy\orm\mapper.py, line 755, in
_configure_property
prop.instrument_class(self)
File D:\workspace\resolver\sqlalchemy\orm\properties.py, line 87, in
instrument_class
attributes.register_descriptor(
File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 1424, in
register_descriptor
manager.instrument_attribute(key, descriptor)
File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 1012, in
instrument_attribute
self.install_descriptor(key, inst)
File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 1054, in
install_descriptor
setattr(self.class_, key, inst)
File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 151, in
__set__
self.impl.set(instance_state(instance), instance_dict(instance),
value, None)
AttributeError: 'NoneType' object has no attribute 'set'
*
does this look like a bug with ironpython? if so, I'll report it to the
developers, but i need a little more help tracking down exactly what's going
wrong...
its likely some slightly different behavior in ironpython regarding
descriptors. If you don't have a lot of SQLA experience, it would be
extremely difficult to get SQLA running with a new Python interpreter.
Getting it to run on Jython took a huge amount of effort and weeks/months of
bughunting, both in SQLA and Jython itself. We currently don't have any
resources to get it to work on IronPython as well.
For bonus points: In the attached database, there's a many-to-many
relationship between 'movies' and 'directors' via a simple joining table.
How come SQLA isn't able to figure this out on its own and let me just
join(movies_table, directors_table)? It seems
Re: [sqlalchemy] mappers and non_primary arg
Hey, a workaround i've used is is to manually set primary key to being a composite of all columns if the table has no actual primary key. however, this will mean that sqlalch ignores any duplicate rows, which you may not want. hp. On Thu, Jul 1, 2010 at 7:41 PM, sandro dentella san...@e-den.it wrote: Hi, I'm trying to use non_primary arg of function 'mapper'. Currently I have a GUI widget to browse/edit tables that is based on introspection of the mapper. My goal would be to reuse all the machinary of table browsing even when browsing tables tat where built w/o primary key. I'm ready to understand that I can't use persistence but I thought that flagging as a non primary mapper I could assemble a mapper even from a table missing a primary_key, that's what I thought was the meaning of: Construct a Mapper that will define only the selection of instances, not their persistence. What I get is: ipdb mapper(X, table, non_primary=True) *** ArgumentError: Mapper Mapper|lavori_class|lavori|non-primary could not assemble any primary key columns for mapped table 'lavori' So wht's the real meaning of primary_key? thanks sandro *:-) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- -- Harry J.W. Percival -- Italy Mobile: +39 389 095 8959 UK Mobile: +44 (0) 78877 02511 (may be turned off) Skype: harry.percival Email: harry.perci...@gmail.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] problems with multi-table mapping on IronPython
Hi Michael, thanks for replying - the reason I attached a zipfile is because sqlite isn't supported natively on ironpython, so I've had to include the source and a dll for it. So, if you did have time to open it up and take a peek, I'd very much appreciate it. Alternatively, how can I run the sqla unit tests? On Tue, Jul 6, 2010 at 3:56 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jul 6, 2010, at 4:18 AM, Harry Percival wrote: Hi, I've got an error which occurs in ironpython but not in cpython. can anyone replicate? See attached. I'm using IPY 2.6. *string:1: DeprecationWarning: object.__init__() takes no parameters for type _keyed_weakref string:1: DeprecationWarning: object.__init__() takes no parameters for type KeyedRef Movies object at 0x0034 Directors object at 0x0038 caught exception 'NoneType' object has no attribute 'set' Traceback (most recent call last): File D:\workspace\resolver\test_multitable.py, line 54, in module mapper(MoviesAndDirectors,j)#ipy errors here File D:\workspace\resolver\sqlalchemy\orm\__init__.py, line 818, in mapper return Mapper(class_, local_table, *args, **params) File D:\workspace\resolver\sqlalchemy\orm\mapper.py, line 210, in __init__ self._configure_properties() File D:\workspace\resolver\sqlalchemy\orm\mapper.py, line 563, in _configure_properties self._configure_property(column_key, File D:\workspace\resolver\sqlalchemy\orm\mapper.py, line 755, in _configure_property prop.instrument_class(self) File D:\workspace\resolver\sqlalchemy\orm\properties.py, line 87, in instrument_class attributes.register_descriptor( File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 1424, in register_descriptor manager.instrument_attribute(key, descriptor) File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 1012, in instrument_attribute self.install_descriptor(key, inst) File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 1054, in install_descriptor setattr(self.class_, key, inst) File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 151, in __set__ self.impl.set(instance_state(instance), instance_dict(instance), value, None) AttributeError: 'NoneType' object has no attribute 'set' * does this look like a bug with ironpython? if so, I'll report it to the developers, but i need a little more help tracking down exactly what's going wrong... its likely some slightly different behavior in ironpython regarding descriptors. If you don't have a lot of SQLA experience, it would be extremely difficult to get SQLA running with a new Python interpreter. Getting it to run on Jython took a huge amount of effort and weeks/months of bughunting, both in SQLA and Jython itself. We currently don't have any resources to get it to work on IronPython as well. For bonus points: In the attached database, there's a many-to-many relationship between 'movies' and 'directors' via a simple joining table. How come SQLA isn't able to figure this out on its own and let me just join(movies_table, directors_table)? It seems unneccesary to have to specify the extra join(movies_table,md_table).join(directors_table)... I don't generally open full zipfiled applications, so if you want to attach a succinct, single-file code example that would help. If you have relationships between two classes, the relationship() function is used to establish that, which would allow query.join(Movie.directors) to generate the joins automatically. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- -- Harry J.W. Percival -- Italy Mobile: +39 389 095 8959 UK Mobile: +44 (0) 78877 02511 (may be turned off) Skype: harry.percival Email: harry.perci...@gmail.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] sqlite transaction rollback after pk clash not working?
Hi Michael,
the get_table_as_columns_dict starts a new session and does a select all,
returning results as a dictionary of columns-lists. my reading of the
debug output though, is that this happens *after* a rollback which seems to
be called automatically:
debug output starts from the first attempt to commit all rows to the
database - ie in the try clause, before the except clause
2010-06-17 11:06:55,682 INFO sqlalchemy.engine.base.Engine.0x...0x35 BEGIN
2010-06-17 11:06:55,991 INFO sqlalchemy.engine.base.Engine.0x...0x35 INSERT
INTO Table1 (pk, col1, col2) VALUES (?, ?, ?)
2010-06-17 11:06:55,996 INFO sqlalchemy.engine.base.Engine.0x...0x35 (1, 'a
good row', 'abc')
2010-06-17 11:06:56,049 INFO sqlalchemy.engine.base.Engine.0x...0x35 INSERT
INTO Table1 (pk, col1, col2) VALUES (?, ?, ?)
2010-06-17 11:06:56,053 INFO sqlalchemy.engine.base.Engine.0x...0x35 (1, 'a
row with pk collision', 'jkl')
2010-06-17 11:06:56,090 INFO sqlalchemy.engine.base.Engine.0x...0x35
ROLLBACK * this rollback here I didn't explicitly call for, it seems
to happen automatically when the pk clash happens*
==
DEBUG-- encountered error, attempting one-at-a-time commits *- we're
now in the except clause
*2010-06-17 11:06:56,397 INFO sqlalchemy.engine.base.Engine.0x...0x35 BEGIN
2010-06-17 11:06:56,413 INFO sqlalchemy.engine.base.Engine.0x...0x35 SELECT
Table1.pk AS Table1_pk, Table1.col1 AS Table1_col1, Table1.col2 AS
Table1_col2
FROM Table1
2010-06-17 11:06:56,421 INFO sqlalchemy.engine.base.Engine.0x...0x35 ()
database contents as follows {'col2': ['abc'], 'col1': ['a good row'], 'pk':
[1L]}
de
debug output is generated cos my engine is set to echo=True.
What do you mean 'the session is unusable here'? do you mean i can't use
the same session inside the try and except clauses? and is it possible that
the new session that get_table_as_columns_dict creates somehow sneaks in
before the try: clause's session has completed the rollback (because it
doesn't look like that from the debug output...). How else would i roll
back the transaction anyway?
here's it's code, for info:
class MyTable(object):
special class to represent a database table.
used in mapper, but has some useful class-methods for querying the whole
table...
@classmethod
def get_table_as_columns_dict(cls):
return the table as a dictionary,
with keys as column names and values as list of column values
NB - dictionaries are unsorted, so cols may show up in any order!
if cls.am_mapped():
table_dict = {}
for col in cls.column_names():
table_dict[col] = []
for row in cls.select_all():
for col in cls.column_names():
if row is None:
#sqlalchemy bug?? retrieving a row with from a
single column table with val=null fails
table_dict[col].append(None)
else:
table_dict[col].append(getattr(row,col))
return table_dict
else: return None
thanks for your help!
bonus question: is it really worth it? the alternative is to stop trying
to add all the rows in a single commit, and just commit them one-by-one from
the beginning (in fact that's what i've done to get round this bug -
essentially delete the try clause and only use the code from the except
clause). But I was worried there might be a big perf. difference?
On Thu, Jun 17, 2010 at 3:26 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Jun 17, 2010, at 6:21 AM, Harry Percival wrote:
`Hey all,
I have some code that attempts to add a bunch of new entries to a table in
a single commit, with some try/excepts designed to catch any primary key
errors. if any errors occur, i want to be able to manually go through and
re-attempt each line one by one to understand which ones fail and which
don't, and to make sure all the legal ones get done.
def add_rows_to_table(self,all_rows):
Session = sessionmaker(bind=self.engine)
session = Session()
for row in all_rows:
item = MyClass(row)
session.add(item)
try:
session.commit()
except Exception,e:
#roll back, start new session, add rows one by one, raise more
specific error
print '='*10,'\nDEBUG-- encountered error, attempting
one-at-a-time commits'
print 'database contents as follows
%s'%str(MyTable.get_table_as_columns_dict())
session.rollback()
session.close()
for rowno, row in enumerate(all_rows):
#commit rows one at a time
etc etc
However, I'm getting some unexpected behaviour. When I do my initial
transaction which attempts to do all rows at the same time, it falls over as
expected if it runs into a PK clash and says it's doing a rollback.. BUT in
fact any of the successful rows before
[sqlalchemy] sqlite transaction rollback after pk clash not working?
Hey all,
I have some code that attempts to add a bunch of new entries to a table in a
single commit, with some try/excepts designed to catch any primary key
errors. if any errors occur, i want to be able to manually go through and
re-attempt each line one by one to understand which ones fail and which
don't, and to make sure all the legal ones get done.
def add_rows_to_table(self,all_rows):
Session = sessionmaker(bind=self.engine)
session = Session()
for row in all_rows:
item = MyClass(row)
session.add(item)
try:
session.commit()
except Exception,e:
#roll back, start new session, add rows one by one, raise more
specific error
print '='*10,'\nDEBUG-- encountered error, attempting
one-at-a-time commits'
print 'database contents as follows
%s'%str(MyTable.get_table_as_columns_dict())
session.rollback()
session.close()
for rowno, row in enumerate(all_rows):
#commit rows one at a time
etc etc
However, I'm getting some unexpected behaviour. When I do my initial
transaction which attempts to do all rows at the same time, it falls over as
expected if it runs into a PK clash and says it's doing a rollback.. BUT in
fact any of the successful rows before the pk clash seem to still be in the
db, ie they were'nt rolled back. is this expected behaviour?
2010-06-17 11:06:55,682 INFO sqlalchemy.engine.base.Engine.0x...0x35 BEGIN
2010-06-17 11:06:55,991 INFO sqlalchemy.engine.base.Engine.0x...0x35 INSERT
INTO Table1 (pk, col1, col2) VALUES (?, ?, ?)
2010-06-17 11:06:55,996 INFO sqlalchemy.engine.base.Engine.0x...0x35 (1, 'a
good row', 'abc')
2010-06-17 11:06:56,049 INFO sqlalchemy.engine.base.Engine.0x...0x35 INSERT
INTO Table1 (pk, col1, col2) VALUES (?, ?, ?)
2010-06-17 11:06:56,053 INFO sqlalchemy.engine.base.Engine.0x...0x35 (1, 'a
row with pk collision', 'jkl')
2010-06-17 11:06:56,090 INFO sqlalchemy.engine.base.Engine.0x...0x35
ROLLBACK
==
DEBUG-- encountered error, attempting one-at-a-time commits
2010-06-17 11:06:56,397 INFO sqlalchemy.engine.base.Engine.0x...0x35 BEGIN
2010-06-17 11:06:56,413 INFO sqlalchemy.engine.base.Engine.0x...0x35 SELECT
Table1.pk AS Table1_pk, Table1.col1 AS Table1_col1, Table1.col2 AS
Table1_col2
FROM Table1
2010-06-17 11:06:56,421 INFO sqlalchemy.engine.base.Engine.0x...0x35 ()
database contents as follows {'col2': ['abc'], 'col1': ['a good row'], 'pk':
[1L]} TABLE SHOULD BE EMPTY
seems to me like the rollback isn't working. at the sqlite command-line,
rollback works fine, so could it be a sqlalchemy problem?
sqlite create table tbl1('pk' numeric, 'col1' text, primary key (pk));
sqlite begin;
sqlite insert into tbl1 values(1,'a');
sqlite insert into tbl1 values(2,'b');
sqlite insert into tbl1 values(3,'c');
sqlite select * from tbl1;
1|a
2|b
3|c
sqlite insert into tbl1 values(3,'CLASH');
Error: column pk is not unique
sqlite select * from tbl1;
1|a
2|b
3|c
sqlite rollback;
sqlite select * from tbl1;
sqlite
thanks in advance for any help!
hp
--
--
Harry J.W. Percival
--
Italy Mobile: +39 389 095 8959
UK Mobile: +44 (0) 78877 02511 (may be turned off)
Skype: harry.percival
Email: harry.perci...@gmail.com
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] how do i take advantage of sqlite manifest typing / type affinity using sqlalchemy?
Not sure what the etiquette is re cross-posting to this list from stackoverflow? here's my question: http://stackoverflow.com/questions/3044518/how-do-i-take-advantage-of-sqlite-manifest-typing-type-affinity-using-sqlalchem I like the idea of sqlite's manifest typing / type affinity: http://www.sqlite.org/datatype3.html Essentially, if I set a column's affinity as 'numeric', it will duck type integers or floats to store them as such, but still allow me to store strings if I want to. Seems to me this is the best 'default' type for a column when i'm not sure ahead of time of what data i want to store in it. so off i go: metadata = MetaData() new_table = Table(table_name, metadata ) for col_name in column_headings: new_table.append_column(Column(col_name, sqlite.NUMERIC, #this should duck-type numbers but can handle strings as well primary_key=col_name in primary_key_columns)) new_table.create(self.engine, checkfirst=False) but when i try and store some string values, eg abc in the table, sqlalchemy falls over: File [...]\sqlalchemy\processors.py, line 79, in to_float return float(value) ValueError: invalid literal for float(): abc Boo, hiss. So, is there any way I can convince sqlalchemy to let sqlite do the typing? perhaps i can use a type from sqlalchemy.types instead of sqlachemy.dialects.sqlite? since the so post, i've done a little more digging. Am I going to have to write my own custom type as per http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types? hp -- -- Harry J.W. Percival -- Italy Mobile: +39 389 095 8959 UK Mobile: +44 (0) 78877 02511 (may be turned off) Skype: harry.percival Email: harry.perci...@gmail.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] how do i take advantage of sqlite manifest typing / type affinity using sqlalchemy?
Answered my own question: Define a custom column type, as per http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types a combination of the documentation and some trial error have given me this: class MyDuckType(sqlalchemy.types.TypeDecorator): SQLALchemy custom column type, designed to let sqlite handle the typing using 'numeric affinity' which intelligently handles both numbers and strings impl = sqlite.NUMERIC def bind_processor(self, dialect): #function for type coercion during db write return None #ie pass value as-is, let sqlite do the typing def result_processor(self, dialect, coltype): #function for type coercion during db read return None #ie pass value as sqlite has stored it, should be ducktyped already def process_bind_param(self, value, dialect): #any changes to an individual value before store in DN return value def process_result_value(self, value, dialect): #any changes to an individual value after retrieve from DB return value def copy(self): #not quite sure what this is for return MyDuckType() The current sqlalchemy dialect type returns to_float in bind_processor, which is why I was getting the errors before. i.m.v.v.h.o., this is a bug. for my bonus points: manually setting column type to MyDuckType in my metadata.reflect() code: def get_database_tables(engine): meta = MetaData() meta.reflect(bind=engine) tables = meta.raw_tables for tbl in tables.values(): for col in tbl.c: col.type = MyDuckType() return tables seems to work for me. Any suggestions / improvements? I've tentatively filled out a ticket on the sqlalchemy bug tracker, not sure if that was a little arrogant? On Tue, Jun 15, 2010 at 4:22 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jun 15, 2010, at 7:14 AM, Harry Percival wrote: Not sure what the etiquette is re cross-posting to this list from stackoverflow? here's my question: http://stackoverflow.com/questions/3044518/how-do-i-take-advantage-of-sqlite-manifest-typing-type-affinity-using-sqlalchem AFAIK this would be a function of Pysqlite.You can make your own SQLAlchemy types sure but thats SQLalchemy doing it, not SQLite's feature. I've read that sqlite documentation page many times and I've never seen what it says actually occur, particularly regarding numerics.Its either inaccurate or Pysqlite squashes the behavior, or I just don't really understand it. There's no way to get numerics of arbitrary precision in and out of SQLite, for example - if you google around for decimal support you'll see that its not possible - floating point conversions always kick in. I like the idea of sqlite's manifest typing / type affinity: http://www.sqlite.org/datatype3.html Essentially, if I set a column's affinity as 'numeric', it will duck type integers or floats to store them as such, but still allow me to store strings if I want to. Seems to me this is the best 'default' type for a column when i'm not sure ahead of time of what data i want to store in it. so off i go: metadata = MetaData() new_table = Table(table_name, metadata ) for col_name in column_headings: new_table.append_column(Column(col_name, sqlite.NUMERIC, #this should duck-type numbers but can handle strings as well primary_key=col_name in primary_key_columns)) new_table.create(self.engine, checkfirst=False) but when i try and store some string values, eg abc in the table, sqlalchemy falls over: File [...]\sqlalchemy\processors.py, line 79, in to_float return float(value) ValueError: invalid literal for float(): abc Boo, hiss. So, is there any way I can convince sqlalchemy to let sqlite do the typing? perhaps i can use a type from sqlalchemy.types instead of sqlachemy.dialects.sqlite? since the so post, i've done a little more digging. Am I going to have to write my own custom type as per http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types? hp -- -- Harry J.W. Percival -- Italy Mobile: +39 389 095 8959 UK Mobile: +44 (0) 78877 02511 (may be turned off) Skype: harry.percival Email: harry.perci...@gmail.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@googlegroups.com. To unsubscribe from
Re: [sqlalchemy] how do i take advantage of sqlite manifest typing / type affinity using sqlalchemy?
Thanks Michael - i've seen others complain about the arbitrary-precision numbers issue... I'm not so bothered by that, but i do like the ability to natively store integers and strings in the same column. On Tue, Jun 15, 2010 at 4:40 PM, Harry Percival harry.perci...@gmail.comwrote: Answered my own question: Define a custom column type, as per http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types a combination of the documentation and some trial error have given me this: class MyDuckType(sqlalchemy.types.TypeDecorator): SQLALchemy custom column type, designed to let sqlite handle the typing using 'numeric affinity' which intelligently handles both numbers and strings impl = sqlite.NUMERIC def bind_processor(self, dialect): #function for type coercion during db write return None #ie pass value as-is, let sqlite do the typing def result_processor(self, dialect, coltype): #function for type coercion during db read return None #ie pass value as sqlite has stored it, should be ducktyped already def process_bind_param(self, value, dialect): #any changes to an individual value before store in DN return value def process_result_value(self, value, dialect): #any changes to an individual value after retrieve from DB return value def copy(self): #not quite sure what this is for return MyDuckType() The current sqlalchemy dialect type returns to_float in bind_processor, which is why I was getting the errors before. i.m.v.v.h.o., this is a bug. for my bonus points: manually setting column type to MyDuckType in my metadata.reflect() code: def get_database_tables(engine): meta = MetaData() meta.reflect(bind=engine) tables = meta.raw_tables for tbl in tables.values(): for col in tbl.c: col.type = MyDuckType() return tables seems to work for me. Any suggestions / improvements? I've tentatively filled out a ticket on the sqlalchemy bug tracker, not sure if that was a little arrogant? On Tue, Jun 15, 2010 at 4:22 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jun 15, 2010, at 7:14 AM, Harry Percival wrote: Not sure what the etiquette is re cross-posting to this list from stackoverflow? here's my question: http://stackoverflow.com/questions/3044518/how-do-i-take-advantage-of-sqlite-manifest-typing-type-affinity-using-sqlalchem AFAIK this would be a function of Pysqlite.You can make your own SQLAlchemy types sure but thats SQLalchemy doing it, not SQLite's feature. I've read that sqlite documentation page many times and I've never seen what it says actually occur, particularly regarding numerics.Its either inaccurate or Pysqlite squashes the behavior, or I just don't really understand it. There's no way to get numerics of arbitrary precision in and out of SQLite, for example - if you google around for decimal support you'll see that its not possible - floating point conversions always kick in. I like the idea of sqlite's manifest typing / type affinity: http://www.sqlite.org/datatype3.html Essentially, if I set a column's affinity as 'numeric', it will duck type integers or floats to store them as such, but still allow me to store strings if I want to. Seems to me this is the best 'default' type for a column when i'm not sure ahead of time of what data i want to store in it. so off i go: metadata = MetaData() new_table = Table(table_name, metadata ) for col_name in column_headings: new_table.append_column(Column(col_name, sqlite.NUMERIC, #this should duck-type numbers but can handle strings as well primary_key=col_name in primary_key_columns)) new_table.create(self.engine, checkfirst=False) but when i try and store some string values, eg abc in the table, sqlalchemy falls over: File [...]\sqlalchemy\processors.py, line 79, in to_float return float(value) ValueError: invalid literal for float(): abc Boo, hiss. So, is there any way I can convince sqlalchemy to let sqlite do the typing? perhaps i can use a type from sqlalchemy.types instead of sqlachemy.dialects.sqlite? since the so post, i've done a little more digging. Am I going to have to write my own custom type as per http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types? hp -- -- Harry J.W. Percival -- Italy Mobile: +39 389 095 8959 UK Mobile: +44 (0) 78877 02511 (may be turned off) Skype: harry.percival Email: harry.perci...@gmail.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group
[sqlalchemy] reflecting existing databases with no a priori knowledge of their structure
Hi All,
I'm building a tool to extract info from databases. The
user/programmer doesn't have any advance knowledge of the structure of
the database before they load it, and i want to dynamically generate
mapped classes for the database.
i just want to check there isn't some helpful sqlalchemy stuff that
can make my life easier, cos it feels harder than it should be.
sqlsoup seems to expect you to know table names ahead of time. i can't
find a way of extracting a list of table names from db =
SqlSoup(engine) and i'm finding myself generating classes on the fly
using the type() function.
stuff like:
meta.reflect(bind=engine)
tables = meta.raw_tables
class MyTable(object):
pass
for t in tables:
tempclass = type('Table%d'%counter,(MyTable,),{'engine':self.engine})
mapper(tempclass,t)
then i use a bunch of classfunctions hanging off MyTable to do things
like return select alls ... anyways, this feels harder than it should
be. am i missing something? or is sqlalchemy simply not really used
much to work with existing / arbitrary databases?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] testing?
how do I run some self-testing in sqlalchemy? I found this doc, but it seems to be out of date?? http://svn.sqlalchemy.org/sqlalchemy/trunk/README.unittests just for fun, i'm trying to run these tests inside IronPython, so any pointers in that direction would be helpful also.. thx, HP -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
