[sqlalchemy] Re: how to do manualy incremented counter
Bobby Impollonia wrote: In mysql you can declare a integer column to be auto_increment and it will handle for you giving each row a different number. Other databases have similar mechanisms. It sounds like the invoice number is the primary key for the invoice table? If you have a integer primary key in sqlalchemy, it assumes that you want to make it auto increment so this should all happen automatically. I don't think this would work for invoices. What happens if a transaction is rolled back in MySQL an an auto-increment column is used? In PostgreSQL, using sequences, the number would be lost forever. Invoices usually require a continuous numbering and should have no holes in the sequence. What is the best solution is to have a table where you'll store the numbers, access it in a serializable way and then create and remove locks. This will grant that you don't have holes and that you don't have repeated numbers allocated to different invoices. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy 0.4.2b released
Jon Rosebaugh wrote: What are we supposed to do with Unicode? As far as I can tell, the Unicode type passes its defined length directly to the underlying string, so that a Unicode(30) column is turned into a VARCHAR(30) or the dialect equivalent. I may be able to determine that a particular column should hold up to 30 Unicode characters, for example, but if the default encoding of UTF-8 is used, each character will use anywhere from one to four byes, so that in the extreme case, it will be trying to stuff a 120-byte string into a VARCHAR(30)! The only way to be certain of the length is to use UTF-32, although in practice I doubt I will ever get astral plane characters in this app, so UTF-16 would probably be sufficient. But I _like_ UTF-8 and would prefer to use it. Perhaps the Unicode type should multiply its length by 4? What RDBMS you use? And what is the encoding of the specific database where you tested this? If you have a database with UTF-8 encoding then it will be expecting 30 Unicode characters coded with the UTF-8 encoding, no matter how many bytes it takes to store then. A VARCHAR(30) is not referring to 30 bytes, but to 30 characters. If you are mixing encodings then sooner or later you'll have big problems. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Save the default function into the database
Paul Johnston wrote: Hi, generate the default values (default = _some_python_function_). The problem is that such function is not saved in the database itsself. Ok, I wonder, why is this a problem for you? Usually people are happy with ORM-specific logic (such as this) just being stored in a python module and not the database. It is nice being able to default to some logic inside the database because if there's access to it using some other tool or even directly through SQL commands then the default would still be applied / applicable. One other possibility is to code the default inside a database trigger. But then you can't say that you have a NOT NULL column because if you omit the value then you'll get an SQL error... (While you can say, for example, INSERT INTO table (column) VALUES (DEFAULT) to get the default value.) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Save the default function into the database
Michael Bayer wrote: im fairly certain triggers used to generate INSERT values are compatible with NOT NULL columns. although in this particular thread I dont think I understand the issue exactly, it seemed like it was just asking for some Python function to be executed external to the database value itself; in which case the solution is to use a custom TypeEngine or TypeDecorator class. I had the impression that it was about database functions, not Python functions. Maybe I thought that because I was working with stored procedures by the time :-) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy 0.4 MERGED TO TRUNK
Marco Mariani wrote: Michael Bayer ha scritto: can i have an example http://trac.turbogears.org/browser/branches/1.0/turbogears/database.py?rev=2320 281 [run_with_transaction.when(_use_sa())] 282 def sa_rwt(func, *args, **kw): 283 log.debug(New SA transaction) 284 transaction = session.create_transaction() 285 try: [...] Thanks Marco! Since I only have limited access to email during the day, I couldn't reply before. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy 0.4 MERGED TO TRUNK
Michael Bayer wrote: a lot. no decorators for now. Not even the way we did on TurboGears that mimics the PEAK decorators? This way, people can use @decorator(param) on Python 2.4+ and can use [decorator(param)] on Python 2.3. At the same place (before the method / function declaration). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Orphaned parents..
Arnar Birgisson [EMAIL PROTECTED] writes: Hi all, Say I have this simple model: DayTimesheet date: a Date entries: a list of TimeEntry TimeEntry: day: a DayTimesheet in: a time (HH:MM) out: a time (HH:MM) DayTimesheet is the parent of TimeEntry in a one-to-many relationship. What would be the best way of ensuring the following: 1. Creating DayTimesheet on demand, i.e. DayTimesheet.get_by(date=nonexistentdate) would create a new instance if needed (inserted on the next flush()) You can try a get_by by that specific date and if there's an exception create the record on the except clause. 2. Automatically removing childless DayTimesheet instances - that is, when the last TimeEntry is deleted from a DayTimesheet, delete the DayTimesheet too? I had a similar problem... I solved it by scheduling a query like: DELETE FROM parent_table WHERE id NOT IN (SELECT parent_id FROM child_table GROUP BY parent_id); It runs periodically and cleans the database from stale records. -- Jorge Godoy [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Column to default to itself
Koen Bok [EMAIL PROTECTED] writes: I need to have a uninterrupted number sequence in my table for invoices. I was trying to do it like this, but I can't get it to work. Can anyone give me a hint? Let your database do the job. It is always aware of all connections made to it, their contexts, their priorities, what transaction isolation level is being used, etc. It will be better on this task. IF you insist on doing that at your code, make the column UNIQUE (or a PK...) and write something like this pseudocode: def save_data(): def insert_data(): try: unique_column_value = get_max_from_unique_column Class(unique_column_value + 1, 'other data') except YourDBExceptionForConstraintViolation: sleep(random.random()) insert_data() The 'sleep(random.random())' is there to avoid constant clashes and to be fair to all connections that are inserting data on your table. -- Jorge Godoy [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Legacy DB Migration Strategy - How to question?
BrendanC [EMAIL PROTECTED] writes: New user here trying to get started - I'd like to create an ORM map from an existing legacy (SQL Server) database - although I may convert this to Postgres or MySQL. So what is the least painful approach to creating the ORM (I plan to use TurboGears to create some Web Front End screens to this database.) The least painful approach depends on when. If you're talking about getting it running *now*, then making the ORM load the definitions from the database is the fastest way. If you're talking about migrating, then describing the database correctly in your model is the least painful approach because you'll have all the work *now*, but your application will be already changed and your model will be able to recreate the tables as needed, so you won't have to worry with it later. Most of the ORM docs I've read seem to require that the schema be defined in the ORM tool - however for a large legacy db that seems like a lot of work. I'd like to reverse engineer the db and create/ derive the maps/relationships from the SQL catalog - Is this possible? Am I missing something obvious here? More specifically, can I reverse migrate (??terminology??) from an existing production database and have all the foreign keys/database constraints/table relationships recognized and get models and controllers generated correctly? The term is the one you used on the first paragraph above: reverse engineer. Even though the ORM can reverse engineer your database some complex constructions might need some help from you. So you don't need to declare every table, but you might need to declare some of them. There's nothing that I know that will generate controllers from a database schema. So you'll have to write those by hand. There are CRUD tools, though, that might help a lot with basic functionality. I'd like to avoid any hand coding (e.g. tweaking FKs/relationships) as part of this initial migration. It all depends on your database schema, how complex it is, how normalized it is, etc. If there was a good normalization project, things get easier for reverse engineering tools (including SQL Alchemy). Any links to relevant ref materials/tutorials/etc would be appreciated here. There are docs on the website. They help a lot! -- Jorge Godoy [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to deal with non-ASCII(such as Chinese)table name?
Michael Bayer [EMAIL PROTECTED] writes:
non-ascii attribute names in your Python code ? or non-ascii column
names defined in your database table ? I didnt know the latter was
possible with most databases.
I know that (ARGH!) MySQL would allow the latter. I've seen column names with
diacriticals... I dunno if it would allow table names with those, though...
Also, with quotes you can get a lot of weird things (such as spaces in column
names).
This is for PostgreSQL:
# create table weird_test (column test serial, é um teste varchar);
NOTA: CREATE TABLE criará sequência implícita weird_test_column test_seq
para coluna serial weird_test.column test
CREATE TABLE
# \d weird_test
Tabela public.weird_test
Coluna| Tipo| Modificadores
-+---+
column test | integer | not null default nextval('weird_test_column
test_seq'::regclass)
é um teste | character varying |
# select * from weird_test ;
column test | é um teste
-+
(0 registros)
# insert into weird_test (é um teste) values ('testing 123');
INSERT 0 1
# select * from weird_test ;
column test | é um teste
-+-
1 | testing 123
(1 registro)
#
So, if you don't use quotes you're a lot more restricted than with quotes. I
dunno, though, who would be crazy to create such names.
--
Jorge Godoy [EMAIL PROTECTED]
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Splitting the model into multiple files
Hi! How's SQL Alchemy's support for splitting the model into multiple files? I've been having some difficulties with getters and SQL Object, so I'd like to know if a move to SQL Alchemy would help me with that or would let me with similar problems... TIA, -- Jorge Godoy [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: use_labels =30 vs MAX_LABEL_LENGTH
jose [EMAIL PROTECTED] writes: * PostgreSQL = 64 - 1 This can be changed in compilation time. -- Jorge Godoy [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: matz blogged us
Michael Bayer [EMAIL PROTECTED] writes: who can read japanese ??? http://www.rubyist.net/~matz/20070302.html#p04 we've been noticed http://translate.google.com/translate?u=http%3A%2F%2Fwww.rubyist.net%2F%7Ematz%2F20070302.html%23p04langpair=ja%7Cenhl=pt-BRsafe=offie=UTF-8oe=UTF-8prev=%2Flanguage_tools _ SQLAlchemy - The Database Toolkit for Python The library which synthesizes SQL from usual system. As for such technology you think that well enough it is convenient. -- Jorge Godoy [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Support for incomplete dates
Mel Collins [EMAIL PROTECTED] writes: As I haven't yet found any other way around this, my only recourse seems to be to split the date columns into three, which just feels wrong to me. Can anyone suggest a more elegant solution? I believe the details of your application should have this requirement... But besides MySQL no database that I know of accepts such absurdities with dates. When I design an application that has dates my specs clearly say that dates should be fully specified or should be NULL. For example, take a holiday table. When I designed mine the user could specify one specific full date (day, month and year) for a one time only holiday and he could set a boolean to True to specify that this date should be considered from there on making this a repeatable holiday. Taking this to your problem, maybe allowing the user to specify an unknown part and filling it with a value that will be ignored in your processing should solve the problem and make your application works with more stricter databases. How you'll design this depends on how many parts of the date may be unknown simultaneously: if only one then you could have a FK if more than one then you'd need a many-to-many relationship OR you could have more boolean fields... I'd think along these lines instead of using invalid dates. -- Jorge Godoy [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy lost connection?
iain duncan [EMAIL PROTECTED] writes: ridiculous but at least it works. I suppose in the case of a web app the cron job could even be on any old server using wget. I'd put the cron job on the same server as the app... :-) The load is ridiculous and there's no need to have an extra machine (be it old or not). Anyway, I think that this is a problem at lower layers: mysql db adaptor or the mysql server itself. This timeout should be configurable and one of the options should be disabling this timeout. -- Jorge Godoy [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Assign mapper many-to-many with extra columns
iain duncan [EMAIL PROTECTED] writes:
On Sat, 2007-03-02 at 18:44 -0500, Karl Guertin wrote:
On 2/3/07, iain duncan [EMAIL PROTECTED] wrote:
InvalidRequestError: Given column 'page_article.ordering', attached to
table 'page_article', failed to locate a corresponding column from table
'article_36c9'
I'd think that you're missing the .c. in the middle:
page_article.c.ordering
Nope, just tried again. The mappers are this:
assign_mapper( session.context, Page_Article, page_article_table )
assign_mapper( session.context, Article, article_table )
assign_mapper(session.context, Page, page_table, properties = {
articles: relation(Article, secondary=page_article_table,
order_by=page_article_table.c.ordering,
backref=pages, lazy=False)})
And the error message is still:
InvalidRequestError: Given column 'page_article.ordering', attached to
table 'page_article', failed to locate a corresponding column from table
'article_61f0'
A hint might be that the error message has no c in it. If you put it there,
then the error might be generated somewhere else or be dependent on something
that is not exactly where you're changing your code.
--
Jorge Godoy [EMAIL PROTECTED]
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
