Insert into table using For In Range and keys of the value

Question

I have a query (sql1) that populates data, and I am trying to insert the outcome of this data (sql1) as well as other inputs into same table.

Here is first query (sql1).

sql1 = ' Select Creator_Id, Record_Id, max(Course_Num) + 1, SiteCode ' \
       ' from ' \
       ' (Select Creator_Id, Record_Id, max(Course_Num) + 1, SiteCode from Courses ' \
       ' where ( Record_Id not in ("Test") ) ' \
       ' group by Record_Id '

cursor.execute(sql1)
all = cursor.fetchall()

I am not sure bottom code is correct (where fields %s comes in and rest fields).

for Creator_Id, Record_Id, Course_Num, SiteCode in all: 
    sql2 = ' Insert into Courses ' \
             ' ( Creator_Id, ' \
             '  Record_Id, ' \
             '  Course_Num, ' \
             '  SiteCode, ' \
                ' coursename, ' \
                ' datestamp ) ' \
           ' VALUES ' \
           ' ( %s, %s, %s, %s, %s, %s ) ' \

How do I express/complete something like on the bottom on this case (where I have two more columns to insert)?

I got this sample from other post, but not I am sure how to apply Key(s) of the value.

Sorry, I need some guidance regards to what I am doing here.

 cursor.execute(sql2, (Cretor_Id, Record_Id, Course_Num, SiteCode), "UniqueCourseName", "")

I am not clear on this. So, how do I apply leopold's lines combining with Insert statement? — Java, Aug 29 '17 at 20:10
cursor.execute() must be inside the for, you are using the for to get each each column of SELECT and add it to the table — Mauricio Cortazar, Aug 29 '17 at 20:13
I updated my original code on the above. Now the issue is now I am getting this error. - TypeError: execute() takes at most 3 arguments... — Java, Aug 29 '17 at 20:36

score 1 · Answer 1 · answered Aug 29 '17 at 18:25

1

You can just do something like this:

for creator_id, record_id, course_num, site_code in all:
    cursor.execute(sql2, (creator_id, record_id, course_num, site_code))

fetchall() should return a list of tuples. You can unpack the tuples in the for loop to make the code easier to read.

Also, it looks like you're missing an opening ( after Insert into Courses

answered Aug 29 '17 at 18:25

LeopoldVonBuschLight

891
5
13

fetchall() return a dictionary, so if you do that, it will add to the db the key in this case creator_id, record_id, course_num, site_code and not the value – Mauricio Cortazar Aug 29 '17 at 18:31
1

@MauricioCortazar I guess it depends on what you are using... This is what I assumed: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-fetchall.html – LeopoldVonBuschLight Aug 29 '17 at 18:35
well in this case OP is using pymysql and it return a dictionary, anyway good point – Mauricio Cortazar Aug 29 '17 at 18:38
@MauricioCortazar The link I posted is for pymysql. Fetchall returns a list of tuples... – LeopoldVonBuschLight Aug 29 '17 at 18:46
that is MySQL Connector/Python is different from pymysql – Mauricio Cortazar Aug 29 '17 at 18:52
but you are right, in my connector I have DictCursor so return a dic read this https://stackoverflow.com/questions/4940670/pymysql-fetchall-results-as-dictionary – Mauricio Cortazar Aug 29 '17 at 18:59

Mauricio Cortazar · Accepted Answer · 2017-08-29T20:09:05.167

the second part is correct but instead of put this ( %s, %s, %s, %s, "UniqueCourseName", CURDATE() ) use this ( %s, %s, %s, %s, %s, %s ) and:

cursor.execute(sql2, (all[i]['Key1'], all[i]['Key2'], 
                       all[i]['Key3'], all[i]['Key4']),"UniqueCourseName", CURDATE())

if you print(all[0]), you will se something like

'Creator_Id' :'value1'
'Record_Id,':'value2'
' Course_Num':'value3'
             ' SiteCode':'value3'
               ' coursename ':'value4'
               ' datestamp':value5

and 'Key' make reference to creator_id, because is a dictionary.

if return a tuple then:

cursor.execute(sql2, (i[0], i[1], 
                       i[2], i[3]),"UniqueCourseName", CURDATE())

But the error I am getting is "TypeError: tuple indices must be integers, not str" — Java, Aug 29 '17 at 20:03

Insert into table using For In Range and keys of the value

2 Answers2

Linked