SQLs from Python strings
On first impressions this may look like a homework assignment question but I can assure you it isn't! I'm having difficulty trying to explain this (hence the strange subject line ;-) but I hope someone can offer me some advice... I have four attributes (A, B, C, D) and each is an integer say, between 0 and 9. I need a method to create a table with the following columns: Name Age [if A=3] then A1, A2, A3 [if B=1] then B1 i.e. if A=3 and B=1 and C & D =0 then 6 columns will be created. I already have the code (well, the logic) to do this but what is the best way to do it. ZSQL methods can't handle such logic, right? I was thinking maybe passing the parameters into a python method to build a "CREATE TABLE..." string... but can this be used to actually execute the query? I don't know if I have explained this well enough. Here's the logic (no particular language and A,B,C,D = P,T,A,B) int p = PRACTICALS; int t = TUTORIALS; int a = ASSIGNMENTS; int b = BONUS; string classAndCode = CLASSCODE; String createDB = "CREATE TABLE " + classAndCode + " ( " while (p != 0) { String createDB = create DB + "p" + p + "integer, " // e.g. p0, p1, p2, etc. p = p-1; } while (t != 0) { String createDB = create DB + "t" + t + "varchar, " t = t-1; } ... etc Okay, say I have a python method, which returns an String (an SQL CREATE TABLE statement). How could I execute this? Thanks very much in advance. Lee PS: Alex, I managed to get the COUNT var to work. The solution was simply a good night's sleep and a large mug of coffee in the morning. Cheers :) -- Lee Reilly mailto:lee.reilly@ntlworld.com http://www.footkick.co.uk/lee
Hi Lee, Two points: 1) DTML works fine in ZSQL methods and 2) Due to (1) you can use <dtml-var xxx> and pull in anything from Zope that you like (but be careful... since someone might be able to pull in something you weren't thinking of when you set up the method: e.g., if you have a method called foo, with the line: <dtml-var xxx> someone might try: http://yourserver/foo?xxx=drop%20table%20valueableData and your valuable data would be gone! Make sure that only folks with the correct privs, or methods with the correct proxy can call your method. -steve
"Lee" == Lee <lee.reilly@ntlworld.com> writes:
Lee> On first impressions this may look like a homework assignment Lee> question but I can assure you it isn't! I'm having difficulty Lee> trying to explain this (hence the strange subject line ;-) Lee> but I hope someone can offer me some advice... Lee> I have four attributes (A, B, C, D) and each is an integer Lee> say, between 0 and 9. Lee> I need a method to create a table with the following columns: Lee> Name Age [if A=3] then A1, A2, A3 [if B=1] then B1 Lee> i.e. if A=3 and B=1 and C & D =0 then 6 columns will be Lee> created. Lee> I already have the code (well, the logic) to do this but what Lee> is the best way to do it. ZSQL methods can't handle such Lee> logic, right? I was thinking maybe passing the parameters Lee> into a python method to build a "CREATE TABLE..." Lee> string... but can this be used to actually execute the query? Lee> I don't know if I have explained this well enough. Lee> Here's the logic (no particular language and A,B,C,D = Lee> P,T,A,B) Lee> int p = PRACTICALS; int t = TUTORIALS; int a = ASSIGNMENTS; Lee> int b = BONUS; Lee> string classAndCode = CLASSCODE; Lee> String createDB = "CREATE TABLE " + classAndCode + " ( " Lee> while (p != 0) { String createDB = create DB + "p" + p + Lee> "integer, " // e.g. p0, p1, p2, etc. p = p-1; } Lee> while (t != 0) { String createDB = create DB + "t" + t + Lee> "varchar, " t = t-1; } Lee> ... etc Lee> Okay, say I have a python method, which returns an String (an Lee> SQL CREATE TABLE statement). How could I execute this? Lee> Thanks very much in advance. Lee> Lee Lee> PS: Alex, I managed to get the COUNT var to work. The Lee> solution was simply a good night's sleep and a large mug of Lee> coffee in the morning. Cheers :) Lee> -- Lee Reilly mailto:lee.reilly@ntlworld.com Lee> http://www.footkick.co.uk/lee Lee> _______________________________________________ Zope maillist Lee> - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope Lee> ** No cross posts or HTML encoding! ** (Related lists - Lee> http://lists.zope.org/mailman/listinfo/zope-announce Lee> http://lists.zope.org/mailman/listinfo/zope-dev )
participants (2)
-
Lee -
Steve Spicklemire