0

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

                  );
5
  • is this the cause: [This_Yr`s_Est.d_Growth_(F(1)/F(0))] TEXT it looks like this maybe escaping the string Commented Sep 24, 2014 at 15:12
  • Taking care of both the / and the ` we end up with [This_Yr's_Est.d_Growth_(F(1)_over_F(0))] TEXT, but it still returns the error concerning the ]. Thanks though! Commented Sep 24, 2014 at 15:18
  • I'd do a divide and conquer to find the offending column line, so remove half the columns, parse again, if it succeeds, try the other half, on failure keep halving until you find the line. Fix it and restart again Commented Sep 24, 2014 at 15:20
  • I'll probably just make a quick python screen to try to make a table with each line individually. Thanks! Commented Sep 24, 2014 at 15:22
  • even " may break now depending on the database backend, I have created an issue here and the fix is to properly quote and escape things. Actually the issue is about table names, but similar. Commented Feb 1, 2015 at 21:35

1 Answer 1

1

[] 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.)

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.