How to make a dynamic 'CREATE TABLE' MySQL query from python list of column names and datatypes usin 'pymysql'?

Question

I have the following data:

table_name=xyz
column_names=['c1', 'c2', 'c3', 'c4']
column_datatypes=['VARCHAR(40)', 'BIGINT(40)', 'BIGINT(20)', 'VARCHAR(10)'] 

Which I am reading from a file .i.e The length of the above lists can vary.So I want to create a dynamic 'CREATE TABLE' MySQL query such as:

CREATE TABLE xyz(c1 VARCHAR(40),c2 BIGINT(40),c3 BIGINT(20),c4 VARCHAR(10));

How can I forge a string query such as above(maybe using string.format())?

Also, Kindly upvote the question (If worthy) as I am on verge of getting blocked from asking further questions as my previous questions did not receive any voting.

Answer 1

Here you go!

import MySQLdb as mdb



table_name='xyz'
column_names=['c1', 'c2', 'c3', 'c4']
column_datatypes=['VARCHAR(40)', 'BIGINT(40)', 'BIGINT(20)', 'VARCHAR(10)']

db=mdb.connect(host='localhost',user='root',passwd='*****',db='test',port=3306)
cursor=db.cursor()

create_stement='create table '+table_name + '('
i=0
while i< len(column_names)-1 :

    create_stement =create_stement +column_names[i] +'  '+column_datatypes[i]+' ,'
    i=i+1

create_stement =create_stement +column_names[i] +'  '+column_datatypes[i]+' )'

print(create_stement)
cursor.execute(create_stement)
print('Done')