Python with JSON Create Table SQL-CodePudding

I have 2 list

c_name = ['departmentid', 'name', 'groupname', 'modifieddate']
d_type =['integer', 'character varying', 'character varying', 'timestamp without time zone']

And I would like to create a SQL query for creating a tables

CREATE TABLE MY_TABLE(
  'departmentid' 'integer',
  'name' 'character varying',
  'groupname' 'character varying',
  'modifieddate' 'timestamp without time zone'
);

Thank you for your help

CodePudding user response：

If all you want to do is get the SQL command as text, you can do this with a combination of string concatenation (or formatting) and for loops.

If you want to "connect" to a database and execute the queries, read up on "database connectors" for the relevant database (such as MySQL, SQLite, SQLPlus etc).

Edit

Here's one way to do it. I don't want to just give you an answer without an explanation, but I hope this is self explanatory. Feel free to ask anything confusing.

c_name = ['departmentid', 'name', 'groupname', 'modifieddate']
d_type =['integer', 'character varying', 'character varying', 'timestamp without time zone']

sql_query = "CREATE TABLE MY_TABLE(\n"

for column, datatype in zip(c_name, d_type):
    sql_query  = f"\t\"{column}\" \"{datatype}\",\n"

sql_query  = ");"

print(sql_query)

The zip() function helps deal with two (or more) iterables simultaneously without using range() and len() to create a counter.