[sqlalchemy] Re: Sequence start value not working with PostgreSQL
Thanks Michael.
On 12/05/2009, at 12:34 AM, Michael Bayer wrote:
>
> the start functionality is unimplemented at the moment. instead,
> issue:
>
>
> t = Table("mytable", )
>
> DDL("CREATE SEQUENCE ").execute_at('before-create', t)
>
>
> Chris Miles wrote:
>>
>> I need to create an explicit Sequence with a specified start value.
>> Looks simple, I tried Sequence('test_seq', start=5000) however the
>> actual sequence created is left with the default start value. The
>> "start" parameter appears to have no effect.
>>
>> This is using PostgreSQL 8.2.4 and SQLAlchemy 0.5.3.
>>
>> Test code below demonstrates the problem.
>>
>> Am I misunderstanding the start parameter or is this a bug?
>>
>>
>> import sqlalchemy as sa
>>
>> engine = sa.create_engine('postgres://localhost/test1', echo=True)
>> meta = sa.MetaData()
>> meta.bind = engine
>>
>> test_seq = sa.Sequence('test_seq', start=5000, metadata=meta)
>> test_seq.create()
>>
>> nextval = engine.execute(test_seq)
>>
>> assert nextval == 5000, nextval
>>
>>
>> Cheers,
>> Chris Miles
>>>
>>
>
>
> >
--~--~-~--~~~---~--~~
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] Sequence start value not working with PostgreSQL
I need to create an explicit Sequence with a specified start value.
Looks simple, I tried Sequence('test_seq', start=5000) however the
actual sequence created is left with the default start value. The
"start" parameter appears to have no effect.
This is using PostgreSQL 8.2.4 and SQLAlchemy 0.5.3.
Test code below demonstrates the problem.
Am I misunderstanding the start parameter or is this a bug?
import sqlalchemy as sa
engine = sa.create_engine('postgres://localhost/test1', echo=True)
meta = sa.MetaData()
meta.bind = engine
test_seq = sa.Sequence('test_seq', start=5000, metadata=meta)
test_seq.create()
nextval = engine.execute(test_seq)
assert nextval == 5000, nextval
Cheers,
Chris Miles
--~--~-~--~~~---~--~~
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: app memory leak - execute() with SA 0.5.2, PG 8.3, Psycopg2
On Feb 22, 6:08 am, Allen Bierbaum wrote: > The python process. The number of objects seems to remain fairly > controlled. But the amount of resident memory used by the python > process does not decrease. I had expected that by calling > gc.collect(2) python would reclaim any objects that could be freed and > free all memory associated with them, thus decreasing the consumed > memory. Maybe this is an invalid assumption. Do you know any way to > ask python to shrink it's process size (ie. clear unused memory that > has been freed but evidently not given back to the OS)? Python 2.5 and later will free up garbage collected memory, handing it back to the system. Previous versions of Python would never free up memory (hence never shrink in size). Are you using Python 2.4? Cheers, Chris Miles --~--~-~--~~~---~--~~ 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: Create tables within a transaction
Manual testing with sqlite appears to show that CREATE TABLE is transactional and can be rolled back. Consider:: $ sqlite3 test2.db Loading resources from /Users/chris/.sqliterc SQLite version 3.4.0 Enter ".help" for instructions sqlite> BEGIN; sqlite> CREATE TABLE test1 (foo INTEGER); sqlite> CREATE TABLE test2 (foo INTEGER); sqlite> COMMIT; sqlite> .tables test1 test2 sqlite> ^D $ rm test2.db $ sqlite3 test2.db Loading resources from /Users/chris/.sqliterc SQLite version 3.4.0 Enter ".help" for instructions sqlite> BEGIN; sqlite> CREATE TABLE test1 (foo INTEGER); sqlite> CREATE TABLE test2 (foo INTEGER); sqlite> ROLLBACK; sqlite> .tables sqlite> Perhaps the behaviour I see through SA is a side effect of pysqlite? Cheers, Chris Miles On Feb 6, 2:36 pm, Michael Bayer wrote: > sqlite doesn't include CREATE TABLE statements within the scope of a > transaction. I think that's a relatively rare behavior only seen in > Postgres, in fact - I dont think Oracle or MySQL have that behavior, > for example. --~--~-~--~~~---~--~~ 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: Create tables within a transaction
Ok. I'll do some testing against other engines when I get a chance.
Thanks for helping.
Cheers
Chris Miles
On Feb 6, 2:36 pm, Michael Bayer wrote:
> sqlite doesn't include CREATE TABLE statements within the scope of a
> transaction. I think that's a relatively rare behavior only seen in
> Postgres, in fact - I dont think Oracle or MySQL have that behavior,
> for example.
>
> On Feb 5, 2009, at 10:01 PM, Chris Miles wrote:
>
>
>
> > That did the trick, thanks.
>
> > Well, actually, it did the trick for PostgreSQL but sqlite isn't
> > rolling back. The SA logs show the same commands are being sent to
> > both. Here's an example:
>
> > $ rm test1.sqlite
> > $ python sa_create_table_transaction_test.py
> > 2009-02-06 13:39:29,006 INFO sqlalchemy.engine.base.Engine.0x..d0
> > BEGIN
> > 2009-02-06 13:39:29,007 INFO sqlalchemy.engine.base.Engine.0x..d0
> > CREATE TABLE table1 (
> > col1 INTEGER
> > )
>
> > 2009-02-06 13:39:29,007 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
> > 2009-02-06 13:39:29,021 INFO sqlalchemy.engine.base.Engine.0x..d0
> > CREATE TABLE table2 (
> > col1 INTEGER
> > )
>
> > 2009-02-06 13:39:29,021 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
> > 2009-02-06 13:39:29,024 INFO sqlalchemy.engine.base.Engine.0x..d0
> > CREATE TABLE table2 (
> > col1 INTEGER
> > )
>
> > 2009-02-06 13:39:29,024 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
> > 2009-02-06 13:39:29,027 INFO sqlalchemy.engine.base.Engine.0x..d0
> > ROLLBACK
> > $ sqlite3 test1.sqlite
> > Loading resources from /Users/chris/.sqliterc
> > SQLite version 3.4.0
> > Enter ".help" for instructions
> > sqlite> .tables
> > table1 table2
> > sqlite>
>
> > And the updated test script:
>
> > #
> > import sqlalchemy as sa
>
> > engine = sa.create_engine('sqlite:///test1.sqlite')
> > #engine = sa.create_engine('postgres://localhost/test1')
> > engine.echo = True
> > metadata = sa.MetaData()
>
> > table1 = sa.Table("table1", metadata,
> > sa.Column('col1', sa.types.Integer,),
> > )
> > table2 = sa.Table("table2", metadata,
> > sa.Column('col1', sa.types.Integer,),
> > )
> > table3 = sa.Table("table3", metadata,
> > sa.Column('col1', sa.types.Integer,),
> > )
>
> > metadata.bind = engine
> > connection = engine.connect()
> > trans = connection.begin()
> > try:
> > table1.create(bind=connection)
> > table2.create(bind=connection)
> > table2.create(bind=connection)
> > table3.create(bind=connection)
> > trans.commit()
> > except:
> > trans.rollback()
> > #
>
> > Cheers,
> > Chris
>
> > On Feb 6, 2:08 am, Michael Bayer wrote:
> >> create() and create_all() take a "bind" argument which can be an
> >> engine or connection. you want the connection in this case.
>
> >> On Feb 5, 2009, at 5:27 AM, Chris Miles wrote:
> >>> I can't find away to link table.create() with the existing
> >>> transaction.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Create tables within a transaction
That did the trick, thanks.
Well, actually, it did the trick for PostgreSQL but sqlite isn't
rolling back. The SA logs show the same commands are being sent to
both. Here's an example:
$ rm test1.sqlite
$ python sa_create_table_transaction_test.py
2009-02-06 13:39:29,006 INFO sqlalchemy.engine.base.Engine.0x..d0
BEGIN
2009-02-06 13:39:29,007 INFO sqlalchemy.engine.base.Engine.0x..d0
CREATE TABLE table1 (
col1 INTEGER
)
2009-02-06 13:39:29,007 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
2009-02-06 13:39:29,021 INFO sqlalchemy.engine.base.Engine.0x..d0
CREATE TABLE table2 (
col1 INTEGER
)
2009-02-06 13:39:29,021 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
2009-02-06 13:39:29,024 INFO sqlalchemy.engine.base.Engine.0x..d0
CREATE TABLE table2 (
col1 INTEGER
)
2009-02-06 13:39:29,024 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
2009-02-06 13:39:29,027 INFO sqlalchemy.engine.base.Engine.0x..d0
ROLLBACK
$ sqlite3 test1.sqlite
Loading resources from /Users/chris/.sqliterc
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .tables
table1 table2
sqlite>
And the updated test script:
#
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///test1.sqlite')
#engine = sa.create_engine('postgres://localhost/test1')
engine.echo = True
metadata = sa.MetaData()
table1 = sa.Table("table1", metadata,
sa.Column('col1', sa.types.Integer,),
)
table2 = sa.Table("table2", metadata,
sa.Column('col1', sa.types.Integer,),
)
table3 = sa.Table("table3", metadata,
sa.Column('col1', sa.types.Integer,),
)
metadata.bind = engine
connection = engine.connect()
trans = connection.begin()
try:
table1.create(bind=connection)
table2.create(bind=connection)
table2.create(bind=connection)
table3.create(bind=connection)
trans.commit()
except:
trans.rollback()
#
Cheers,
Chris
On Feb 6, 2:08 am, Michael Bayer wrote:
> create() and create_all() take a "bind" argument which can be an
> engine or connection. you want the connection in this case.
>
> On Feb 5, 2009, at 5:27 AM, Chris Miles wrote:
> > I can't find away to link table.create() with the existing
> > transaction.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Create tables within a transaction
I notice that a table create (and drop/etc) is always followed by an
implicit commit. Is it possible to suppress the commit or force SA to
create multiple tables in one transaction so that if any fail they can
all be rolled back?
Here's some code to demonstrate what I want. In this example, the
table creation fails half way through, but leaves 2 tables created. I
want the CREATEs rolled back if any of them fail.
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///test1.sqlite')
engine.echo = True
metadata = sa.MetaData()
table1 = sa.Table("table1", metadata,
sa.Column('col1', sa.types.Integer,),
)
table2 = sa.Table("table2", metadata,
sa.Column('col1', sa.types.Integer,),
)
table3 = sa.Table("table3", metadata,
sa.Column('col1', sa.types.Integer,),
)
metadata.bind = engine
connection = engine.connect()
trans = connection.begin()
try:
table1.create()
table2.create()
table2.create()
table3.create()
trans.commit()
except:
trans.rollback()
I can't find away to link table.create() with the existing
transaction.
Note: I don't want to just use create_all(). This is part of an in-
house schema version control system.
Note 2: If I create tables from SQL (sending "CREATE TABLE ..."
strings) within the transaction then I get the desired behaviour.
However, I'd prefer to use table.create() for convenience (i.e. across
multiple engines).
Cheers,
Chris Miles
--~--~-~--~~~---~--~~
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: Postgres - Backup - Restore
On Nov 10, 4:57 am, "Petr Kobalíček" <[EMAIL PROTECTED]> wrote: > I have postgres related problem. I'm normally developing with sqlite, > but we are using postgres on the server. The problem is that > sqlalchemy probably remembers primary keys and after database restore > it will start in all tables from 1. If you use the PostgreSQL tools pg_dump and pg_restore they should maintain the sequences properly for you when copying databases between servers. Cheers, Chris Miles --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
