[sqlalchemy] Interface to (very old) Sybaase?
Heyho! Has anybody worked with a Sybase Anywhere (ASA 9 -- yes, very old ...) database? I may need to build a simple CRUD (actually onnly R and U ;-) frontend to some legacy application. (I probably will give TurbeGears a try for this.) I do have a JDBC driver, and I *think* ODBC should work (when I tried it some time ago), but I haven't worked (much) with either. (I think I remember having seen a jdbc bridge for either Perl or Python, but I'm not sure anywhere and at least I can't find Debian packages right now.) Thanks in advance -- vbi -- Je n'ai pas souvent assisté à des course de spermatozoïdes, mais j'ai donné beaucoup de départs. -+- Olivier de Kersauson -+- signature.asc Description: This is a digitally signed message part.
Re: [sqlalchemy] Multi-column primary key with autoincrement?
Heyho! [multi-column primary key where one column is autoincrement int] On Wednesday 16 December 2009 05.29:54 Daniel Falk wrote: The true problem here is with sqlite, which tries to make a smart choice about whether to autoincrement or not. And it gets it wrong. SQLAlchemy is correct to not specify the id field in the INSERT statement. That's the cue to the db that it needs to supply that value on its own. Hmm. Closer inspection shows that * sqlite doesn't even support the scenario I want (autoincrement on one column with multi column primary key) and * sqlalchemy doesn't notice this and just creates a two column primary key without autoincrement at all. Is there any hope that sqlalchemy will start to simulate the feature on sqlite? (I'm using the python-pysqlite2 2.5.5-3 / libsqlite3-0 3.6.20-1 / python-sqlalchemy 0.5.6-1 packages from Debian squeeze, btw) I've opened #1642 now. PostgreSQL handles this in just the way I was expecting (no surprise since my expectation on autoincrement columns is derived from the way pg builds its serial data type :-), so I'll have to test if sqlalchemy will do the right thing here. Then I can at least use pg (I was planning to do so in production anyway; sqlite is convenient for development though.) cheers -- vbi -- featured product: GNU Privacy Guard - http://gnupg.org signature.asc Description: This is a digitally signed message part.
Re: [sqlalchemy] Multi-column primary key with autoincrement?
Heyho! On Wednesday 16 December 2009 16:36:10 Michael Bayer wrote: You need to either use the default keyword and specify a function or SQL expression that will generate new identifiers, or just set up the PK attributes on your new objects before adding them to the session. ... or just switch to pg for testing. I don't care to support other db anyway. Ok, thanks for clearing this up. cheers -- vbi -- If we can capitalize on something that did or did not happen in 1947 then it can help the entire state. -- Rep. Dan Foley on inventing the Extraterrestrial Culture Day signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Multi-column primary key with autoincrement?
Heyho! My small blog-style web site should support article versioning, so: class Entry(DeclarativeBase): id = Column(Integer, autoincrement=True, primary_key=True) version = Column(Integer, primary_key=True, default=0) ... and more stuff (content, author, ...) it seems autoincrement is not supported in this case. (I'm working in a TurboGears 2 environment with SQLite; final deployment will probably be with PostgreSQL) At least, when trying to populate a db from TurboGear's pasteer set-up, I get an IntegrityError (id may not be NULL) and see that id is not set in the INSERT statement. Just removing the version column from the Entry class suffices to let it work as before. Additionally: to keep the queries simple and fast, I'll introduce a current flag which should be set to True for the latest version of any id. Are there hooks I can use to manage this? (something like: execute UPDATE ... set current = False where version myversion whenver an Entry is being added to the db with a version 0) And a third qestion: what is the easiest way to create a new version? copy my 'Entry' object element by element to a new instance, or does sqla provide a cloning mechanism? thanks a lot! cheers -- vbi -- SCO's lawsuit is a lost cause. The implications for Linux users are rather like the implications for passengers on an ocean liner of a seagull diving into the water nearby. -- Thomas Carey, Bromberg Sunstein, LLP, attorney signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: executemany + postgresql
Heyho! On Friday 06 November 2009 02.46:11 Jon Nelson wrote: ... was performing an individual INSERT for every single row. Don't know sqlalchemy good enough, but for big bulk imports on the SQL side, shouldn't COPY be used? Which is as far as I know pg-specific / non-SQL standard. cheers -- vbi -- Lo-lan-do モインさん? nobse Lo-lan-do: Gesundheit. -- #debian-devel signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Portable Enum Columns
On Tuesday 06 October 2009 14.45:33 Yannick Gingras wrote: [...] Is there another way to do it? Something that would be portable and to both MySQL and Postgres would be great. Since both pg and mysql hava a native enum type, it's only a matter of writing the appropriate code in the SQL dialects. This came up just recently, search the list archive. I can't remember if the answer last time was it would be quite easy to do or somebody is working on it, though. cheers -- vbi -- featured product: PostgreSQL - http://postgresql.org signature.asc Description: This is a digitally signed message part.
[sqlalchemy] vertical partitioning
Heyho! Is there a tutorial on vertical partitioning? I have a table Entry and a table EntryFlags (1:1 relation from EntryFlags to Entry). The idea is that while there is a large number of Entry rows only a small number has flags set (and thus needs an entry in EntryFlags; note that they don't need to be booleans, despite the name). So having a separate table for the EntryFlags keeps the rows of Entry smaller and should also speed up if I select by certain flags. How do I represent this in sqlalchemy? Obviously I can trivially do the relation stuff to get entry.flags.myflag, but nicer would be having the columns from the EntryFlags table appear in Entry as if they were inline, returning a default value where an EntryFlags column doesn't exist. (Bonus for removing the EntryFlags row if all values are back to default ;-) Thanks in advance. -- vbi -- The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offence. -- E. Dijkstra, 1975 signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: vertical partitioning
On Wednesday 30 September 2009 21.58:55 Kevin Horn wrote: I have a table Entry and a table EntryFlags (1:1 relation from EntryFlags to Entry). The idea is that while there is a large number of Entry rows only a small number has flags set (and thus needs an entry in EntryFlags; note that they don't need to be booleans, despite the name). So having a separate table for the EntryFlags keeps the rows of Entry smaller and should also speed up if I select by certain flags. How do I represent this in sqlalchemy? [...] the columns from the EntryFlags table appear in Entry as if they were inline, returning a default value where an EntryFlags column doesn't exist. (Bonus for removing the EntryFlags row if all values are back to default ;-) Check out the Customizing Column Properties in the Mapper Configuation docs: http://www.sqlalchemy.org/docs/05/mappers.html#customizing-column-propert ies If I understand correctly, this should do what you want. (Disclaimer: haven't done it myself, YMMV) Hmm. Mapping an outer join to my actual model class would be part of it. Not sure how to easily assign default values for the part of the row not backed by a real row on the EntryFlags side (they shouldn't just be NULL; default values on the EntryFlags table wouldn't be of much use since they'd be applied only when the row would be generated and not for access to the outer join, afaict) (And as an aside: this has become a a bit an academic question for now, I've thought about more about what I'm trying to do and redesigned the schema.) cheers -- vbi Kevin Horn --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- -- Do you understand now why we attacked Iraq? Because war is good for the economy, which means war is good for America. Also, since God is on America's side, anyone who opposes war is a godless un-American Communist. -- excerpt from one of those 'joke' mails floating around. signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Database with audit trail table
On Saturday 22 August 2009 01.08:05 David Bolen wrote: Adrian von Bidder avbid...@fortytwo.ch writes: Ideas comments? For what it's worth, I'd think that the best sort of audit would be something done in the database itself, since it would audit any changes whether done through any interface. Yes and no... I see your point (and since I know pg better than sqla/python I'd probably even be quicker doing it in SQL), but the application has more knowledge about what's going on. My audit table should not just version the db content, but I plan to record additional stuff that comes from the application (like: who authorized the change? etc.) which is not readily available at the db level. (And then there's the fact that I'm writing this toy project to learn about sqlalchemy, so going ahead and writing an SQL audit trail framework misses this goal completely :-) cheers -- vbi (Off to play around with SeesionExtension some more ...) -- featured link: http://www.pool.ntp.org signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Database with audit trail table
Heyho! Instead of creating changeby / changed fields on all my tables, I'm planning to write some model classes where changes would be recorded in a separate audit trail table (the obvious benefit beyond not requiring the additional fields is that I can preserve the history as far back as I want) So, like a table audit ( timestamp, dbchange, info, ...) where dbchange would be some kind of machine interpretable description and info would be a textual description of the event (created user blah, removed product x from order y, ...) Where do I start to get this automated? Session has the information about what needs to be done (insert, delete, update), and the model classes know what should be recorded into the audit records. So where do I hook into so that I automatically can add these audit records at flush time? (this would need to be after flush, actually, since autogenerated values need to be available. But as long as I get into the same transaction I'm just fine.) Perhaps this has even been done before? (Obviously, this kind of audit trail would be lost upon rollback. For a first variant this is fine with me; later I' guess an option to use a separate session for the audit stuff would be nice, but that'd need to log which audit records were part of a transaction that was later rolled back...) Ideas comments? cheers -- vbi -- Bill Dickey is learning me his experience. -- Yogi Berra in his rookie season. signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Two enhancement proposals for orm.Query
On Wednesday 17 June 2009 19.08:10 klaus wrote: ... whether a query will yield any result ... The best approximation seems to be query.first() is not None which can select a lot of columns. I often see query.count() 0 which can become quite expensive on a DBMS like PostgreSQL. Just a side note that pg tends to compute first() efficiently if (and I assume sa does this) the implementation uses LIMIT to tell the db that really only the first row is interesting. I don't know about other databases. cheers -- vbi -- this email is protected by a digital signature: http://fortytwo.ch/gpg signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: union with two different orders
On Saturday 06 June 2009 17.39:20 naktinis wrote: I think this was not the case, since I didn't expect the merged result to be ordered. To be more precise, the query looks like: q1 = Thing.query().filter(...).order_by(Thing.a.desc()).limit(1) q2 = Thing.query().filter(...).order_by(Thing.a.asc()).limit(1) q = q1.union(q2).order_by(Thing.id).all() The q1 returns first filtered element with largest 'a' column, q2 - first with smallest 'a'. So, I guess my question is still valid. You didn't mention limit in your first post, so I misunderstood what you were trying to do, sorry. Yes, as Michael said, subqueries are the way to go. I'm quite new to sa, so I can't help you there. cheers -- vbi -- featured link: http://www.pool.ntp.org signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: union with two different orders
On Saturday 06 June 2009 14.18:33 naktinis wrote: I want to use union on two queries, which have different order: q1 = Thing.query().order_by(Thing.a) q2 = Thing.query().order_by(Thing.b) q = q1.union(q2).all() SQL doesn't work as you think it does here. A UNION does not concatenate the results of the two queries, but is allowed to return the result in any order. ORDER BY can *then* be applied to the end result of your union. So even if you use subqueries, the order by in the subqueries might just be ignored. This is to allow the SQL query planner to be clever while building the union (perhaps a large union over two queries over the same table: if both queries require a table scan over the large table, the planner might decide to build the union by scanning the table only once while running both queries in parallel, so the table is loaded from disk once insead of twice. The UNION would then contain the resulting rows in more or less random order.) But I digress. What you want to do is something like: SELECT 1 as COL1, ... FROM ... UNION SELECT 2 as COL1, ... FROM ... ORDER BY COL1, ... cheers -- vbi But after this query I get MySQL error message Incorrect usage of UNION and ORDER BY. I guess that this could be because having SELECT ... UNION SELECT ... ORDER BY B, it is not clear whether the second subquery or both queries should be ordered using B criteria. I think this can be solved by adding brackets to each of the subquery: (SELECT ...) UNION (SELECT ...). Is there any way to create this query using SQLAlchemy ORM? I am using SQLAlchemy 0.5.4. -- Vertrauen ist gut. Anwalt ist saugeil. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: querying into objects from a large flat table or have multiple processed tables mapped to objects?
[web logs - db] On Tuesday 26 May 2009 00.27:03 Michael Bayer wrote: the best thing to do would be to experiment with some various schemas and see what works best Also, it's extremely important to keep in mind that SQL databases can only work well with big tables if you create the right indices. What kind of index to create depends on the database you'll use and on which queries you'll run. (And: don't use a small example data set to decide which indices to build. A database often will change its query plan, sometimes drastically, depending on how much data is in a table.) So: create your schema, fill it with a few million log entries and then look at which queries might need which indices. In the case of web logs, you'll probably want an index on every base field (tinestamp, user, IP, URL string), but depending on your exact queries, combined indices on multiple column, or indices on functions of fields (like, perhaps, an index on the ending of the url to quickly filter jpg/png requests vs. html/php requests vs. requests on directories, ending by '/') might speed up your queries dramatically. cheers -- vbi -- Even though I use zsh, holding down the TAB key just doesn’t write my code for me, so I have to use a text editor. -- Scott James Remnant signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Q: fetch value of autoincrement column
On Friday 22 May 2009 23.00:05 Werner F. Bruhin wrote: What do you want to do with the autoincrement column? Often these are used for primary keys, which in turn get used as foreign keys. I want to use the id as filename; the table will cache some info that comes from the file. Using it as a filename directly has several advantages: * I don't have to care about a unique filename, since the db already takes care of that. * I don't have to create an additional column with the filename. Obviously, I can just first create the file with a temp filename, but I'd like to avoid this additional step if it's possible. Less code == fewer bugs. And I hoped that using the id from the beginning would be easily possible. Right now, I'm undecided if I should just drop portability and fetch the next value from the mytable_id_seq that postgres has generated for me with the autoincrement columns (from what I understand, even if I declare the column autoincrement, if I set the id manually it will Just Work(tm) like it should or if I should go the temporary filename route. I'm leaning towards the former because I probably will drop portability at some point in any case, since PostgreSQL just has too many cool features waiting to be used :-) (And no, I'm not going to use blobs and store the file as a large object in the db. Relational databases are not made for this, and besides it makes the db harder to maintain because db dumps get huge or the blobs have to be backed up separately, and debugging is harder because I can't use shell tools to get at the file data.) (So you see, I'm not trying to build foreign keys by hand or any such thing. I actually know relational databases quite well, it's just my knowledge of SQLAlchemy and Python which is lacking.) thanks -- vbi -- Alle Wirksamkeit ist stärker am Mittelpunkt als gegen die Peripherie zu. Raum zwischen Mars und Jupiter. -- Goethe, Maximen und Reflektionen, Nr. 1336 signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Small note on reading SA docs
On Friday 22 May 2009 01.59:13 Michael Bayer wrote: otherwise if you have any advice on how to get 0.4/0.3 delisted from such a prominent place on Google, that would be appreciated. Since removing them entirely is an option for you, perhaps just completely remove them from search engines via robots.txt? (And have a page linking to and describing the 0.3/0.4 docs in a way so that people searching for them explicitly still find them.) cheers -- vbi -- The most interesting [DNS weirdness] is that when I visit the Asus website two Asus IPs (one in the US, one in Taiwan) will query my nameserver for the . record for an entire week. -- Koos van den Hout signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Q: fetch value of autoincrement column
On Friday 22 May 2009 08.43:09 Alexandre Conrad wrote: Don't you want that non-null column to be a foreign key ? Would that make a difference? cheers -- vbi 2009/5/21 Adrian von Bidder avbid...@fortytwo.ch: Hi, Is it possible to fetch the values of an autoincrement field without flushing the object to the DB? (In postgres, I obviously can manually fetch nextval of the automatically generated sequence, but I lose the portability that way ...) Why? Because I need the id to generate data that will be filled into some (non- null) columns of the table row. So I can't flush since I'll get an IntegrityError about non-null columns, and I can't fill those columns without knowing the id that's going to be assigned. (Yes, I can use dummy values, then flush(), and then update the row before committing. But that's not exactly elegant...) cheers -- vbi -- Fnord. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- -- Der Glaube versetzt Berge, der Zweifel erklettert sie. -- Friedrich Georg Jünger signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Creating something with a unique, random key
On Friday 22 May 2009 12.01:05 Iwan wrote: Naïvely, I thought you'd create an X, flush it, and then catch any IntegrityError's thrown. [...] I know that PostgreSQL can't continue in a transaction after an error, you have to roll back the transaction. I don't know what the SQL standard says on this, but that's how postgres has always behaved. I guess you just have to query for your string to see if it's unique. Performance-wise it shouldn't make a difference, and in Python, I usually find a simple if even nicer than a try-except block. cheers -- vbi -- Jeder Mensch ist einzigartig. -- Benedetto Croce signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Q: fetch value of autoincrement column
On Friday 22 May 2009 13.58:34 Alexandre Conrad wrote: Hello Adrian, 2009/5/22 Adrian von Bidder avbid...@fortytwo.ch: On Friday 22 May 2009 08.43:09 Alexandre Conrad wrote: Don't you want that non-null column to be a foreign key ? Would that make a difference? That's what a foreign key is used for: http://en.wikipedia.org/wiki/Foreign_key Oh, thanks a lot. Not really helpful, though. I was quite simply asking if it's possibly to fetch the value of an autoincrement column (in a portable way, if possible) without causing a flush, because at the time when I want to use the value, the row is not complete yet. Oh, well... cheers -- vbi -- featured link: http://www.pool.ntp.org signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Q: fetch value of autoincrement column
Hi, Is it possible to fetch the values of an autoincrement field without flushing the object to the DB? (In postgres, I obviously can manually fetch nextval of the automatically generated sequence, but I lose the portability that way ...) Why? Because I need the id to generate data that will be filled into some (non- null) columns of the table row. So I can't flush since I'll get an IntegrityError about non-null columns, and I can't fill those columns without knowing the id that's going to be assigned. (Yes, I can use dummy values, then flush(), and then update the row before committing. But that's not exactly elegant...) cheers -- vbi -- Fnord. signature.asc Description: This is a digitally signed message part.
