how to build create table script in python

Question

I am trying to create an Oracle table using Python 3.5. I have the column names and data type stored in a dictionary, and I want to create an SQL statement (stored in sql_stmt) to execute it from Python. My dictionary looks like this:

adict1 = {'col1': 'Number', 'col2': 'Date', 'col3': 'Date'}

I am trying to create an SQL statement which looks like this:

create table dummy(col1 Number, col2 Date, col3 Date)

So, I was trying to create the SQL statement by looping through the dictionary keys:

sql_stmt = "create table"

for key in adict1 :
   sql_stmt = sql_stmt + ("dummy('%s' , '%s')" ,(key,adict1[key]))

But it throws the error:

Traceback (most recent call last):
File "<pyshell#413>", line 2, in <module>
sql_stmt = sql_stmt + ("dumm5('%s' , '%s')" ,(key,adict1[key]))
TypeError: must be str, not tuple

What have I done wrong?

Possible duplicate of [How do I put a variable inside a String in Python?](https://stackoverflow.com/questions/2960772/how-do-i-put-a-variable-inside-a-string-in-python) — Aran-Fey, Apr 08 '18 at 08:27

score 1 · Answer 1 · answered Apr 08 '18 at 08:35

1

there is a error when you plus the string:

sql_stmt = sql_stmt + ("dumm5('%s' , '%s')" ,(key,adict1[key]))

you can format it like this:

sql_stmt = sql_stmt + 'dumm5({0}, {1})'.format(key, adict1[key])

answered Apr 08 '18 at 08:35

ryanlee

341
3
9

score 1 · Accepted Answer · answered Apr 08 '18 at 08:36

You need to keep the table name and opening bracket outside the loop, make sure the comma is in the right place, use {} and .format to format your string with the variables, remove the last comma, then add the closing bracket.

adict1 = {'col1': 'Number', 'col2': 'Date', 'col3': 'Date'}

sql_stmt = "create table dummy("

for key in adict1 :
   sql_stmt = sql_stmt + ("{} {}, ".format(key,adict1[key]))

sql_stmt = sql_stmt[:-2]
sql_stmt += ")"

Then sql_stmt is "create table dummy(col2 Date, col3 Date, col1 Number)".

score 0 · Answer 3 · answered Apr 08 '18 at 09:18

0

A more pythonic solution:

adict1 = {'col1':'Number', 'col2':'Date', 'col3':'Date'}
sql_stmt = "create table dummy(%s)" % ', '.join(key+' '+val for key,val in adict1.items())

but it may appear somewhat "over-pythonic"...

answered Apr 08 '18 at 09:18

sciroccorics

2,357
1
8
21

how to build create table script in python

3 Answers3