RE: [Zope] multiple inserts and MySQLDA
A couple of methods pop into my head: 1) At the point the data is available, use a <dtml-in> to call an insert procedure once for every exam score. 2) Pass the list of (exam_score, student) in as a list of tuples to an sql query and use a <dtml-in> combined with a <dtml-var sql_delimiter> between each insert, such as: <dtml-in exam_scores> INSERT INTO EXAM_DB VALUES (<dtml-sqlvar score type=?>, <dtml-sqlvar student type=nb>) <dtml-unless sequence-end><dtml-var sql_delimiter></dtml-unless> </dtml-in> passing the data in in a usable format could be a challenge if you have two lists instead of a list of tuples. i don't know what the maximum size of an insert is - you may be better of with (1) if the insert size is large. HTH...
-----Original Message----- From: Jorge Magalhaes [mailto:root@predict.telepac.pt] Sent: Thursday, April 06, 2000 5:49 PM To: zope@zope.org Subject: [Zope] multiple inserts and MySQLDA
Hi Zopiis:
How i can build a DTML method for making multiple inserts in MySQL db. For example, inserting a exames scores for a class with 20 students.
My problem isn't make the sql query, but call the query for each pair (student_id, score).
I would like fetched the student_id and the student name from the db. How i can do it?
I find in this newsgroup some posts about the Product MySQLDA1.4. I have some queries for select integers and y d'ont have the related problems. For that i have changed the type INT(2), for example, to INT
If a is defined a INT(2)
for the query SELECT a FROM T1, the result is (23L)
i make
ALTER TABLE t1 CHANGE a a INT;
after this modifications for the query
SELECT a FROM T1, the result is (23)
It's Ok
Have a nice day. -- +------------------------------------------------------------- ---------+ | Jorge Magalhães Email: jmagalhaes@eng.uminho.pt | | Quinta do Calvário, bloco 14 Telemóvel: 966172200 | | 2º Direito Trás Fax: + 351 253 516007 | | 5400 Chaves Phone: + 351 276 333008 | | Portugal Email: magalhaes.jorge@netc.pt | +------------------------------------------------------------- ---------+
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Actually, MySQL allows the insertion of multiple records in a single insert statement, and it is orders of magnitude faster than looping in dtml while executing a sql query on every iteration. Just do: INSERT INTO table (field1, field2) VALUES (value1, value2), (value3, value4), (value5, value6),... Each set of values must be wrapped in parentheses, and there should NOT be parenthese around the entire list of value sets. Obviously, you have to build the list with a dtml-in statement, although you could possibly trick dtml-sql into doing it for you by using a list of tuples and setting the multiple variable. I don't know about that one. --sam Daniel.Weber@sematech.org wrote:
A couple of methods pop into my head:
1) At the point the data is available, use a <dtml-in> to call an insert procedure once for every exam score. 2) Pass the list of (exam_score, student) in as a list of tuples to an sql query and use a <dtml-in> combined with a <dtml-var sql_delimiter> between each insert, such as:
<dtml-in exam_scores> INSERT INTO EXAM_DB VALUES (<dtml-sqlvar score type=?>, <dtml-sqlvar student type=nb>) <dtml-unless sequence-end><dtml-var sql_delimiter></dtml-unless> </dtml-in>
passing the data in in a usable format could be a challenge if you have two lists instead of a list of tuples. i don't know what the maximum size of an insert is - you may be better of with (1) if the insert size is large.
HTH...
-----Original Message----- From: Jorge Magalhaes [mailto:root@predict.telepac.pt] Sent: Thursday, April 06, 2000 5:49 PM To: zope@zope.org Subject: [Zope] multiple inserts and MySQLDA
Hi Zopiis:
How i can build a DTML method for making multiple inserts in MySQL db. For example, inserting a exames scores for a class with 20 students.
My problem isn't make the sql query, but call the query for each pair (student_id, score).
I would like fetched the student_id and the student name from the db. How i can do it?
I find in this newsgroup some posts about the Product MySQLDA1.4. I have some queries for select integers and y d'ont have the related problems. For that i have changed the type INT(2), for example, to INT
If a is defined a INT(2)
for the query SELECT a FROM T1, the result is (23L)
i make
ALTER TABLE t1 CHANGE a a INT;
after this modifications for the query
SELECT a FROM T1, the result is (23)
It's Ok
Have a nice day. -- +------------------------------------------------------------- ---------+ | Jorge Magalhães Email: jmagalhaes@eng.uminho.pt | | Quinta do Calvário, bloco 14 Telemóvel: 966172200 | | 2º Direito Trás Fax: + 351 253 516007 | | 5400 Chaves Phone: + 351 276 333008 | | Portugal Email: magalhaes.jorge@netc.pt | +------------------------------------------------------------- ---------+
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
On Fri, 7 Apr 2000 Daniel.Weber@SEMATECH.Org wrote:
A couple of methods pop into my head:
1) At the point the data is available, use a <dtml-in> to call an insert procedure once for every exam score.
This is my first problem: make the tuple list!!! my table in the db have two fields (student_name and student_id) my first query are select student_id as ID from students; in DTML method i tried: <form action="send"> <table> <dtml-in "firstquery()"> <tr> <td><dtml-var ID></td> <td><input type="TEXT" name="scor"></td> </tr> <td><input type="submit" value="Score it"></td> </table> </form> my second query is: insert into scores (student_id, score) values (<dtml.sqlvar IDE type=int>, <dtml-sqlvar SCORE type=int) my send method <dtml-in "firstquery()"> <dtml-call "secondquery(IDE = ID, SCORE= scor)"> </dtml-in> don't work !!!!!!!!!!!!!!!!! bad news Thanks for your attention
2) Pass the list of (exam_score, student) in as a list of tuples to an sql query and use a <dtml-in> combined with a <dtml-var sql_delimiter> between each insert, such as:
<dtml-in exam_scores> INSERT INTO EXAM_DB VALUES (<dtml-sqlvar score type=?>, <dtml-sqlvar student type=nb>) <dtml-unless sequence-end><dtml-var sql_delimiter></dtml-unless> </dtml-in>
passing the data in in a usable format could be a challenge if you have two lists instead of a list of tuples. i don't know what the maximum size of an insert is - you may be better of with (1) if the insert size is large.
HTH...
-----Original Message----- From: Jorge Magalhaes [mailto:root@predict.telepac.pt] Sent: Thursday, April 06, 2000 5:49 PM To: zope@zope.org Subject: [Zope] multiple inserts and MySQLDA
Hi Zopiis:
How i can build a DTML method for making multiple inserts in MySQL db. For example, inserting a exames scores for a class with 20 students.
My problem isn't make the sql query, but call the query for each pair (student_id, score).
I would like fetched the student_id and the student name from the db. How i can do it?
I find in this newsgroup some posts about the Product MySQLDA1.4. I have some queries for select integers and y d'ont have the related problems. For that i have changed the type INT(2), for example, to INT
If a is defined a INT(2)
for the query SELECT a FROM T1, the result is (23L)
i make
ALTER TABLE t1 CHANGE a a INT;
after this modifications for the query
SELECT a FROM T1, the result is (23)
It's Ok
Have a nice day. -- +------------------------------------------------------------- ---------+ | Jorge Magalhães Email: jmagalhaes@eng.uminho.pt | | Quinta do Calvário, bloco 14 Telemóvel: 966172200 | | 2º Direito Trás Fax: + 351 253 516007 | | 5400 Chaves Phone: + 351 276 333008 | | Portugal Email: magalhaes.jorge@netc.pt | +------------------------------------------------------------- ---------+
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
You can actually nest ZSQL methods in a couple of ways. First of al, I sometimes find it easiest to use a dtml-let to set up the namespace for the second query, when doing what you are doing eg: <dtml-in qry1> <dtml-let value1=var1 value2=var2> <dtml-in qry2> ... </dtml-in> </dtml-let> </dtml-in This has always worked for me. There should be know need to pass parameters in explicitly, since dtml-let should put them in the namespace. HOWEVER, depending on your needs, this is sometime easier. ZSQLMethod # 1 (id is qry1) SELECT (value1, value2) from table1 ZSQLMethod # 2 (id is qry2) INSERT INTO table2 values <dtml-in qry1> <dtml-unless sequence-start>,</dtml-unless> (<dtml-var value1>, <dtml-var value2>) </dtml-in> Notice that I am calling the first query directly from the second query, which is about as close to nested SELECT statements as you can get in MySQL. --sam Jorge Magalhaes wrote:
On Fri, 7 Apr 2000 Daniel.Weber@SEMATECH.Org wrote:
A couple of methods pop into my head:
1) At the point the data is available, use a <dtml-in> to call an insert procedure once for every exam score.
This is my first problem: make the tuple list!!!
my table in the db have two fields (student_name and student_id)
my first query are
select student_id as ID from students;
in DTML method i tried:
<form action="send"> <table> <dtml-in "firstquery()"> <tr> <td><dtml-var ID></td> <td><input type="TEXT" name="scor"></td> </tr> <td><input type="submit" value="Score it"></td> </table> </form>
my second query is:
insert into scores (student_id, score) values (<dtml.sqlvar IDE type=int>, <dtml-sqlvar SCORE type=int)
my send method
<dtml-in "firstquery()"> <dtml-call "secondquery(IDE = ID, SCORE= scor)"> </dtml-in>
don't work !!!!!!!!!!!!!!!!!
bad news
Thanks for your attention
2) Pass the list of (exam_score, student) in as a list of tuples to an sql query and use a <dtml-in> combined with a <dtml-var sql_delimiter> between each insert, such as:
<dtml-in exam_scores> INSERT INTO EXAM_DB VALUES (<dtml-sqlvar score type=?>, <dtml-sqlvar student type=nb>) <dtml-unless sequence-end><dtml-var sql_delimiter></dtml-unless> </dtml-in>
passing the data in in a usable format could be a challenge if you have two lists instead of a list of tuples. i don't know what the maximum size of an insert is - you may be better of with (1) if the insert size is large.
HTH...
-----Original Message----- From: Jorge Magalhaes [mailto:root@predict.telepac.pt] Sent: Thursday, April 06, 2000 5:49 PM To: zope@zope.org Subject: [Zope] multiple inserts and MySQLDA
Hi Zopiis:
How i can build a DTML method for making multiple inserts in MySQL db. For example, inserting a exames scores for a class with 20 students.
My problem isn't make the sql query, but call the query for each pair (student_id, score).
I would like fetched the student_id and the student name from the db. How i can do it?
I find in this newsgroup some posts about the Product MySQLDA1.4. I have some queries for select integers and y d'ont have the related problems. For that i have changed the type INT(2), for example, to INT
If a is defined a INT(2)
for the query SELECT a FROM T1, the result is (23L)
i make
ALTER TABLE t1 CHANGE a a INT;
after this modifications for the query
SELECT a FROM T1, the result is (23)
It's Ok
Have a nice day. -- +------------------------------------------------------------- ---------+ | Jorge Magalhães Email: jmagalhaes@eng.uminho.pt | | Quinta do Calvário, bloco 14 Telemóvel: 966172200 | | 2º Direito Trás Fax: + 351 253 516007 | | 5400 Chaves Phone: + 351 276 333008 | | Portugal Email: magalhaes.jorge@netc.pt | +------------------------------------------------------------- ---------+
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
You can actually nest ZSQL methods in a couple of ways. First of al, I sometimes find it easiest to use a dtml-let to set up the namespace for the second query, when doing what you are doing eg:
<dtml-in qry1> <dtml-let value1=var1 value2=var2> <dtml-in qry2> ... </dtml-in> </dtml-let> </dtml-in
This has always worked for me. There should be know need to pass parameters in explicitly, since dtml-let should put them in the namespace.
HOWEVER, depending on your needs, this is sometime easier.
ZSQLMethod # 1 (id is qry1)
SELECT (value1, value2) from table1
ZSQLMethod # 2 (id is qry2)
INSERT INTO table2 values <dtml-in qry1> <dtml-unless sequence-start>,</dtml-unless> (<dtml-var value1>, <dtml-var value2>) </dtml-in>
I see (value1=student_id, value2=score ). The value1 coming from the qry1 (OK) but in the qry2 i need to insert for each value1 a new value2. I think that a need do some modifications. I have tried to substitue <dtml-var value2> for <dtml-sqlvar SCORE type=int> but the qry2 inserting the same value2 for each value1. Thanks jorge.
Notice that I am calling the first query directly from the second query, which is about as close to nested SELECT statements as you can get in MySQL.
--sam
If you have a list of scores that is in the same order as the student list (either by doing a joined query, or some other method), you can then access the appropriate item in the scores list while looping through the students by doing <dtml-var "scores_list[_['sequence-index']]">, which will return the appropriate entry from the list. That should be enough to devise a solution that works for you. Alternatively, you may just have to call the insert query many times, in a dtml-in loop. In that case, just use the dtml-let code that I sent you earlier. --sam Jorge Magalhaes wrote:
You can actually nest ZSQL methods in a couple of ways. First of al, I sometimes find it easiest to use a dtml-let to set up the namespace for the second query, when doing what you are doing eg:
<dtml-in qry1> <dtml-let value1=var1 value2=var2> <dtml-in qry2> ... </dtml-in> </dtml-let> </dtml-in
This has always worked for me. There should be know need to pass parameters in explicitly, since dtml-let should put them in the namespace.
HOWEVER, depending on your needs, this is sometime easier.
ZSQLMethod # 1 (id is qry1)
SELECT (value1, value2) from table1
ZSQLMethod # 2 (id is qry2)
INSERT INTO table2 values <dtml-in qry1> <dtml-unless sequence-start>,</dtml-unless> (<dtml-var value1>, <dtml-var value2>) </dtml-in>
I see (value1=student_id, value2=score ). The value1 coming from the qry1 (OK) but in the qry2 i need to insert for each value1 a new value2. I think that a need do some modifications. I have tried to substitue
<dtml-var value2> for <dtml-sqlvar SCORE type=int> but the qry2 inserting the same value2 for each value1.
Thanks
jorge.
Notice that I am calling the first query directly from the second query, which is about as close to nested SELECT statements as you can get in MySQL.
--sam
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
participants (3)
-
Daniel.Weber＠SEMATECH.Org -
Jorge Magalhaes -
Sam Gendler