[sqlalchemy] Re: Saving all transactions against a database (logging queries?)
Hello Barry,
I'm trying to get logging work...I put in my model.py the following code
and I expected to see my queries into /tmp/sa.log file, but...
what's wrong with it?
from turbogears import database
from sqlalchemy import Table, relation
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.assignmapper import assign_mapper
import logging
database.bind_meta_data()
session = database.session
engine = database.metadata.engine
context = session.context
engine.echo = False
logging.basicConfig(level=logging.DEBUG,
format='%(asctime)s %(levelname)-8s %(message)s',
datefmt='%a, %d %b %Y %H:%M:%S',
filename='/tmp/sa.log',
filemode='w')
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)ยท
Barry Warsaw wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Jan 5, 2007, at 12:01 PM, Michael Bayer wrote:
i havent gotten around to adding docs for logging to the main
docs...but its using Python's logging module now. turn off all the
"echo=True" flags and go straight to logging:
import logging
logging.basicConfig() # see python's logging docs for options
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
Sweet.
- -Barry
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (Darwin)
iQCVAwUBRZ6IsnEjvBPtnXfVAQLINgP+JUdkjNn+CtAA5Uao3Gs+n+4IuuMeiCoz
8J9yY9XlU74AW4N1ysf0wao0r2p871t2+sgIuFnBpRxO3DficR+iji3LJ24ZeWdH
KcG/uxUGk4uF7jfMeAdzVVRe/XqVRwI1uwMCN5p2rkXjdEokJp7G8uT872nC7EM5
3OzCHKORs2M=
=PBfM
-END PGP SIGNATURE-
>
--~--~-~--~~~---~--~~
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 defaults not being created with tables.
Michael, Thanks for the reply (and the help). --~--~-~--~~~---~--~~ 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: Saving all transactions against a database (logging queries?)
Ahhh, that explains it. In my example, I had already removed the crud at the beginning of each line. For anyone else looking to do something similar, you can configure postgres.conf to log the queries it recieves and I get the following in the log file : LOG: statement: UPDATE students SET custom_20004='17-JAN-85' WHERE students.student_id = 514 I'll still have to trim the beginning of each line, but it's usable. Thanks for the help. Brian On 1/5/07, Michael Bayer <[EMAIL PROTECTED]> wrote: every other line is a repr() of the bind parameter dictionary sent to the query, so you probably want to grab those separately (they are sent as distinct log lines - not sure why they are munged together in your example above). the query itself is using bind parameters in psycopg2's configured bind param format, "pyformat". thats what SA is sending to the database. > --~--~-~--~~~---~--~~ 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: Transactions
a connection to an in-memory sqlite database is unique to itself; theres no way to connect to it twice. when using SQLite, all connections within a particular thread are using the same "thread local" connection (i.e. it uses the SingletonThreadPool by default), so in your example above the two Connection objects reference the same underlying pysqlite connection instance. if you want to see it use the regular connection pool, send the sqlalchemy.pool.QueuePool class to the create_engine's "pool_class" argument. youll see it fails pretty badly with "memory:" (and as i recall doesnt work so great with file-based databases either but i havent tried in a long 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: Saving all transactions against a database (logging queries?)
every other line is a repr() of the bind parameter dictionary sent to the query, so you probably want to grab those separately (they are sent as distinct log lines - not sure why they are munged together in your example above). the query itself is using bind parameters in psycopg2's configured bind param format, "pyformat". thats what SA is sending to the database. --~--~-~--~~~---~--~~ 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: Saving all transactions against a database (logging queries?)
OK, I've got the logging working. The INFO level gives much more than
just the actual queries run against the db, but the bigger problem is
that the queries aren't given in valid SQL.
Example from the log:
UPDATE students SET custom_20004=%(custom_20004)s WHERE
students.student_id = %(students_student_id)s {'custom_20004':
"'17-JAN-85'", 'students_student_id': Decimal("514")}
Is there any quick way to get it to output valid SQL? I'm using
postgres, so I'm now looking at the possiblity it logging the queries
it receives instead.
Brian
On 1/5/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
> i havent gotten around to adding docs for logging to the main
> docs...but its using Python's logging module now. turn off all the
> "echo=True" flags and go straight to logging:
>
> import logging
> logging.basicConfig() # see python's logging docs for options
> logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
>
> the INFO level on 'sqlalchemy.engine' will give you all the queries,
> the DEBUG level the queries as well as the result sets. using
> "logging" you can format the log messages any way you want and pass
> your own buffers in...just read the docs at
> http://www.python.org/doc/2.4.2/lib/module-logging.html .
>
>
> > >
>
--~--~-~--~~~---~--~~
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: Transactions
You may want to turn on logging to see exactly what SQL is being issued:
try adding
import logging
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
to the top of your test script
On 1/5/07, Paul Johnston <[EMAIL PROTECTED]> wrote:
Hi,
The following program outputs 1; I thought it should output 0. Any
comments?
from sqlalchemy import *
db = create_engine("sqlite:///:memory:")
metadata = BoundMetaData(db)
users = Table('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', String))
users.create()
c1 = db.connect()
t1 = c1.begin()
c1.execute(users.insert(), user_id=1, user_name='user1')
c2 = db.connect()
t2 = c2.begin()
print len(c2.execute("select * from users").fetchall())
Regards,
Paul
>
--~--~-~--~~~---~--~~
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] Transactions
Hi,
The following program outputs 1; I thought it should output 0. Any comments?
from sqlalchemy import *
db = create_engine("sqlite:///:memory:")
metadata = BoundMetaData(db)
users = Table('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', String))
users.create()
c1 = db.connect()
t1 = c1.begin()
c1.execute(users.insert(), user_id=1, user_name='user1')
c2 = db.connect()
t2 = c2.begin()
print len(c2.execute("select * from users").fetchall())
Regards,
Paul
--~--~-~--~~~---~--~~
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 defaults not being created with tables.
yeah...if you want a schema level default, SA calls that a
"PassiveDefault":
users = Table('users', meta,
Column('user_id', Integer, primary_key=True),
Column('username', String(32), nullable=False),
Column('email_address', String(60), unique=True, nullable=False),
Column('password', String(20), nullable=False),
Column('created', DateTime,
PassiveDefault(func.current_timestamp()),
nullable=False)
)
--~--~-~--~~~---~--~~
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] Column defaults not being created with tables.
I'm a SQLAlchemy newbie and sure I'm doing something simply stupidly
wrong, but the column defaults are being ignored when I create a table.
Does anyone have any suggestions? Thank you!
Python 2.4.3
SQLAlchemy 0.3.3
MySQLdb 1.2.1
#
from sqlalchemy import *
db = create_engine('mysql://username:[EMAIL PROTECTED]/testdb')
meta = BoundMetaData(db)
users = Table('users', meta,
Column('user_id', Integer, primary_key=True),
Column('username', String(32), nullable=False),
Column('email_address', String(60), unique=True, nullable=False),
Column('password', String(20), nullable=False),
Column('created', DateTime, default=func.current_timestamp(),
nullable=False)
)
# default=func.now() is also ignored.
meta.engine.echo = True
meta.create_all()
#
2007-01-05 12:16:02,372 INFO sqlalchemy.engine.base.Engine.0x..10 show
table status like 'users'
2007-01-05 12:16:02,373 INFO sqlalchemy.engine.base.Engine.0x..10 None
2007-01-05 12:16:02,376 INFO sqlalchemy.engine.base.Engine.0x..10 show
table status like 'users'
2007-01-05 12:16:02,376 INFO sqlalchemy.engine.base.Engine.0x..10 None
2007-01-05 12:16:02,379 INFO sqlalchemy.engine.base.Engine.0x..10
CREATE TABLE users (
user_id INTEGER NOT NULL AUTO_INCREMENT,
username VARCHAR(32) NOT NULL,
email_address VARCHAR(60) NOT NULL,
password VARCHAR(20) NOT NULL,
created DATETIME NOT NULL,
PRIMARY KEY (user_id),
UNIQUE (email_address)
)
2007-01-05 12:16:02,379 INFO sqlalchemy.engine.base.Engine.0x..10 None
2007-01-05 12:16:02,394 INFO sqlalchemy.engine.base.Engine.0x..10 COMMIT
--~--~-~--~~~---~--~~
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] Problem with array fields in postgres
Sorry,
but I don't understand how create, insert or select data with the array
field in postgres.
if I try to do somethong like this
users = Table('prova', metadata,
Column('user_id', Integer, primary_key=True),
Column('name', String(40)),
Column('some_numbers', Integer[4]),
)
users.create()
python tell that colum some_numbers is an unsuscriptable object and
Integer[] is an invalid sintax.
I create directly the tables in postgres, than I can insert data
because
table.insert({'user_id':1, name':'Bill', 'color':'ARRAY{1, 3,
5}').execute() return key error: int[].
I read the documentation but I don't found the solution.
thanks for the help
luca
--~--~-~--~~~---~--~~
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] Using pdb with sqlalchemy
Hi all,
I'm a little confused this morning, and maybe that is the reason why I'm
missing the obvious: while I'm able to use the Python debugger when I
run the tests by adding --verbose to the test runner, I do not seem able
to do that in a standalone SA script.
I tried to put "echo=True" or "echo='debug'" on the metadata
constructor, and to explicitly setting the log level to debug on the
whole sqlalchemy loggers hierarchy, and to save and restore
sys.std{in,err,out}, but with no luck. All I get is a BdbQuit exception...
Any hint will be appreciated,
thanks in advance,
bye, lele.
--~--~-~--~~~---~--~~
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: Saving all transactions against a database (logging queries?)
Excellent. Thank you for the clarification.
Brian
On 1/5/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
i havent gotten around to adding docs for logging to the main
docs...but its using Python's logging module now. turn off all the
"echo=True" flags and go straight to logging:
import logging
logging.basicConfig() # see python's logging docs for options
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
the INFO level on 'sqlalchemy.engine' will give you all the queries,
the DEBUG level the queries as well as the result sets. using
"logging" you can format the log messages any way you want and pass
your own buffers in...just read the docs at
http://www.python.org/doc/2.4.2/lib/module-logging.html .
>
--~--~-~--~~~---~--~~
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: Saving all transactions against a database (logging queries?)
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Jan 5, 2007, at 12:01 PM, Michael Bayer wrote:
i havent gotten around to adding docs for logging to the main
docs...but its using Python's logging module now. turn off all the
"echo=True" flags and go straight to logging:
import logging
logging.basicConfig() # see python's logging docs for options
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
Sweet.
- -Barry
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (Darwin)
iQCVAwUBRZ6IsnEjvBPtnXfVAQLINgP+JUdkjNn+CtAA5Uao3Gs+n+4IuuMeiCoz
8J9yY9XlU74AW4N1ysf0wao0r2p871t2+sgIuFnBpRxO3DficR+iji3LJ24ZeWdH
KcG/uxUGk4uF7jfMeAdzVVRe/XqVRwI1uwMCN5p2rkXjdEokJp7G8uT872nC7EM5
3OzCHKORs2M=
=PBfM
-END PGP SIGNATURE-
--~--~-~--~~~---~--~~
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: Saving all transactions against a database (logging queries?)
i havent gotten around to adding docs for logging to the main
docs...but its using Python's logging module now. turn off all the
"echo=True" flags and go straight to logging:
import logging
logging.basicConfig() # see python's logging docs for options
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
the INFO level on 'sqlalchemy.engine' will give you all the queries,
the DEBUG level the queries as well as the result sets. using
"logging" you can format the log messages any way you want and pass
your own buffers in...just read the docs at
http://www.python.org/doc/2.4.2/lib/module-logging.html .
--~--~-~--~~~---~--~~
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: no error when using wrong 'attribute' name on an object
well, theres nothing that says the attribute youre sending in is
"wrong". classes in python dont have any notion of predeclared
attribute names. a mapped class can have any number of other
attributes which dont correspond to database-mapped attributes.
in your case, you would like to constrain the attributes on your class
to the set of those which have been explicitly set up in a mapping
relationship. youd implement your own constructor like this:
class MyBaseClass(ActiveMapper):
def __init__(self, **kwargs):
mapper = class_mapper(self.__class__)
for key, value in kwargs.items():
if key not in mapper.props:
raise AttributeError("non mapped attribute: '%s'" %
key)
setattr(self, key, value)
class Color(MyBaseClass):
# etc
while im not a big ActiveMapper user, id leave it up to the
ActiveMapper developers if they think this behavior should be built in
to ActiveMapper itself (i kind of dont think it should be).
--~--~-~--~~~---~--~~
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] Saving all transactions against a database (logging queries?)
Hello all, I've been using SA to convert data from a csv or another database into a new database (either empty or already being used). I do all my work on copies of the original data and then when I've confirmed that data is being moved accurately, I have to come up with a way to get the data in a form that can be imported into the new database. It's not so much a problem if the database is blank, because I can copy it wholesale, but if UPDATES against existing data have to occur, then I have a bigger issue. I know with SA 2.x you could turn echoing on in the engine and capture all the statements. I'm wondering if I could use the python logging features to log just INSERT, UPDATE and maybe DELETE as they happen against the database. This would mean that I could just run the queries against the new database. I've seen some comments about logging queries, but haven't found anything on this list or in the documentation that indicate how this would be done. Also, would the output be pure SQL? Even if I had to write a filter to keep extra information out, it'd probably be worth it. Any info pointing me in the right direction would be appreciated. Brian --~--~-~--~~~---~--~~ 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: Lazy load a single column
On 1/5/07, Lee McFadden <[EMAIL PROTECTED]> wrote: On 1/5/07, Sean Davis <[EMAIL PROTECTED]> wrote: > A simple question: > > I have a table with one HUGE column. Is there a way to make this column > lazy-loaded? > http://www.sqlalchemy.org/docs/adv_datamapping.myt#advdatamapping_properties_deferred Perfect! Thanks. Sean --~--~-~--~~~---~--~~ 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: Lazy load a single column
On 1/5/07, Sean Davis <[EMAIL PROTECTED]> wrote: A simple question: I have a table with one HUGE column. Is there a way to make this column lazy-loaded? http://www.sqlalchemy.org/docs/adv_datamapping.myt#advdatamapping_properties_deferred -- Lee McFadden blog: http://www.splee.co.uk work: http://fireflisystems.com skype: fireflisystems --~--~-~--~~~---~--~~ 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] Lazy load a single column
A simple question: I have a table with one HUGE column. Is there a way to make this column lazy-loaded? Thanks, Sean --~--~-~--~~~---~--~~ 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: Foreign key not updated
On 1/5/07, Michael Bayer <[EMAIL PROTECTED]> wrote: yeah see, thats exactly the kind of thing i dont want SA's ORM to get into, because its really thorny..updating the relationship on all child objects. at the very least, it requires loading them all in, cascading the change, etc. it gets pretty nuts and is not particularly scalable (similar to cascading deletes not being terribly scalable in SA either). the best way to cascade a change of PK like that is to set it on your database using ON UPDATE CASCADE, and just do an UPDATE statement. the DB takes care of the rest. Ok, thanks :o) Arnar --~--~-~--~~~---~--~~ 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] no error when using wrong 'attribute' name on an object
Hello, suppose I have the following definition : class Color(ActiveMapper): class mapping: __table__ = "color" id = column(Integer, primary_key=True) color = column(Unicode(32), unique=True) my problem is that if I do a typo when trying to create or modify a "color" like r = Color(colore='red') # notice the e at colore... or r. = 32 I get no error, so debugging is not easy at all ! Is there a way to get some messages when using wrong attributes ? --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
