Using a variable for a table name with python sql cursor

Question

I am using python sql cursor to dynamically access my database and I am in a situation where I want to use a variable in place of a table name. So far all of my attempts have resulted in syntax errors, although I (think?) I am doing things as expected? Unless a table name as a variable is different from a value as a variable:

here is what I currently have:

cursor.execute("INSERT INTO %s (word=%s,item_id=%s,word_tag=%s,unstemmed_word=%s, word_position=%s, TF=%s, normalized_term_frequency=%s, sentence=%s,anthology_id=%s) "%(table_name, stemmedWord,fle.split()[0], str(word[1]), uniqeWord, word_pos, TF, normalized_term_frequency, sentence, fle.split()[1].split(".")[0]))

and I have also tried this:

cursor.execute("INSERT INTO %s (word,item_id,word_tag,unstemmed_word, word_position, TF, normalized_term_frequency, sentence,anthology_id) values(%s, %s,%s, %s, %s, %s, %s, %s, %s)",(table_name, stemmedWord,fle.split()[0], str(word[1]), uniqeWord, word_pos, TF, normalized_term_frequency, sentence, fle.split()[1].split(".")[0]))

What dbapi do you use?

NobbyNobbs
– NobbyNobbs

2018-06-25 07:55:29 +00:00
Commented Jun 25, 2018 at 7:55 — NobbyNobbs
– NobbyNobbs, Commented Jun 25, 2018 at 7:55

Mureinik · Accepted Answer · 2018-06-25 07:53:19Z

5

You cannot dynamically bind object names, only values. You'll have to resort to string manipulation for the table's name. E.g.:

sql = "INSERT INTO {} (word=%s,item_id=%s,word_tag=%s,unstemmed_word=%s, word_position=%s, TF=%s, normalized_term_frequency=%s, sentence=%s,anthology_id=%s)".format(table_name)

cursor.execute(sql % (stemmedWord,fle.split()[0], str(word[1]), uniqeWord, word_pos, TF, normalized_term_frequency, sentence, fle.split()[1].split(".")[0]))

answered Jun 25, 2018 at 7:53

Mureinik

316k54 gold badges402 silver badges406 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paula Thomas · Accepted Answer · 2018-06-25 08:28:20Z

1

If you are on python >= 3.6 this is probably better:

 cursor.execute(f'INSERT INTO {table_name} (word="{stemmedWord}",item_id={fle.split()[0]},word_tag={str(word[1])},unstemmed_word="{oword_posrmuniqeWord}", word_position=word_pos, TF={TF}, normalized_term_frequency={normalized_term_frequency}, sentence="{sentence}",anthology_id={fle.split()[1].split(".")[0])}'

but I think your syntax errors are coming from two things:

you have provided a string to split fle on. (Correction this defaults to space - so is OK!)
you haven't quoted what seem to be obvious strings in you sql fields.

edited Jun 25, 2018 at 8:28

answered Jun 25, 2018 at 8:13

Paula Thomas

1,1901 gold badge10 silver badges15 bronze badges

Collectives™ on Stack Overflow

Using a variable for a table name with python sql cursor

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related