Re: [sqlalchemy] Efficient dev workflow for keeping test database in sync with dev database?
We usually run most of our our tests with sqlite:///memory and recreate the db every run. The only additional things we have to do is to seed the db with data as needed. That way we do not need to run alembic stuff on it at all. To test alembic migrations we will usually snapshot our staging db and run migrations against it. HTH AM On 1/8/15 2:50 PM, alchemy1 wrote: On Postgres, I run tests against a database which require that the tables already exist. The table definitions in the test db need to be identical to the table definitions in the dev db (in the test db they should be empty though). My development workflow is that I change model classes in my code, then run 'alembic revision --autogenerate' and 'alembic upgrade head' to update my dev db. But is there a way to configure alembic to keep my test db (running on my development machine on the same Postgres server as the dev db) in sync with my dev db? For example when 'alembic upgrade head' runs the latest revisions of schema changes against dev db then it could do it against the test db as well? Right now I don't have a good solution in place, is there an efficient workflow I could use to keep the test db in sync with the dev db? Ideally without requiring me to remember to run an extra command against the test db when I make a change to the dev db (e.g. if alembic could make changes to both databases at the same time there'd be no need to remember to do it in both places). -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: can someone tell me how to reset Alembic back to the state of my DB
Blow away the alembic versions table from the database and the folder with the versions file and patches files on disk and you should be good to go. HTH AM On 12/30/2014 06:01 PM, dewey wrote: Ok, I went ahead and just copied all the data into the clean new schema. So now my actual DB is in sync with my SA declarative models. How do I get Alembic to start tracking fresh from this point (ie delete any old revisions or old history)?? Thanks, D On Tuesday, December 30, 2014 7:49:03 PM UTC-6, dewey wrote: I first installed Alembic and while practicing, I created a first revision. I made some changes in my Declarative models in code And then I used SA to blow away my DB and recreate the whole schema new again And then I made a few more changes to my models (oops) AND loaded a bunch of data Now, I can't figure out how to get Alembic back into parity Can I point it at the DB to get it's base and then point it at my models to create a brand-new, first revision?? I'm kind of stumped. As a worst case, I've used SA to create a new parallel schema (in sync with current models) and I can select all the old data into it but I still need to know how to tell Alembic to forget (delete) all prior revisions and start over. Thanks for any tips!! Dewey -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Is my DB schema optimal?
My general suggestion for 'optimality' questions is to not worry about it. Start with a schema that seems natural and maps well to the problem you are trying to solve. Once you have it setup and working, then and only then focus on whether it needs to be optimized/denormalized etc etc. In a large percentage of the cases, for the loads that are demanded of db backed applications, IME one almost never needs to change the original schema. What would help more is to focus on the 'kind' of queries you are going to have and make sure you index the appropriate fields. HTH AM On 12/24/14 12:33 PM, msikma wrote: Hi there, I've got the following schema: http://pastie.org/private/w3oyxp5yjqggtiorknz6q Am I doing things right? To explain what I'm trying to do: right now, I'm trying to parse a dictionary file (edict2). It consists of dictionary 'entries', each of which have some basic information, then multiple 'readings', and multiple 'tags'. (And definitions, but those will come later.) Each reading item itself can also contain multiple 'tags'. There will not be too many connections per entry, but there will be many (170,000+) entries. So I've made two relationship tables. I think this should work. But I'm not sure if it is optimal. In pure SQL I'd probably do a (id, id) primary key. Right now it generates this SQL: http://pastie.org/private/an5kiotkqgatl4tre3x4q I'd just like to set this up as properly as possible from the start, so maybe you have suggestions on what I could improve? Thanks! Michiel -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Handling big Python objects
I would recommend just storing them on disk and let the OS VMM deal with caching for speed. If you are not constrained for space I would recommend not zlib-ing it either. AM On 12/3/14 1:18 PM, Andrea Gavana wrote: Hello list, sorry for the possibly noob question, I've googled around without much success looking for an answer. Basically, I am given a series of this huge Python class (a Simulation object), which contains an enormous amount of information - when I cPickle it (with highest protocol), it can result to files 200-250 MB in size, although rarely it can get up to 2 GB. I am looking for intelligent ways to store these objects into a database. I have to say that I don't have that much control on this Simulation class, so I can't change its internal structure - I'm just looking for a better alternative to what I am doing. So, what I am doing now is basically storing this huge object as a string. I have these two methods: import cPickle import zlib import base64 def serialize(my_simulation): my_db_object = base64.b64encode(zlib.compress(cPickle.dumps(obj, cPickle.HIGHEST_PROTOCOL))) return my_db_object def deserialize(my_db_object): my_simulation = cPickle.loads(zlib.decompress(base64.b64decode(my_db_object))) return simulation I can use them to store/retrieve this big Python classes to/from the database, but I feel it's not a particularly effective way to handle this problem. I've tried to get my head around BLOBs and LargeBinary stuff, but I'm not sure I'm on the right path here. I appreciate any suggestion on how to approach the problem, to make the storing/retrieving of these objects a bit less time/memory consuming (especially time). On a related note, I am now using MySQL as a backend - but I am open to suggestions about possible alternatives that may make this problem more tractable: I have some difficulties in installing other backends (like PostgreSQL or psycopg2), but I know I can use Access and Oracle backends. I know that Access is not particularly famous in the SQLAlchemy world. Of course, if any other backend has advantages over MySQL in dealing with this issue, I may try to convince IT to get it installed on our machines. All suggestions and comments are most welcome. Thank you in advance for your help. Andrea. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Handling big Python objects
On 12/3/14 2:23 PM, Andrea Gavana wrote: On Wednesday, December 3, 2014 10:42:27 PM UTC+1, Jonathan Vanasco wrote: On Wednesday, December 3, 2014 4:23:31 PM UTC-5, Ams Fwd wrote: I would recommend just storing them on disk and let the OS VMM deal with caching for speed. If you are not constrained for space I would recommend not zlib-ing it either. I'll second storing them to disk. Large object support in all the databases is a pain and not very optimal. Just pickle/unpickle a file and use the db to manage that file. Thanks to all of you who replied. A couple of issues that I'm sure I will encounter by letting the files on disk: 1. Other users can easily delete/overwrite/rename the files on disk, which is something we really, really do not want; If this is windows group policies are your friends :). If this is linux, permissions with a secondary service to access the files are a decent choice. 2. The whole point of a database was to have everything centralized in one place, not leaving the simulation files scattered around like a mess in the whole network drive; The last time I did it a post processing step in my data pipeline organized the files based on a multi-level folder structure based on the first x-characters of their sha1. 3. As an aside, not zlib-ing the files saves about 5 seconds/simulation (over a 20 seconds save) but increases the database size by 4 times. I'll have to check if this is OK. To use compression or not depends on your needs. If the difference in time consumed is so stark, I would highly recommend compression. HTH AM Thank you again for your interest. Andrea. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] hybrid_properties and literals
Hi.
I am having some trouble understanding how to use native python data
types with hybrid properties. I have the following model. I am using
flask-sqlalchemy however I run into the same issue in straight
sqlalchemy too.
class SystemModel(BaseModel):
__tablename__ = 'system'
class_number = DB.Column(DB.Integer, DB.ForeignKey(
ClassModel.get_fk('number')), primary_key=True)
name = DB.Column(DB.String, nullable=False)
_ports = DB.Column('ports', DB.String)
# ports is stringified [(port, proto, mapped_port), ... ]
@hybrid_property
def ports(self):
if self._ports:
return ast.literal_eval(self._ports) -- problem here
return None -- also here
@ports.setter
def ports(self, value):
self._ports = str(value)
I run into trouble when using this in a query:
results = SystemModel.query.with_entities(SystemModel.ports).filter(
SystemModel.ports != None).all()
So the problem of course is that self._ports is not really a string so
literal_eval fails. I think I can use sqlalchemy.literal here but am not
sure if that will help in this case or how exactly to use it.
I realize that the right way would be to have a separate ports table
with the system pk being the fk+pk and then using it and I will probably
go down that road, however I would still like to understand what exactly
is going on here.
I did read:
http://techspot.zzzeek.org/2011/10/21/hybrids-and-value-agnostic-types/
and can certainly go down that road too, however it seems a bit of
overkill for what I really want.
Any help would be greatly appreciated.
Thanks
AM
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] hybrid_properties and literals
On 06/19/2014 06:13 AM, Mike Bayer wrote:
On 6/19/14, 2:05 AM, AM wrote:
Hi.
I am having some trouble understanding how to use native python data
types with hybrid properties. I have the following model. I am using
flask-sqlalchemy however I run into the same issue in straight
sqlalchemy too.
class SystemModel(BaseModel):
__tablename__ = 'system'
class_number = DB.Column(DB.Integer, DB.ForeignKey(
ClassModel.get_fk('number')), primary_key=True)
name = DB.Column(DB.String, nullable=False)
_ports = DB.Column('ports', DB.String)
# ports is stringified [(port, proto, mapped_port), ... ]
@hybrid_property
def ports(self):
if self._ports:
return ast.literal_eval(self._ports) -- problem here
return None -- also here
@ports.setter
def ports(self, value):
self._ports = str(value)
I run into trouble when using this in a query:
results = SystemModel.query.with_entities(SystemModel.ports).filter(
SystemModel.ports != None).all()
So the problem of course is that self._ports is not really a string so
literal_eval fails. I think I can use sqlalchemy.literal here but am
not sure if that will help in this case or how exactly to use it.
I realize that the right way would be to have a separate ports table
with the system pk being the fk+pk and then using it and I will
probably go down that road, however I would still like to understand
what exactly is going on here.
I did read:
http://techspot.zzzeek.org/2011/10/21/hybrids-and-value-agnostic-types/ and
can certainly go down that road too, however it seems a bit of
overkill for what I really want.
Any help would be greatly appreciated.
what SQL would you like to see when you do your query? My vague
recollection is that ast.literal_eval() returns a Python code object?
That doesn't seem likely to be something you could run on the database
side unless you want to run Python functions in Postgresql (which would
be kind of nuts IMHO).
What I am storing is things like string versions of lists, tuples and
dicts, for e.g.:
str([1, 2, 3])
str({'a':1}
and so on. ast.literal_eval will only parse those and return those, it
does not evaluate expressions and statements so no real code at all.
I got around this issue by creating a PythonASTLiteralColumn based on
the example in the docs and that worked perfectly.
Having said that I would still like to understand how to approach a
situation where I want a hybrid property that is a normal python data
type, if that's possible at all?
Thanks.
AM
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] hybrid_properties and literals
On 06/19/2014 10:24 AM, Mike Bayer wrote:
On 6/19/14, 1:05 PM, AM wrote:
What I am storing is things like string versions of lists, tuples and
dicts, for e.g.:
str([1, 2, 3])
str({'a':1}
and so on. ast.literal_eval will only parse those and return those, it
does not evaluate expressions and statements so no real code at all.
I got around this issue by creating a PythonASTLiteralColumn based on
the example in the docs and that worked perfectly.
Having said that I would still like to understand how to approach a
situation where I want a hybrid property that is a normal python data
type, if that's possible at all?
SQLAlchemy is a bridge between Python and SQL but there is no magic in
creating the thing on each side of that bridge. If you want a SQL
query that interrogates a column of data in a way that is meaningful
regarding some kind of in-Python behavior, you need to decide how that
behavior will be expressed in your SQL query. The hybrid will work
fine at the instance level but if you want it to behave meaningfully in
a SQL query you'd first need to know what the SELECT statement you want
will actually look like in terms of raw SQL.
Ah ok, I see what you mean. In my particular case I really don't want
anything special. Basically if I have a table like I mentioned before:
class SystemModel(BaseModel):
__tablename__ = 'system'
class_number = DB.Column(DB.Integer, DB.ForeignKey(
ClassModel.get_fk('number')), primary_key=True)
name = DB.Column(DB.String, nullable=False)
_ports = DB.Column('ports', DB.String)
The only queries I am going to be running at the SQL level are of the form:
select _ports from system where _ports is not null;
select _ports from system where class_number = 1
update system set _ports=[(1, 2, 3), ...] where class_number = 1
inserts and deletes.
What I wanted at the python end was that if I ran this query
select _ports from system where class_number = 1
I would get
result.ports = [(1, 2, 3), ...] # python list
instead of
result.ports = [(1, 2, 3), ...] # python string
As I mentioned I can do that via the custom column type, but wanted to
find out if there was an easier way that I was missing.
Thanks for all your help.
AM
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] metadata.create_all with postgres 9.3.3 on AWS RDS
On 06/09/2014 03:30 PM, Michael Bayer wrote:
On Jun 9, 2014, at 4:32 PM, AM ams@gmail.com wrote:
Hi all.
In my app I have a bootstrap method that calls:
metadata.create_all(checkfirst)
against a postgres RDS instance.
The tables already exist however the query emitted seems to be:
CREATE TABLE user_roles (
user_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY(user_id) REFERENCES user (id),
FOREIGN KEY(role_id) REFERENCES role (id)
)
Per my understanding this query should not even be emitted or if it is there
should be a 'IF NOT EXISTS' somewhere in there.
I was wondering if someone could point out what I am doing wrong?
it doesn't use IF NOT EXISTS. it runs a query against pg_namespace. Turn on
echo=True on your engine and you'll see something like:
select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace
where n.nspname=current_schema() and relname=%(name)s
{'name': u'a'}
CREATE TABLE a (
id SERIAL NOT NULL,
PRIMARY KEY (id)
)
if this query isn't succeeding, perhaps you have a schema mismatch of some
kind. if the CREATE TABLE does succeed then the default schema is probably not
what you expect.
So I do get the pg_namespace query with one row in the result. What I am
a it confused about is why the CREATE is emitted since the table exists?
Thanks.
AM
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Advice for Method to consistently import XML into SQLAlchemy
On 05/14/2014 04:23 AM, Sayth Renshaw wrote: Hi Looking for some guidance and advice on using xml as an update source for my to be data web project. If I am consistently going to be updating data into the database from XML files what is a good method? Should I be creating a Sax parser as in this example http://iamtgc.com/importing-xml-into-a-database-with-python-and-sqlalchemy/ . To be clear the XML I am importing from is not basic, using from that example my xml would like more like. So i would need to filter the values out that I want which I have acheived using xmltodict. |!-- books.xml --| |||catalog||| |book| |isbn||=||1-880985-26-8||binding=paperback ebook=mobi altformat=pdf etc=ManyMore| |title||The Consumer/||title||| |author||M. Gira/||author||| |||/||book||| |book| |isbn||=||0-679775-43-9|||binding=paperback ebook=mobi altformat=pdf etc=ManyMore|| |title||The Wind-Up Bird Chronicle/||title||| |author||Haruki Murakami/||author||| |||/||book||| |||!-- imagine more entries here... --| |/||catalog||| Copies are available here http://old.racingnsw.com.au/Site/_content/racebooks/20140515GOSF0.xml Or should I be attempting to filter and convert the XML to json format and import into SQLAlchemy. Or other, searching around I cannot, strangely find one and only one way to do it. It seems to be a mish mosh of good luck, well from perspective of someone looking to implement this for the first time. Probably painfully obvious to those who have done it before. Anyway thank you for your time. Sayth -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. The last time I had to domething like this, I converted all xml attributes to tags using an XSLT sheet and then used lxml to populate a sqla object. You can also do it manually as in the link but use lxml etree which might make the code a bit easier to read. IMO sax is a bit more performant than etree but can get a bit confusing. HTH AM -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] a [probably] stupid query question
On 03/19/2014 02:07 PM, Richard Gerd Kuesters wrote: hi, i have a simple stupid query question, which i didn't found any examples. let's say i have table A and B, and would like to reproduce in SA the following SQL: SELECT A.*, B.* FROM A, B WHERE A.b_id = B.id i would probably need a new fuse, too ... best regards, richard. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#using-joins HTH AM -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] a [probably] stupid query question
On 03/19/2014 04:44 PM, Richard Gerd Kuesters wrote: thanks AM! i know that a join have the same effect (that's why i already told it's probably stupid question), but i was wondering if the select ... from tbl1, tbl2 is possible using SA. that's all :) cheers, richard. Em 2014-03-19 20:11, AM escreveu: On 03/19/2014 02:07 PM, Richard Gerd Kuesters wrote: hi, i have a simple stupid query question, which i didn't found any examples. let's say i have table A and B, and would like to reproduce in SA the following SQL: SELECT A.*, B.* FROM A, B WHERE A.b_id = B.id i would probably need a new fuse, too ... best regards, richard. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#using-joins HTH AM -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. Not at my desk so I cannot test this, but IIRC in the select you can specify both tables to get all the columns in all tables so something like: session.query(A, B).join(B)... This might not be exactly correct though and I would probably check the docs. IIRC the return value would be a list of tuples (rowA, rowB) unless you do a fetchone. HTH AM -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Database locker error with sqlite in multi-process environment
On 12/19/2013 01:39 PM, Michael Bayer wrote: SQLite doesn’t work great for concurrent access. Or is the database file just stuck in a locked state? try copying it to a different file. On Dec 19, 2013, at 12:51 AM, AM ams@gmail.com wrote: PS: The only thing I can think of that is not quite usual is that I have a save method on my models and that calls flush() on the session object. and the session is created using: DB = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) Thanks. AM On 12/18/2013 09:35 PM, AM wrote: Hi All. I have a pyramid app, that uses sqlite via sqlalchemy when run in development. The configuration used: sqlalchemy.url = sqlite:///%(here)s/pyp.sqlite?pool_size=1 sqlalchemy.isolation_level = SERIALIZABLE The application initializes the engine using: settings = config.get_settings() engine = sa.engine_from_config(settings, 'sqlalchemy.') DB.configure(bind=engine) BASE.metadata.bind = engine Another part of the application that runs as a daemon also uses the same setup. This daemon always starts after the app. The daemon initializes the engine using the same piece of code. When the application tries to insert a record in the database I get the following error: OperationalError: (OperationalError) database is locked I am fairly certain I had this working previously quite some time back but for the life of me cannot remember how. Any help would be greatly appreciated. Thanks. AM -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. I have tried copying and moving the file around, yet still run into this. Oh well I guess its time to get postgres up and running. Thanks. AM -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Database locker error with sqlite in multi-process environment
Hi All. I have a pyramid app, that uses sqlite via sqlalchemy when run in development. The configuration used: sqlalchemy.url = sqlite:///%(here)s/pyp.sqlite?pool_size=1 sqlalchemy.isolation_level = SERIALIZABLE The application initializes the engine using: settings = config.get_settings() engine = sa.engine_from_config(settings, 'sqlalchemy.') DB.configure(bind=engine) BASE.metadata.bind = engine Another part of the application that runs as a daemon also uses the same setup. This daemon always starts after the app. The daemon initializes the engine using the same piece of code. When the application tries to insert a record in the database I get the following error: OperationalError: (OperationalError) database is locked I am fairly certain I had this working previously quite some time back but for the life of me cannot remember how. Any help would be greatly appreciated. Thanks. AM -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Database locker error with sqlite in multi-process environment
PS: The only thing I can think of that is not quite usual is that I have a save method on my models and that calls flush() on the session object. and the session is created using: DB = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) Thanks. AM On 12/18/2013 09:35 PM, AM wrote: Hi All. I have a pyramid app, that uses sqlite via sqlalchemy when run in development. The configuration used: sqlalchemy.url = sqlite:///%(here)s/pyp.sqlite?pool_size=1 sqlalchemy.isolation_level = SERIALIZABLE The application initializes the engine using: settings = config.get_settings() engine = sa.engine_from_config(settings, 'sqlalchemy.') DB.configure(bind=engine) BASE.metadata.bind = engine Another part of the application that runs as a daemon also uses the same setup. This daemon always starts after the app. The daemon initializes the engine using the same piece of code. When the application tries to insert a record in the database I get the following error: OperationalError: (OperationalError) database is locked I am fairly certain I had this working previously quite some time back but for the life of me cannot remember how. Any help would be greatly appreciated. Thanks. AM -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Re: keyword search
I think trying to solve this using the ORM will be slow no matter what you do. Moreover I feel that it's not really an ORMs concern to provide you the ability to do this over multiple columns. I would recommend using something like whoosh to create an index over the columns you want and look up the index. Take a look at whoosh-alchemy https://pypi.python.org/pypi/WhooshAlchemy which might make things easier. HTH AM On 12/17/2013 02:03 AM, lars van gemerden wrote: PS: a search over all fields would be great too! On Tuesday, December 17, 2013 11:01:51 AM UTC+1, lars van gemerden wrote: Hi all, I want to implement a keyword search over multiple fields and/or even related tables. Tables/orm classes are not designed by me but by users of my software, so i will probably let them indicate whether a field/relationship should be included (or I'll base it on type, e.g. String and Text fields only). I was thinking of using a hybrid property, but they seem a bit to magical for this purpose (might be really slow). Is there a best practice/standard way to do this in SQLA? Cheers, Lars -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [wishlist] ORM/engine Feature Request - comment()
On 11/18/2013 04:06 PM, Jonathan Vanasco wrote: Something that I realized would be very useful, is to enable comments on query compilation. That would make it SO much easier to understand logs. Yes this sounds slightly silly. Yes I am 100% serious. I've been working all day on optimizing a web page. It originally had 200 queries, I'm down to 60, I think we can get to 15. Combing through 200 queries and associating the SQL to code is a pain. Consider this statement / syntax : results = dbSession.query( Foo ).filter_by( Foo.id.in_( 1,2,3) ).comment(initial foo select).all() generating this sql ( which would propagate into all the various logs ) SELECT * FROM foo WHERE foo.id IN (1,2,3); -- initial foo select I think this would be ridiculously useful for troubleshooting. It could even (potentially) be controlled by a setting in the engine or connect string. Show comments on DEV, not PRODUCTION; turn on Production comments only during a test timeframe, etc. Most Sql dialects support comments in queries. The following works in postgres, mysql, sqlite: c-style SELECT foo FROM bar /* comment here , can span multiple lines */; until EOL - postgres and mysql SELECT foo FROM bar ; -- comment After combing through many different docs, I also found this little (outdated) chart -- http://3rdstage.blogspot.com/2009/04/sql-comments-syntax-for-oracle-db2.html -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. Wouldn't selecting the sql logger and logging the comment provide you the same information? AM -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Code organization with declarative models
On 08/12/2013 02:50 PM, George Sakkis wrote: Hello everyone, this is more of a code architecture and design question but I'm wondering what the best practices are regarding declarative models. On the one extreme, models are pretty barebone, with little more than the columns, relationships and possibly a few declared attributes and properties (python and/or hybrid). On the other extreme, models are much heavier, encapsulating pretty much the business logic of the application in methods (and classmethods or staticmethods for querying the database). Between these two extremes are models with some common or important business logic kept inside the class and the rest defined elsewhere (where this elsewhere might be the controllers or the resource layer or the Data Access Objects or whatever the nomenclature happens to be). So where should the line be drawn between what belongs in a declarative class and what not? For example, I suspect that models should be completely decoupled from the Session; any Session-related code (for querying/updating/deleting objects) should not live inside the declarative class. Still I haven't seen this being mentioned explicitly in the docs and can't put my finger on it. Any insight would be appreciated. Thanks, George -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. My 2cents: Usually I keep the models absolutely barebones (as you suggested). As far as I am concerned they are not aware of any 'business' logic and only deal with CRUD operations and perhaps complex CRUD if the need arises. I usually have another abstraction which is generally a factory that delegates db tasks to the model and deals with all business logicky stuff, sessions, complex relationships etc. (a bit like Django's managers but not quite as coupled to the model i.e. the model does not know about it). This has worked quite well for me in the past and although it is a bit more work is quite flexible. HTH AM -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Adding properties of wrapped object to association proxy
Exactly what I was looking for! Thanks a ton, I did not realize I could
actually do that and went spelunking down hybrid properties.
Thanks again.
AM
On 08/04/2013 07:34 AM, Michael Bayer wrote:
you want to flatten the scalar attributes to combine the namespace of OrgModel and
OrgMemberModel into one.there's no magic there you just have to proxy them all,
here's name:
class OrgMemberModel(Base):
__tablename__ = 'org_member'
user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
org_id = Column(Integer, ForeignKey('org.id'), primary_key=True)
active = Column(Boolean, nullable=False, default=True)
role = Column(String(64), nullable=False, default='member')
user = relationship('UserModel',
backref=backref('org_membership', cascade='all,
delete-orphan'))
org = relationship('OrgModel')
name = association_proxy(org, name)
u1 = UserModel()
u1.org_membership.append(OrgMemberModel(role='r1', org=OrgModel(name='o1')))
assert u1.org_membership[0].role == 'r1'
assert u1.org_membership[0].name == 'o1'
u1.org_membership[0].name = 'o2'
assert u1.org_membership[0].name == 'o2'
On Aug 4, 2013, at 4:03 AM, AM ams@gmail.com wrote:
On 08/03/2013 09:38 AM, Michael Bayer wrote:
On Aug 2, 2013, at 2:32 PM, Ams Fwd ams@gmail.com wrote:
Hi All.
Is there any way to transparently add the properties of the associated object
to the association proxy?
For e.g. if I have the following setup
class UserModel(Base):
name = sa.Column(sa.String(512))
orgs = association_proxy('org_member', 'org')
class OrgModel(Base):
name = sa.Column(sa.String(256), unique=True)
class OrgMemberModel(Base):
__tablename__ = 'org_member'
user_id = sa.Column(sa.Integer, sa.ForeignKey('user.id'),
primary_key=True)
org_id = sa.Column(sa.Integer, sa.ForeignKey('org.id'),
primary_key=True)
active = sa.Column(sa.Boolean, nullable=False, default=True)
role = sa.Column(sa.String(64), nullable=False, default='member')
user = relationship('UserModel', backref=backref('org_membership',
cascade='all, delete-orphan'))
org = relationship('OrgModel')
What I would like to be able to do is access the properties of the OrgModel
OrgMemberModel via the org_membership[i] object.
Currently I have to do the following:
user.org_membership[0].organization.name
user.org_membership[0].role
Ideally I would like to be able to do:
user.org_membership[0].name
user.org_membership[0].role
Any help would be greatly appreciated.
on UserModel you'd have an association proxy from org_membership to org. Not sure
what org_member is.
Hi Michael.
I think that is what I have. org_member is the many-to-many relation table
between User and Org. org_membership in this case is the backref from the
relation into the user table.
My problem is that the backref (as expected) provides the relation property and
another property that points to the org at the other end of the relation
between user and org.
What I want is a technique that does not require that extra level of
indirection:
user-org_membership-org-org_property
and instead simply be able to do:
user-org_membership-org_property.
Thanks.
AM
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Adding properties of wrapped object to association proxy
On 08/03/2013 09:38 AM, Michael Bayer wrote:
On Aug 2, 2013, at 2:32 PM, Ams Fwd ams@gmail.com wrote:
Hi All.
Is there any way to transparently add the properties of the associated object
to the association proxy?
For e.g. if I have the following setup
class UserModel(Base):
name = sa.Column(sa.String(512))
orgs = association_proxy('org_member', 'org')
class OrgModel(Base):
name = sa.Column(sa.String(256), unique=True)
class OrgMemberModel(Base):
__tablename__ = 'org_member'
user_id = sa.Column(sa.Integer, sa.ForeignKey('user.id'),
primary_key=True)
org_id = sa.Column(sa.Integer, sa.ForeignKey('org.id'),
primary_key=True)
active = sa.Column(sa.Boolean, nullable=False, default=True)
role = sa.Column(sa.String(64), nullable=False, default='member')
user = relationship('UserModel', backref=backref('org_membership',
cascade='all, delete-orphan'))
org = relationship('OrgModel')
What I would like to be able to do is access the properties of the OrgModel
OrgMemberModel via the org_membership[i] object.
Currently I have to do the following:
user.org_membership[0].organization.name
user.org_membership[0].role
Ideally I would like to be able to do:
user.org_membership[0].name
user.org_membership[0].role
Any help would be greatly appreciated.
on UserModel you'd have an association proxy from org_membership to org. Not sure
what org_member is.
Hi Michael.
I think that is what I have. org_member is the many-to-many relation
table between User and Org. org_membership in this case is the backref
from the relation into the user table.
My problem is that the backref (as expected) provides the relation
property and another property that points to the org at the other end of
the relation between user and org.
What I want is a technique that does not require that extra level of
indirection:
user-org_membership-org-org_property
and instead simply be able to do:
user-org_membership-org_property.
Thanks.
AM
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Bulk creation of columns
On Sep 21, 2011, at 6:30 AM, pravin battula wrote:
Hi,
How can i create columns in bulk?
I tried as below but doesn't work.
migrate_engine = create_engine('mysql://root:root@localhost/
payroll', echo=False)
metadata = MetaData(bind = migrate_engine)
metadata.reflect(bind = migrate_engine, schema = 'payroll')
tableObj = metadata.tables.get('test.salary')
colList =
[Column('description',String(100)),Column('information',String(50))]
tableObj.append_column(colList)
tableObj.create_column(colList)
You need to create new Column objects for each table because the table objects
take ownership of the passed in columns.
Cheers,
M
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
