In [17]:

    

import re
import pandas as pd
import numpy as np

#Location = r'./canales/media/pictures/nwpat_EErd9Nc.csv'
Location = r'./canales/media/pictures/nwcas_uTqj6eE.csv'
df = pd.read_csv(Location)
dwot = re.compile('\d{2,2}/\d{2,2}/\d{4,4}')
dwt = re.compile('\d{2,2}/\d{2,2}/\d{4,4} \d{1,2}:\d{2,2}:\d{2,2} (AM|PM)')
t_cols = []

def str_or_date(val):
    if type(val) == str:
        if (dwot.match(val) or dwt.match(val)):
            return 'DATETIME'
        else:
            return ('VARCHAR')
    else:
        return 'VARCHAR(1200) CHARACTER SET utf8'
    
for column in df:
    
    dt = type(df[column].iloc[0])
    if dt == str:
        c_type = df[column].apply(lambda x: str_or_date(x))[0]
        if c_type == 'VARCHAR':
            c_len = df[column].apply(lambda x: len(str(x))).max()
            c_def = ("%s(%i) CHARACTER SET utf8" % (c_type, c_len))
        else:
            c_def = c_type
        t_cols.append((column, c_def))
    elif dt == np.int64:
        t_cols.append((column, 'INT'))
    elif dt == float or np.float64:
        t_cols.append((column, 'FLOAT'))
    elif dt == np.bool_:
        t_cols.append((column, 'BOOL'))


c_cont = 0
t_name = Location.split('/')[len(Location.split('/')) - 1].split('.')[0]
create_table = ("CREATE TABLE %s (\n\r" % t_name)
for c in t_cols:
    c_name = c[0].replace(' ', '_')
    if c_cont < len(t_cols)-1:
        create_table += '\t`'+c_name+'` '+c[1]+',\n\r'
    else:
        create_table += '\t`'+c_name+'` '+c[1]+'\n\r'
    c_cont+=1
create_table +=');'

#print(create_table)
c_cont = 0
insert_to = 'INSERT INTO table ('
for i_value in t_cols:
    if c_cont < len(t_cols)-1:
        insert_to += '`' + i_value[0].replace(' ','_') + '`,'
    else:
        insert_to += '`' + i_value[0].replace(' ','_') + '`)'
    c_cont+=1

insert_values = '('
#for dv in df.values:
    #print(type(dv[0]))

for c_def in t_cols:
    val = ''
    if c_def[1].find('VARCHAR') == 0:
        val = "'" + df[c_def[0]] + "'"
        
    insert_values += val
    
    
insert_values += ');'
print(insert_values)
#print(insert_to)
#print(insert_values)
#for i in df.iteritems():
#    print(i[0])
#    result = []
#    if i[1].dtype == np.int64:
#        for j in i[1]:
#            print(j)


    

    




0       ('SANLE000''UHC''SANLE000''Single''Non-student...
1                                                     NaN
2                                                     NaN
3                                                     NaN
4                                                     NaN
5       ('VASMI000''SUPPERIOR''VASMI000''Single''Full ...
6       ('MARWE000''UHC MCARE / SUP MCAID''MARWE000''M...
7       ('MAREV000''UHC MCARE/SUP MCAID LAB''MAREV000'...
8                                                     NaN
9       ('CANRI000''AMBETTER''CANRI000''Married''Non-s...
10                                                    NaN
11      ('MARLU000''SUP MCARE/SUP MCAID''MARLU000''Mar...
12      ('VILLI000''SUP MCAID''VILLI000''Single''Non-s...
13                                                    NaN
14                                                    NaN
15      ('FRAAN000''MOLINA MCAID''FRAAN000''Single''No...
16                                                    NaN
17      ('SALJO000''UHC COMMERCIAL''SALJO000''Married'...
18      ('MARWE000''UHC MCARE/SUP MCAID LAB''MARWE000'...
19                                                    NaN
20      ('DELJO000''UHC MCAID''DELJO000''Single''Full ...
21      ('DELDA000''UHC MCAID''DELDA000''Single''Non-s...
22      ('DELNO000''UHC MCAID''DELNO000''Single''Full ...
23      ('ESPMA000''UHC MCAID''ESPMA000''Married''Non-...
24      ('ZAMSE000''MCARE / GEHA''ZAMSE000''Widowed''N...
25                                                    NaN
26                                                    NaN
27                                                    NaN
28                                                    NaN
29                                                    NaN
                              ...                        
2683                                                  NaN
2684                                                  NaN
2685                                                  NaN
2686    ('MARIM000''AMBETTER LAB''MARIM000''Married''N...
2687                                                  NaN
2688                                                  NaN
2689                                                  NaN
2690                                                  NaN
2691                                                  NaN
2692                                                  NaN
2693                                                  NaN
2694                                                  NaN
2695                                                  NaN
2696                                                  NaN
2697                                                  NaN
2698                                                  NaN
2699                                                  NaN
2700                                                  NaN
2701                                                  NaN
2702                                                  NaN
2703                                                  NaN
2704                                                  NaN
2705                                                  NaN
2706                                                  NaN
2707                                                  NaN
2708                                                  NaN
2709                                                  NaN
2710                                                  NaN
2711                                                  NaN
2712                                                  NaN
Name: Chart Number, Length: 2713, dtype: object

In [115]:

    

a = '10/29/1944'
a
b = '07/06/2017 8:56:57 AM'
d = re.compile('\d{2,2}/\d{2,2}/\d{4,4}')
print(d.match(a))
dwt = re.compile('\d{2,2}/\d{2,2}/\d{4,4} \d{1,2}:\d{2,2}:\d{2,2} (AM|PM)')
print(dwt.match(b))
None == True
type('') == str


    

    




<_sre.SRE_Match object; span=(0, 10), match='10/29/1944'>
<_sre.SRE_Match object; span=(0, 21), match='07/06/2017 8:56:57 AM'>






    
Out[115]:





True

In [ ]: