Python SQlite3 syntax error - cant figure out whats wrong
Question: I have a variation of the code below which I've re-written several times and
get the same error. `OperationalError: near ".": syntax error` which I googled
and removed a primary key and checked that all field names started with a
lower case letter and had no spaces. I'm a complete beginner with SQLite so
any help would be very 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, price 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 INTO static_data (%(colnames)s) values (%(dictfields)s);'''
    colnames = ','.join(fieldnames)
    dictnames = [":"+i for i in dictnames]
    dictfields = ','.join(dictnames)
    SQL_STRING_ETF = SQL_STRING % dict(colnames=colnames,dictfields=dictfields)
    cursor.execute(SQL_STRING_ETF, ETF)
    

Full traceback is below

    
    
    Traceback (most recent 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 INTO 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);'
    


Answer: There are some invalid parameter names:

    
    
    INSERT INTO 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 be valid SQL identifiers, i.e., must not begin with a
digit, and cannot contain any special characters expect `_`.

You already have valid names for the table field names; just use them instead
of `dictnames` (and in the `ETF` dictionary).

