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? — Aran-Fey
– Aran-Fey, Commented Apr 8, 2018 at 8:27

Ollie · Accepted Answer · 2018-04-08 08:36:41Z

1

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)".

answered Apr 8, 2018 at 8:36

Ollie

1,7421 gold badge15 silver badges33 bronze badges

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

Comments

ryanlee · Accepted Answer · 2018-04-08 08:35:25Z

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 8, 2018 at 8:35

ryanlee

4014 silver badges11 bronze badges

Comments

sciroccorics · Accepted Answer · 2018-04-08 09:18:08Z

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 8, 2018 at 9:18

sciroccorics

2,4271 gold badge11 silver badges23 bronze badges

Collectives™ on Stack Overflow

how to build create table script in python

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related