sqlite - Python SQlite3 syntax error - cant figure out whats wrong -

sqlite - Python SQlite3 syntax error - cant figure out whats wrong -

i have variation of code below i've re-written several times , same error. operationalerror: near ".": syntax error googled , removed primary key , checked field names started lower case letter , had no spaces. i'm finish beginner sqlite help much appreciated.

etf = {'': '1', '#_of_holdings': '31', '%_in_top_10': '46.32%', '1_week': '-2.14%', '1_year': '3.86%', '200-day': '10.53%', '3_year': '39.32%', '4_week': '-6.65%', '5_year': 'n/a', 'annual_dividend_rate': '$0.18', 'annual_dividend_yield_%': '0.49%', 'assets': '13770', 'avg._vol': '4233', 'beta': '0.99', 'commission_free': 'not available', 'concentration': 'c', 'dividend': '$0.02', 'dividend_date': '2014-09-24', 'er': '1.25%', 'etfdb_category': 'global equities', 'expenses': 'c', 'inception': '2010-07-20', 'inverse': 'no', 'leveraged': 'no', 'liquidity': 'c', 'lower_bollinger': '$36.16', 'lt_cap_gains': '15%', 'name': 'wcm/bny mellon focused growth adr etf', 'overall': 'c', 'p/e_ratio': '23.58', 'performance': 'a-', 'price': '36.10', 'resistance_1': '$36.10', 'rsi': '33', 'scoredividend': 'c', 'st_cap_gains': '35%', 'support_1': '$36.10', 'symbol': 'aadr', 'tax_form': '1099', 'upper_bollinger': '$38.80', 'value': '152.8113', 'volatility': 'c', 'ytd': '-3.23%'} import sqlite3 conn = sqlite3.connect('sample.sqlite') cursor = conn.cursor() cursor.execute('''create table static_data (p4w real, tax_form text, resistance_1 real, dividend_date date, \ expenses_rating text, avg_vol real, p5y real, scoredividend text, concentration text, expense_ratio real, \ inverse text, upper_bollinger real, p_e_ratio real, leveraged text, performance_rating text, pytd real, \ volatility text, cost real, rsi real, lt_cap_gains real, holdings real, symbol real, overall_rating text,\ p1y real, beta real, p3y real, dividend_yield real, value real, inception date, dividend real, in_top_10 real,\ assets real, name text, st_cap_gains real, etfdb_category real, annual_dividend_rate real, support_1 real, \ lower_bollinger real, dma200 real, liquidity text, p1w real, commission_free text)''') conn.commit() fieldnames = ['p4w','tax_form','resistance_1','dividend_date','expenses_rating','avg_vol', 'p5y','scoredividend','concentration','expense_ratio','inverse','upper_bollinger', 'p_e_ratio','leveraged','performance_rating','pytd','volatility','price','rsi', 'lt_cap_gains','holdings','symbol','overall_rating','p1y','beta','p3y','dividend_yield', 'value','inception','dividend','in_top_10','assets','name','st_cap_gains','etfdb_category', 'annual_dividend_rate','support_1','lower_bollinger','dma200','liquidity','p1w', 'commission_free'] dictnames = ['4_week','tax_form','resistance_1','dividend_date','expenses','avg._vol', '5_year','scoredividend','concentration','er','inverse','upper_bollinger','p/e_ratio', 'leveraged','performance','ytd','volatility','price','rsi','lt_cap_gains','#_of_holdings', 'symbol','overall','1_year','beta','3_year','annual_dividend_yield_%','value','inception', 'dividend','%_in_top_10','assets','name','st_cap_gains','etfdb_category', 'annual_dividend_rate','support_1','lower_bollinger','200-day','liquidity','1_week', 'commission_free'] fieldmap = zip(fieldnames,dictnames) sql_string = '''insert static_data (%(colnames)s) values (%(dictfields)s);''' colnames = ','.join(fieldnames) dictnames = [":"+i in dictnames] dictfields = ','.join(dictnames) sql_string_etf = sql_string % dict(colnames=colnames,dictfields=dictfields) cursor.execute(sql_string_etf, etf)

full traceback below

traceback (most recent phone call last): file "/usr/lib/python2.7/dist-packages/ipython/core/interactiveshell.py", line 2820, in run_code exec code_obj in self.user_global_ns, self.user_ns file "<ipython-input-41-64b4fceab545>", line 36, in <module> cursor.execute(sql_string_etf, etf) operationalerror: near ".": syntax error

sql_string_etf evaluates to

'insert static_data (p4w,tax_form,resistance_1,dividend_date,expenses_rating,avg_vol,p5y,scoredividend,concentration,expense_ratio,inverse,upper_bollinger,p_e_ratio,leveraged,performance_rating,pytd,volatility,price,rsi,lt_cap_gains,holdings,symbol,overall_rating,p1y,beta,p3y,dividend_yield,value,inception,dividend,in_top_10,assets,name,st_cap_gains,etfdb_category,annual_dividend_rate,support_1,lower_bollinger,dma200,liquidity,p1w,commission_free) values (:4_week,:tax_form,:resistance_1,:dividend_date,:expenses,:avg._vol,:5_year,:scoredividend,:concentration,:er,:inverse,:upper_bollinger,:p/e_ratio,:leveraged,:performance,:ytd,:volatility,:price,:rsi,:lt_cap_gains,:#_of_holdings,:symbol,:overall,:1_year,:beta,:3_year,:annual_dividend_yield_%,:value,:inception,:dividend,:%_in_top_10,:assets,:name,:st_cap_gains,:etfdb_category,:annual_dividend_rate,:support_1,:lower_bollinger,:200-day,:liquidity,:1_week,:commission_free);'

there invalid parameter names:

class="lang-sql prettyprint-override">insert static_data(...) values (...,:avg._vol,:5_year,...,:p/e_ratio,...,:#_of_holdings,...,:1_year,..., :3_year,:annual_dividend_yield_%,...,:%_in_top_10,...,:200-day,..., :1_week,:commission_free);

parameter names must valid sql identifiers, i.e., must not begin digit, , cannot contain special characters expect _.

you have valid names table field names; utilize them instead of dictnames (and in etf dictionary).

python sqlite