[sqlalchemy] Access for DB session in Celery
Hi, I'm using some DB operation inside my Celery tasks and i dont know which is good approach for accessing DB session in each task. Now i store in global celery config on start a DB session: scoped_session(sessionmaker(bind=engine)) And each task takes that class from config and makes an instance. But maybe better would be to store directly in celery config a DB session instance ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/1jgi_sGQNcIJ. 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] Using Oracle Indexes...
Hi, Hi i use sqlalchemy to map an existing oracle table to my class via the declarative syntax. One of the columns is indexed. Do i understand it correctly to only specify the index in my Column? class Example(...) mnr = Column(MNR, String(12), primary_key=True, index=True) And how can i check if the index is used in a select? Do i see it with echo=True? Thanks in advance... Christian -- 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: Lock table, do things to table, unlock table: Best way?
Unique constraints have worked well. Thanks! On May 29, 1:44 pm, Michael Bayer mike...@zzzcomputing.com wrote: yup On May 29, 2012, at 1:01 PM, Jeff wrote: Thanks Michael, Just to make clear what exactly begin_nested() is contributing: Normal case: session.rollback() goes back to the last session.commit() session.begin_nested() case: session.rollback() goes back to the last session.begin_nested() or session.commit(), whichever occurred last. Correct? On May 28, 11:54 am, Michael Bayer mike...@zzzcomputing.com wrote: An option to add along to the unique constraint, if you expect to get collisions often, is to use a SAVEPOINT so that a process can roll back partially if this particular INSERT fails, then use the row. The Session offers SAVEPOINT via begin_nested(): session.begin_nested() try: session.add(thing_that_may_exist_already) session.commit() # flushes, and commits only the savepoint except exc.IntegrityError: session.rollback() thing_that_may_exist_already = session.query(Thing).filter_by(criteiron).one() the difference between using locks to prevent concurrent dupes versus using constraints and expecting dupes to fail is known as pessimistic versus optimistic locking. On May 28, 2012, at 10:38 AM, Jeff wrote: The unique constraint sounds like a workable solution! I'll implement that with a try/except and report back if that was effective. Thanks! On May 28, 5:43 am, Simon King si...@simonking.org.uk wrote: On Sun, May 27, 2012 at 6:18 PM, Jeff jeffalst...@gmail.com wrote: Thanks, I have indeed spent a lot of time looking at SELECT FOR UPDATE, but as far as I can tell that locks rows that have been selected. That is not helpful in this use case, in which the issue is rows not existing, and then later existing. Am I misunderstanding? On May 27, 11:48 am, A.M. age...@themactionfaction.com wrote: On May 27, 2012, at 1:07 AM, Jeff wrote: I have multiple processes accessing a table. All of these processes want to read a set of rows from the table, and if the rows are not present they will make a calculation and insert the rows themselves. The issue comes where process A does a query to see if the target set of rows is present in the table, and they're not, and then another starts calculating. While it's calculating, process B inserts the rows. Then process A inserts the rows, and now we have two copies of these sets of rows. Bad. You should look at SELECT FOR UPDATE. http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html?highlight=lockmo... Cheers, M Could you put unique constraints on the table so that the second process will get an error when it tries to insert the duplicate rows? It won't prevent you from performing the calculations twice, but at least you won't get the duplicates. Another option would be to write some sort of pending marker into the table, so that subsequent processes know that the result is already being calculated. Simon -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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] Another Parent instance is not bound to a Session; lazy load...
Hello all, my curious situation is the following. A very simplified version of the code is: for data in res: obj = MyObject() ---here I fill the obj, aventually doing some query (create session, get, close) to SA sess = createSession() sess.add(obj) sess.commit() sess.close() -- do some other query (create session, get, close) and eventually persist the changes (create session, merge/add, commit close) now... on the first loop it works fine, then I receive the Parent instance warning me that a an obj inner attribute, say a contact, is not bound so cannot load contact.phone attribute. BTW I receive such message when I commit() but I am able to make it appear looking in the second loop, during a debugging session, at the specific obj attribute. What I would like to implement is to restrict the session (in a more general sense SA activity even if I know that actually SA inject some instrumentation in obj at the creation time) inside a unique class, say with some static methods (the code above involving the session is in a separate class) in order to centralize the operation toward the DB, and doing this it should act in a stateless way (createSession, do stuff, commit/rollback, close), unfortunately seems that I am missing something. Thanks Maurizio -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/J4THN4X57aUJ. 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] mssql and specifying a schema name?
Hello,
I'm trying to autolaod my table image but it keeps complaining that the
table doesn't exists.
I've enabled the echo = true and I see that you specify in the query:
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT],
[COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image',
'MyDatabase'
)
But my schema name is dbo?
Where do I specify that?
On create_engine?
or?
import sqlalchemy
e = sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
from sqlalchemy.orm import mapper
#---
image_table = sqlalchemy.Table('image', metadata, autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
Thanks,
Lucas
--
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.
Re: [sqlalchemy] mssql and specifying a schema name?
the default schema name is determined by:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = (SELECT user_name())
AND type = 'S'
for some reason on your system it's coming up as MyDatabase. You'd want to
fix that so that it comes up with dbo.
On May 30, 2012, at 1:52 PM, Lukasz Szybalski wrote:
Hello,
I'm trying to autolaod my table image but it keeps complaining that the
table doesn't exists.
I've enabled the echo = true and I see that you specify in the query:
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT],
[COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image',
'MyDatabase'
)
But my schema name is dbo?
Where do I specify that?
On create_engine?
or?
import sqlalchemy
e = sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
from sqlalchemy.orm import mapper
#---
image_table = sqlalchemy.Table('image', metadata, autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
Thanks,
Lucas
--
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.
--
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.
Re: [sqlalchemy] Using Oracle Indexes...
On May 30, 2012, at 7:38 AM, Christian Klinger wrote: Hi, Hi i use sqlalchemy to map an existing oracle table to my class via the declarative syntax. One of the columns is indexed. Do i understand it correctly to only specify the index in my Column? class Example(...) mnr = Column(MNR, String(12), primary_key=True, index=True) And how can i check if the index is used in a select? Do i see it with echo=True? an index in SQL is a construct that is specified using a CREATE statement, and only once for that database, in the same way the CREATE statement is used for a table. If you are using your declarative models to emit the CREATE statements to your Oracle database, with index=True you will also see a corresponding CREATE INDEX in the DDL that is emitted. If you are *not* emitting CREATE statements, and your Oracle database already has an existing schema, then the index=True flag has no effect. It is only meaningful when CREATE statements are emitted. To see if your SELECT is using particular indexes, you'd want to view the query plan, which you get by using EXPLAIN. See http://docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm for background on this specific to Oracle. -- 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: mssql and specifying a schema name?
On May 30, 1:03 pm, Michael Bayer mike...@zzzcomputing.com wrote:
the default schema name is determined by:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = (SELECT user_name())
AND type = 'S'
for some reason on your system it's coming up as MyDatabase. You'd want to
fix that so that it comes up with dbo.
I can't.
The database was design for a specific application and I cannot make
changes to it structure, other then my records.
Is there a _somevalue that stores this in my engine, so that I can
overwrite it? or is it automatically get pulled every time I do a
autoload=True?
Thanks,
Lucas
On May 30, 2012, at 1:52 PM, Lukasz Szybalski wrote:
Hello,
I'm trying to autolaod my table image but it keeps complaining that the
table doesn't exists.
I've enabled the echo = true and I see that you specify in the query:
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT],
[COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image',
'MyDatabase'
)
But my schema name is dbo?
Where do I specify that?
On create_engine?
or?
import sqlalchemy
e = sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
from sqlalchemy.orm import mapper
#---
image_table = sqlalchemy.Table('image', metadata, autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
Thanks,
Lucas
--
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
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to 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: mssql and specifying a schema name?
On May 30, 1:03 pm, Michael Bayer mike...@zzzcomputing.com wrote:
the default schema name is determined by:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = (SELECT user_name())
AND type = 'S'
for some reason on your system it's coming up as MyDatabase. You'd want to
fix that so that it comes up with dbo.
Actually,
Let me try a different username.
That works. The username I was using was admin...so I logged in as
me and it has schema name of dbo.
Thanks,
Lucas
On May 30, 2012, at 1:52 PM, Lukasz Szybalski wrote:
Hello,
I'm trying to autolaod my table image but it keeps complaining that the
table doesn't exists.
I've enabled the echo = true and I see that you specify in the query:
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT],
[COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image',
'MyDatabase'
)
But my schema name is dbo?
Where do I specify that?
On create_engine?
or?
import sqlalchemy
e = sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
from sqlalchemy.orm import mapper
#---
image_table = sqlalchemy.Table('image', metadata, autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
Thanks,
Lucas
--
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
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to 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.
Re: [sqlalchemy] Re: mssql and specifying a schema name?
On May 30, 2012, at 3:24 PM, Lukasz Szybalski wrote:
On May 30, 1:03 pm, Michael Bayer mike...@zzzcomputing.com wrote:
the default schema name is determined by:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = (SELECT user_name())
AND type = 'S'
for some reason on your system it's coming up as MyDatabase. You'd want
to fix that so that it comes up with dbo.
I can't.
The database was design for a specific application and I cannot make
changes to it structure, other then my records.
Is there a _somevalue that stores this in my engine, so that I can
overwrite it? or is it automatically get pulled every time I do a
autoload=True?
then you'd need to put schema='dbo' into each of your Table objects. the
autoload should work fine with that.
Thanks,
Lucas
On May 30, 2012, at 1:52 PM, Lukasz Szybalski wrote:
Hello,
I'm trying to autolaod my table image but it keeps complaining that the
table doesn't exists.
I've enabled the echo = true and I see that you specify in the query:
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT],
[COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image',
'MyDatabase'
)
But my schema name is dbo?
Where do I specify that?
On create_engine?
or?
import sqlalchemy
e = sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
from sqlalchemy.orm import mapper
#---
image_table = sqlalchemy.Table('image', metadata, autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
Thanks,
Lucas
--
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
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to 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.
--
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: mssql and specifying a schema name?
On May 30, 2:35 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On May 30, 2012, at 3:24 PM, Lukasz Szybalski wrote:
On May 30, 1:03 pm, Michael Bayer mike...@zzzcomputing.com wrote:
the default schema name is determined by:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = (SELECT user_name())
AND type = 'S'
for some reason on your system it's coming up as MyDatabase. You'd want
to fix that so that it comes up with dbo.
I can't.
The database was design for a specific application and I cannot make
changes to it structure, other then my records.
Is there a _somevalue that stores this in my engine, so that I can
overwrite it? or is it automatically get pulled every time I do a
autoload=True?
then you'd need to put schema='dbo' into each of your Table objects. the
autoload should work fine with that.
Where would I put the schema='db'?
somewhere inside this statement ?
image_table = sqlalchemy.Table('image', metadata, autoload=True) ?
or?
[SQL Server]Login failed. The login is from an untrusted domain and
cannot be used with Windows authentication.
Thanks,
Lucas
On May 30, 2012, at 1:52 PM, Lukasz Szybalski wrote:
Hello,
I'm trying to autolaod my table image but it keeps complaining that the
table doesn't exists.
I've enabled the echo = true and I see that you specify in the query:
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT],
[COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
ORDER BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
ORDER BY [COLUMNS_1].[ORDINAL_POSITION]
2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image',
'MyDatabase'
)
But my schema name is dbo?
Where do I specify that?
On create_engine?
or?
import sqlalchemy
e = sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
from sqlalchemy.orm import mapper
#---
image_table = sqlalchemy.Table('image', metadata, autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
--
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.
Re: [sqlalchemy] Re: mssql and specifying a schema name?
On May 30, 2012, at 3:56 PM, Lukasz Szybalski wrote:
On May 30, 2:35 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On May 30, 2012, at 3:24 PM, Lukasz Szybalski wrote:
On May 30, 1:03 pm, Michael Bayer mike...@zzzcomputing.com wrote:
the default schema name is determined by:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = (SELECT user_name())
AND type = 'S'
for some reason on your system it's coming up as MyDatabase. You'd want
to fix that so that it comes up with dbo.
I can't.
The database was design for a specific application and I cannot make
changes to it structure, other then my records.
Is there a _somevalue that stores this in my engine, so that I can
overwrite it? or is it automatically get pulled every time I do a
autoload=True?
then you'd need to put schema='dbo' into each of your Table objects. the
autoload should work fine with that.
Where would I put the schema='db'?
somewhere inside this statement ?
image_table = sqlalchemy.Table('image', metadata, autoload=True) ?
or?
[SQL Server]Login failed. The login is from an untrusted domain and
cannot be used with Windows authentication.
if you're already using a different username that solves the problem, stick
with that.
otherwise yes, Table('image', metadata, schema='dbo', autoload=True)
using schema can make things more complicated so don't use it if you don't
have to.
--
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.
Re: [sqlalchemy] Another Parent instance is not bound to a Session; lazy load...
well yes, the way you're doing this is entirely the opposite of how the ORM is designed to function.The Session has been developed in order to work in an intelligent manner with full graphs of interrelated objects, all coordinated under the umbrella of a transaction which applies atomicity and isolation to the full series of operations.An individual object loaded from a Session is in fact an extension of that Session's state, which is in turn an extension of the state of the current transaction within the database, all kept in sync mostly automatically. When you continuously break this connection between a whole series of interconnected objects you'll run into not just lots of problems keeping objects associated with continuously new transactions, but also horrendous performance from due to the excessive number of commits and re-acquisition of new connections/transactions. In general, the Session is typically used in a bounding sense, its lifespan beginning before you work with any objects, and ending only after you've completed the work with those objects.I frequently use the metaphor here that the Session is the table setting and the objects are the meal. Docs on why commit forces a reload by default and all the rest of this is explained pretty well at http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html . On May 30, 2012, at 4:21 AM, Maurizio Nagni wrote: Hello all, my curious situation is the following. A very simplified version of the code is: for data in res: obj = MyObject() ---here I fill the obj, aventually doing some query (create session, get, close) to SA sess = createSession() sess.add(obj) sess.commit() sess.close() -- do some other query (create session, get, close) and eventually persist the changes (create session, merge/add, commit close) now... on the first loop it works fine, then I receive the Parent instance warning me that a an obj inner attribute, say a contact, is not bound so cannot load contact.phone attribute. BTW I receive such message when I commit() but I am able to make it appear looking in the second loop, during a debugging session, at the specific obj attribute. What I would like to implement is to restrict the session (in a more general sense SA activity even if I know that actually SA inject some instrumentation in obj at the creation time) inside a unique class, say with some static methods (the code above involving the session is in a separate class) in order to centralize the operation toward the DB, and doing this it should act in a stateless way (createSession, do stuff, commit/rollback, close), unfortunately seems that I am missing something. Thanks Maurizio -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/J4THN4X57aUJ. 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. -- 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.
Re: [sqlalchemy] Another Parent instance is not bound to a Session; lazy load...
On Wed, May 30, 2012 at 8:39 PM, Michael Bayer mike...@zzzcomputing.com wrote: well yes, the way you're doing this is entirely the opposite of how the ORM is designed to function. The Session has been developed in order to work in an intelligent manner with full graphs of interrelated objects, all coordinated under the umbrella of a transaction which applies atomicity and isolation to the full series of operations. An individual object loaded from a Session is in fact an extension of that Session's state, which is in turn an extension of the state of the current transaction within the database, all kept in sync mostly automatically. When you continuously break this connection between a whole series of interconnected objects you'll run into not just lots of problems keeping objects associated with continuously new transactions, but also horrendous performance from due to the excessive number of commits and re-acquisition of new connections/transactions. In general, the Session is typically used in a bounding sense, its lifespan beginning before you work with any objects, and ending only after you've completed the work with those objects. I frequently use the metaphor here that the Session is the table setting and the objects are the meal. Thing is, in order to work with a large volume of objects, you're forced to do this, otherwise the session can grow uncontrollably. When you separate the operation to work in batches, you almost always have some objects that have a lifespan larger than a single batch, and then a single session. Another case in which an object's lifespan can exceed the session's, is when you want to implement caching with objects of your data model - cached values will have come from other sessions than the current one, and things get horribly messy. And, finally, the long transaction pattern, which luckily is a lot easier to solve. If the OP is using that pattern, it's just a matter of reattaching detached objects to the session. -- 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] Can't make an association table use InnoDB
Having difficulty creating a database that includes the following
plumbing:
class Base(object):
id = Column(Integer, primary_key=True)
__table_args__ = {'mysql_engine': 'InnoDB'}
Base = declarative_base(cls=Base)
class Event(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Base.metadata,
Column('avalanche_id', Integer, ForeignKey('Avalanche.id')),
Column('event_id', Integer, ForeignKey('Event.id')),
mysql_engine='InnoDB')
class Avalanche(Base):
Doing Base.metadata.create_all(engine) yields:
OperationalError: (OperationalError) (1005, Can't create table
'alstottj.Avalanche_Event_Association' (errno: 150)) '\nCREATE TABLE
`Avalanche_Event_Association` (\n\tavalanche_id INTEGER, \n\tevent_id
INTEGER, \n\tFOREIGN KEY(avalanche_id) REFERENCES `Avalanche` (id), \n
\tFOREIGN KEY(event_id) REFERENCES `Event` (id)\n)ENGINE=InnoDB\n
\n' ()
Commenting out the line mysql_engine='InnoDB' removes the error and
the tables are all created, but the association table is now MyISAM.
I have some feelings on what could be causing the error, but they all
seem improbable. Thoughts?
--
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.
Re: [sqlalchemy] Another Parent instance is not bound to a Session; lazy load...
On May 30, 2012, at 8:53 PM, Claudio Freire wrote: Thing is, in order to work with a large volume of objects, you're forced to do this, otherwise the session can grow uncontrollably. flush periodically, and don't maintain references to things you're done with. The Session does not strongly reference objects that have no pending changes, and they'll be garbage collected. When you separate the operation to work in batches, you almost always have some objects that have a lifespan larger than a single batch, and then a single session. Working in batches is fine. You only need a single Session for all those batches, and a single transaction.If you want several transactions, also fine, call commit() periodically. In none of these cases does the Session need to be closed, and all objects worked with thus far which are still referenced in memory can remain attached to that Session, and you wont have any detachment errors. The problems you're having are from unnecessary detachment of objects, from calling Session.close() and continuing to work with objects that have lost their owning Session, within the context of a new Session they have no association with. Another case in which an object's lifespan can exceed the session's, is when you want to implement caching with objects of your data model - cached values will have come from other sessions than the current one, and things get horribly messy. There are documented patterns for caching - see the example in examples/beaker_caching in the distro. This pattern is designed to cleanly handle the pattern of detached objects becoming re-associated with a particular session at once. The pattern is along the lines of, session is created to work with a field of objects, a set of objects is retrieved from the cache, then re-associated with the cache en-masse using the merge_result() method illustrated in the 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.
Re: [sqlalchemy] Can't make an association table use InnoDB
This might be because the tables you're trying to reference are themselves not
InnoDB. Try running DESCRIBE on the referenced tables at the MySQL console to
help confirm this, as well as the same CREATE TABLE statement below.
On May 30, 2012, at 11:31 PM, Jeff wrote:
Having difficulty creating a database that includes the following
plumbing:
class Base(object):
id = Column(Integer, primary_key=True)
__table_args__ = {'mysql_engine': 'InnoDB'}
Base = declarative_base(cls=Base)
class Event(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Base.metadata,
Column('avalanche_id', Integer, ForeignKey('Avalanche.id')),
Column('event_id', Integer, ForeignKey('Event.id')),
mysql_engine='InnoDB')
class Avalanche(Base):
Doing Base.metadata.create_all(engine) yields:
OperationalError: (OperationalError) (1005, Can't create table
'alstottj.Avalanche_Event_Association' (errno: 150)) '\nCREATE TABLE
`Avalanche_Event_Association` (\n\tavalanche_id INTEGER, \n\tevent_id
INTEGER, \n\tFOREIGN KEY(avalanche_id) REFERENCES `Avalanche` (id), \n
\tFOREIGN KEY(event_id) REFERENCES `Event` (id)\n)ENGINE=InnoDB\n
\n' ()
Commenting out the line mysql_engine='InnoDB' removes the error and
the tables are all created, but the association table is now MyISAM.
I have some feelings on what could be causing the error, but they all
seem improbable. Thoughts?
--
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.
--
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.
