CODEWITHSUNDEEP
pythonsqlitepandas
nicholas.reichel
nicholas.reichel

Reputation: 2270

Pandas to_sql returning error due to column names with sqlite?

I'm trying to store stock screens in an sqlite database but when I use screen.to_sql() from pandas it returns:

Error: near "/": syntax error

If I replace all the slashes with ' over ' then I get the following:

Error: unrecognized token: "]"

I'm confused because this code is nearly identical to the one I used for a mysql database, can anyone see why sqlite does not like this?

Here is the full query as created by pandas.

CREATE TABLE raw_screens (
              [Company_Name] TEXT,


[Ticker] TEXT,
  [%_Ratio_of_Q1/prior_Yr_Q1_Actual_Q(-3)] TEXT,
  [Price/Book] TEXT,
  [Price/Sales] TEXT
  [Current_ROE_(TTM)] TEXT,
  [Current_ROI_(TTM)] TEXT,
  [ROI_(5_Yr_Avg)] TEXT,
  [Current_ROA_(TTM)] TEXT,
  [ROA_(5_Yr_Avg)] TEXT,
  [Market_Value/#_Analysts] TEXT,
  [Annual_Sales] TEXT,
  [Cost_of_Goods_Sold] TEXT,
  [EBITDA_] TEXT,
  [Price/Cash_Flow] TEXT,
  [PEG_Ratio] TEXT,
  [P/E_(F2)] TEXT,
  [%_Ratio_of_Q1/Q0] TEXT,
  [This_Yr`s_Est.d_Growth_(F(1)/F(0))] TEXT,
  [Last_Yrs_Growth_(F[0]_/_F_[-1])] TEXT,
  [%_Change_EPS_(F(-1)/F(-2))] TEXT,
  [Long-Term_Growth_Consensus_Est.] TEXT,
  [5_Yr._Hist._EPS_Growth] TEXT,
  [Sales_Growth_F(0)/F(-1)] TEXT,
  [5_Yr_Historical_Sales_Growth] TEXT,
  [F(1)_Consensus_Sales_Est._(Mil.)] TEXT,
  [Q(1)_Consensus_Sales_Est._(Mil.)] TEXT,
  [P/E_(Trailing_12_Months)] TEXT,
  [EBIT_] TEXT,
  [Pretax_Income_] TEXT,
  [Preferred_Equity] TEXT,
  [Current_Liabilities_] TEXT,
  [Long_Term_Debt_] TEXT,
  [Current_Assets_] TEXT,
  [Intangibles] TEXT,
  [Inventory_] TEXT,
  [Receivables_] TEXT,
  [Debt/Total_Capital] TEXT,
  [Debt/Equity_Ratio] TEXT,
  [Current_Ratio] TEXT,
  [Quick_Ratio] TEXT,
  [Book_Value] TEXT,
  [Asset_Utilization] TEXT,
  [Inventory_Turnover] TEXT,
  [Net_Income_] TEXT,
  [Cash_Flow_] TEXT,
  [Net_Income_Growth_F(0)/F(-1)] TEXT,
  [12_Mo._Net_Income_Current/Last_%] TEXT,
  [12_Mo._Net_Income_Current-1Q/Last-1Q_%] TEXT,
  [5_Yr_Div._Yield_%] TEXT,
  [5_Yr_Hist._Div._Growth_%] TEXT,
  [Dividend_] TEXT,
  [Net_Margin] TEXT,
  [Operating_Margin_12_Mo] TEXT,
  [Turnover] TEXT,
  [Cash_Ratio] TEXT,
  [Rank_in_Industry_(of_ABR)] TEXT,
  [%_Price_Change_(YTD)] TEXT,
  [%_Price_Change_(4_Weeks)] TEXT,
  [%_Price_Change_(1_Week)] TEXT,
  [Beta] TEXT,
  [Price_as_a_%_of_52_Wk_H-L_Range] TEXT,
  [Last_Close] TEXT,
  [Current_Avg_Broker_Rec] TEXT,
  [%_Rating_Strong_Buy_or_Buy] TEXT,
  [%_Rating_Strong_Sell_or_Sell] TEXT,
  [Industry_Rank_(of_ABR)] TEXT,
  [52_Week_Low] TEXT,
  [%_Price_Change_(12_Weeks)] TEXT,
  [Relative_Price_Change] TEXT,
  [Q0_Consensus_Est._(last_completed_fiscal_Qtr)] TEXT,
  [52_Week_High] TEXT,
  [Market_Cap_] TEXT,
  [Last_EPS_Surprise_(%)] TEXT,
  [P/E_(F1)] TEXT,
  [%_Rating_Change_-_4_Weeks] TEXT,
  [%_Change_F1_Est._(4_weeks)] TEXT,
  [Div._Yield_%] TEXT,
  [Avg_Volume] TEXT,
  [Shares_Outstanding_] TEXT,
  [Change_in_Avg_Rec_] TEXT,
  [%_Rating_Hold] TEXT,
  [%_Change_Q2_Est._(4_weeks)] TEXT,
  [%_Change_Q1_Est._(4_weeks)] TEXT,
  [%_Change_F2_Est._(4_weeks)] TEXT,
  [%_Change_LT_Growth_Est._(4_weeks)] TEXT,
  [Q1_Consensus_Est._] TEXT,
  [St._Dev._Q1_/_Q1_Consensus] TEXT,
  [Q2_Consensus_Est._(next_fiscal_Qtr)] TEXT,
  [St._Dev._Q2_/_Q2_Consensus] TEXT,
  [F0_Consensus_Est.] TEXT,
  [F1_Consensus_Est.] TEXT,
  [St._Dev._F1_/_F1_Consensus] TEXT,
  [%_Change_Q0_Est._(4_weeks)] TEXT,
  [12_Mo_Trailing_EPS] TEXT,
  [Last_Reported_Fiscal_Yr_] INTEGER,
  [%_Rating_Upgrades_] TEXT,
  [%_Rating_Downgrades_] TEXT,
  [Common_Equity] TEXT,
  [Average_Target_Price] TEXT,
  [Previous_EPS_Surprise_(%)] TEXT,
  [Avg_EPS_Surprise_(Last_4_Qtrs)] TEXT,
  [Actual_EPS_used_in_Surprise_($/sh)] TEXT,
  [Last_Qtr_EPS] TEXT,
  [Last_Reported_Qtr_] TEXT,
  [Last_Yr's_EPS_(F0)_Before_NRI] TEXT,
  [F2_Consensus_Est.] TEXT,
  [Date] TEXT

                  );

Upvotes: 0

Views: 826

Answers (1)

CL.
CL.

Reputation: 180080

[] quotes cannot be nested.

You should use a quote character that can be escaped, such as " (which would have to be doubled):

...,
"my silly name" TEXT,
"my even ""sillier"" name with quotes" TEXT,
...

(In this case, you don't even have " in the names.)

Upvotes: 1

Related Questions