create mysql table in python-CodePudding

I want to create mysql table in python taking column names from mongodb using pymongo. I have 31 columns in my dataset. The code is below

      from pymongo import MongoClient                                           
      import mysql.connector                                                                    
                                                                                                              
   def migrate():                                                   
                                                       
    client=MongoClient('mongodb://localhost:27017')                                     
    db=client['mydb']                                                          
    coll=db['data']                                                            
    mongo_docs = coll.find()                                                            
    fieldnames = list(mongo_docs[0].keys()) #gives all the columns in the dataset                  
    fieldnames.remove('_id')                                            
    print(fieldnames)                                       
    connection= mysql.connector.connect(host="localhost",user="root",database="mydb",password="paswd")       
    
    cursor1=connection.cursor()                                         
    connection.commit()                                                
    col_namestypes=['int(3)','date','float(8,5)','int(3)','float(8,5)']              
    for x in fieldnames:                                          
            for i in col_namestypes:                            
                    if i==col_namestypes[0]:                                           
                            cursor1.execute("CREATE TABLE ndata(%s %s)" %(x)%(i))

migrate()

I want to create table like--> create table ndata(fieldnames datatype)

These are my columns/fieldnames ['STA', 'Date', 'Precip', 'WindGustSpd', 'MaxTemp', 'MinTemp', 'MeanTemp', 'Snowfall', 'PoorWeather', 'YR', 'MO', 'DA', 'PRCP', 'DR', 'SPD', 'MAX', 'MIN', 'MEA', 'SNF', 'SND', 'FT', 'FB', 'FTI', 'ITH', 'PGT', 'TSHDSBRSGF', 'SD3', 'RHX', 'RHN', 'RVG', 'WTE']

I got an error like: TypeError: not enough arguments for format string

Do i have to specify all the columns datatypes in col_nametypes? or what changes should i make?

CodePudding user response：

# columns names 
fieldnames =['col1','col2','col3','col4','col5']
# columns types
col_namestypes=['int(3)','date','float(8,5)','int(3)','float(8,5)']

# iterate through columns definitions
for i in range(len(fieldnames)):                                          
    if 0==i:  # creates table using first column definition
        # cursor1.execute(f"DROP TABLE IF EXISTS mydb.ndata")  # be careful not to drop table with data 
        cursor1.execute(f"CREATE TABLE IF NOT EXISTS mydb.ndata({fieldnames[i]} {col_namestypes[i]})") 
    else:   # adds other columns using ALTER statement one by one
        cursor1.execute(f"ALTER TABLE mydb.ndata ADD {fieldnames[i]} {col_namestypes[i]}")

CodePudding user response：

The problem is with this part:

for x in fieldnames:                                          
    for i in col_namestypes:                            
        if i==col_namestypes[0]:                                           
            cursor1.execute("CREATE TABLE ndata(%s %s)" %(x)%(i))

So, basically you are trying to create ndata table several times because of the loop, but you need to create table once with appropriate arguments.

Also, this part of a code:

for i in col_namestypes:  
    i==col_namestypes[0]

It means that for each element check if element equals element, it's just strange, if you need to check for int, so check for it:

for i in col_namestypes:                            
    if i=='int(3)':

@alexspb provided a nice solution for this problem.