Error while creating table in Python Sqlite

Question

I am running some query and I want to store the output of the query as new table. I am using Jupyter notebook where I have connected to a sqlite database which has the table I am using in the query.

sql2="""

create table sales_fact_unique  as
(
 with min_leads as 
 ( 
  select LeadId, min(BookingCreateDate)as FirstBookingDate
  from 
   sales_fact
    where GrossRevenue <>0
  group by
   LeadId
) 

 select a.LeadId, a.FirstBookingDate, b.ArrivalDate,
  b.DepartureDate,b.DealWonSalesPersonId,
  b.GrossRevenue,b.EngineFromFinalURL,b.CampaignFromFinalUrl
 from 
  min_leads as a
inner join 
 (select * from sales_fact where grossrevenue <>0 ) as b
 on a.leadid=b.leadid 
 and a.FirstBookingDate=b.BookingCreateDate
)
;

"""


conn.execute(sql2)
conn.commit()

Here is the error I am getting.

<ipython-input-42-bc72021206b2> in <module>()
     45 
     46 
---> 47 c.execute(sql2)
     48 conn.commit()
     49 

OperationalError: near "(": syntax error

Any idea?

You really should use a ORM such as sqlalchemy. It will make your life so much simpler. — Mantis
– Mantis, Commented Jan 25, 2016 at 23:21
You have the correct amount of opening and closing () symbols, but I think you need an extra closing one and opening one somewhere. — Open AI - Opting Out
– Open AI - Opting Out, Commented Jan 25, 2016 at 23:40
@Manish its not really something I can sum up in a post. It works the same with all versions of sql. Read the docs, they are really good and will be well with your time — Mantis
– Mantis, Commented Jan 25, 2016 at 23:42
The error is only coming when I use create table syntax. Its not there without that. But I do need to create a new table. And am not sure what is wrong here. Everything matches. — Baktaawar
– Baktaawar, Commented Jan 26, 2016 at 5:22

CL. · Accepted Answer · 2016-01-26 09:00:57Z

1

CREATE TABLE ... AS ... requires a query, not a subquery, so you have to remove the outermost pair of parentheses:

> CREATE TABLE t AS (SELECT 1);
Error: near "(": syntax error
> CREATE TABLE t AS SELECT 1;

answered Jan 26, 2016 at 9:00

CL.

182k18 gold badges241 silver badges282 bronze badges

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

Collectives™ on Stack Overflow

Error while creating table in Python Sqlite

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related