Python SQlite3语法错误无法找出错误所在

Question

我有一个下面的代码，我已经写了好几次的变化，得到相同的错误。OperationalError: near ".": syntax error我在谷歌上搜索并删除了一个主键，检查所有字段名是否都以小写字母开头，并且没有空格。我是一个与SQLite完全初学者，所以任何帮助将非常感谢。你知道吗

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)

下面是完整的回溯

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计算结果为

'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 1

存在一些无效的参数名称：

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);

参数名必须是有效的SQL标识符，即不能以数字开头，并且不能包含除_之外的任何特殊字符。你知道吗

您已经有了表字段名的有效名称；只需使用它们而不是dictnames（在ETF字典中）。你知道吗