[sqlalchemy] Re: Doing a dynamic Update
Rick,
On Jan 15, 1:34 pm, Rick Morrison [EMAIL PROTECTED] wrote:
Two issues:
a) You need to give SA a table definition for the table you're trying to
update.
b) You need to specify the name of the column to update in the dict(), not
the string 'key'
I've updated the script to work by passing in both the column name to update
and the update value to use. I've made my changes in bold below:
def main(*key, val*):
engine = create_engine('mssql://dbName:[EMAIL PROTECTED]') * # No table
name in
URI !! /%s' % tblName)*
I actually had this right in my code, but I posted it incorrectly when
sanitizing it for the newsgroup.
conn = engine.connect()
# create MetaData
meta = MetaData()
# bind to an engine
meta.bind = engine
*
# specify table definition
tbl = Table('tbl_Acct_prefs', meta,
Column('netname', VARCHAR(20)),
Column('pref_name', VARCHAR(40)),
Column('pref_value', VARCHAR(40))
)*
# create metadata
*#* meta.create_all() * # === you need this only if you're creating
the table with your program*
I was following the docs on the website for this one...I guess I
misunderstood since their example was using a sqlite database in
memory. Oh well...
tbl.update(tbl.c.netname=='saw').execute(*{key:val}*)
This worked. Thank you for your patience and for sharing your
knowledge.
Mike
--~--~-~--~~~---~--~~
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: Doing a dynamic Update
Rick, On Jan 10, 7:02 pm, Rick Morrison [EMAIL PROTECTED] wrote: For a stepwise migration from raw, SQL, it will probably be easier to get your mind around the SQL-expression side of the library, and then adopt ORM features as you feel comfortable with them. On the SQL-expression side of the library, you'll find that your Table() object has a collection called c (for Columns). It's a dict-like collection that supports retrieving the column by name: Table.update() takes a dictionary of updates, so the name-based access is already in there: tbl.update(tbl.c.dateworked == mydate).execute(dict(columnname = newvalue)) or using sessions: S.execute(tbl.update(tbl.c.dateworked == mydate), dict(columnname = newvalue)) HTH, Rick Sorry I didn't reply sooner. I got busy with other things at work last week. To use your first suggestion, I would have to use a series of if statements like below, correct? if colName == 'someColName': tbl.update(tbl.c.dateworked == mydate).execute(dict(columnname =newvalue)) elif colName == 'anotherColName': tbl.update(tbl.c.dateworked == mydate).execute(dict(othercolumnname =newvalue)) else: pass This will work, but it's not quite as elegant as I had hoped. The sessions method looks like it would have the same issue. Thanks for the advise. Mike --~--~-~--~~~---~--~~ 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: Doing a dynamic Update
Hey Mike You don't need to go through that. Just populate the dictionary used for the execute() with the appropriate column name as the key, and you're good to go. On Jan 15, 2008 10:05 AM, Mike [EMAIL PROTECTED] wrote: Rick, On Jan 10, 7:02 pm, Rick Morrison [EMAIL PROTECTED] wrote: For a stepwise migration from raw, SQL, it will probably be easier to get your mind around the SQL-expression side of the library, and then adopt ORM features as you feel comfortable with them. On the SQL-expression side of the library, you'll find that your Table() object has a collection called c (for Columns). It's a dict-like collection that supports retrieving the column by name: Table.update() takes a dictionary of updates, so the name-based access is already in there: tbl.update(tbl.c.dateworked == mydate).execute(dict(columnname = newvalue)) or using sessions: S.execute(tbl.update(tbl.c.dateworked == mydate), dict(columnname = newvalue)) HTH, Rick Sorry I didn't reply sooner. I got busy with other things at work last week. To use your first suggestion, I would have to use a series of if statements like below, correct? if colName == 'someColName': tbl.update(tbl.c.dateworked == mydate).execute(dict(columnname =newvalue)) elif colName == 'anotherColName': tbl.update(tbl.c.dateworked == mydate).execute(dict(othercolumnname =newvalue)) else: pass This will work, but it's not quite as elegant as I had hoped. The sessions method looks like it would have the same issue. Thanks for the advise. Mike --~--~-~--~~~---~--~~ 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: Doing a dynamic Update
On Jan 15, 9:20 am, Rick Morrison [EMAIL PROTECTED] wrote:
Hey Mike
You don't need to go through that. Just populate the dictionary used for the
execute() with the appropriate column name as the key, and you're good to
go.
On Jan 15, 2008 10:05 AM, Mike [EMAIL PROTECTED] wrote:
content snipped for brevity
if colName == 'someColName':
tbl.update(tbl.c.dateworked == mydate).execute(dict(columnname
=newvalue))
elif colName == 'anotherColName':
tbl.update(tbl.c.dateworked ==
mydate).execute(dict(othercolumnname =newvalue))
else:
pass
This will work, but it's not quite as elegant as I had hoped. The
sessions method looks like it would have the same issue. Thanks for
the advise.
Mike
Ok...so here goes:
# Column names:
#netname
#pref_name
#pref_value
def main(tblName, key):
engine = create_engine('mssql://dbName:[EMAIL PROTECTED]/%s' % tblName)
conn = engine.connect()
# create MetaData
meta = MetaData()
# bind to an engine
meta.bind = engine
# create metadata
meta.create_all()
tbl.update(tbl.c.netname=='saw').execute(dict(key = 'New Val'))
if __name__ == '__main__':
tblName = 'tbl_Acct_Prefs'
pref_value = 'someval'
main(tblName, pref_value)
This returns
Incorrect syntax near the keyword 'WHERE'.
DB-Lib error message 10007, severity 5:
General SQL Server error: Check messages from the SQL Server.
'UPDATE [tbl_Acct_Prefs] SET WHERE [tbl_Acct_Prefs].netname = %
(tbl_Acct_Prefs_netname_1)s' {'tbl_Acct_Prefs_netname_1': 'saw'}
If I just do this:
if __name__ == '__main__':
tblName = 'tbl_Acct_Prefs'
main(tblName, pref_value)
I get a variable undefined error, which I understand. I think I know
what you mean, but I don't know how to pass in an object that doesn't
exist yet. I tried creating a dict inside the main() where I did this:
colDict = {'netname':netname, 'pref_name':pref_name,
'pref_value':pref_value}
tbl.update(tbl.c.netname=='saw').execute(dict(colDict[key] = 'New
Val'))
where key was one of the string keys in colDict...but it didn't like
that much either.
ORM appears to be harder to grasp than I had originally thought.
Mike
--~--~-~--~~~---~--~~
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: Doing a dynamic Update
Two issues:
a) You need to give SA a table definition for the table you're trying to
update.
b) You need to specify the name of the column to update in the dict(), not
the string 'key'
I've updated the script to work by passing in both the column name to update
and the update value to use. I've made my changes in bold below:
def main(*key, val*):
engine = create_engine('mssql://dbName:[EMAIL PROTECTED]') * # No table name
in
URI !! /%s' % tblName)*
conn = engine.connect()
# create MetaData
meta = MetaData()
# bind to an engine
meta.bind = engine
*
# specify table definition
tbl = Table('tbl_Acct_prefs', meta,
Column('netname', VARCHAR(20)),
Column('pref_name', VARCHAR(40)),
Column('pref_value', VARCHAR(40))
)*
# create metadata
*#* meta.create_all() * # === you need this only if you're creating
the table with your program*
tbl.update(tbl.c.netname=='saw').execute(*{key:val}*)
--~--~-~--~~~---~--~~
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: Doing a dynamic Update
For a stepwise migration from raw, SQL, it will probably be easier to get your mind around the SQL-expression side of the library, and then adopt ORM features as you feel comfortable with them. On the SQL-expression side of the library, you'll find that your Table() object has a collection called c (for Columns). It's a dict-like collection that supports retrieving the column by name: Table.update() takes a dictionary of updates, so the name-based access is already in there: tbl.update(tbl.c.dateworked == mydate).execute(dict(columnname = newvalue)) or using sessions: S.execute(tbl.update(tbl.c.dateworked == mydate), dict(columnname = newvalue)) HTH, Rick --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
