librelist archives

« back to archive

How to configure 'session' to execute raw SQL statements in Flask-SQLAlchemy

How to configure 'session' to execute raw SQL statements in Flask-SQLAlchemy

From:
Miquel Joy
Date:
2012-09-19 @ 09:42
Hello,

I'm quite new to Flask and SQLAlchemy, and I've found a nice way to start
with Flask-SQLAlchemy extension and maybe to go on.

I'm managing acceptably well with it but I need to execute some SQL
statements in an association table and despite it works OK in Python shell
it does not work in Flask session.

I'm following the suggestions from Flask-SQLAlchemy Tutorial:

>   from flask import Flask
>   from flask.ext.sqlalchemy import SQLAlchemy
>   app = Flask(__name__)
>   app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///tmp/mydb.db'
>   db = SQLAlchemy(app)

and to execute SQL statements I add to the header:

>   engine = db.get_engine(app)
>   Session = sessionmaker(bind=engine)
>   conn = engine.connect()
>   session = Session(bind=conn)

and further:

>   @app.route('/update', methods=['POST'])
>   def update_entry():
>   ...
>   ins = tags.insert().values(entry_id=id, tag_id=tag_id)
>   conn.execute(ins)
>   db.session.commit()
>   ...


When I launch the application I get the following error in Flask Debugger:

""ProgrammingError: (ProgrammingError) SQLite objects created in a thread
can only be used in that same thread.The object was created in thread id
11364 and this is thread id 580 None None"

Well it's quite clear that there is a problem with the sessions, because
there are two different threads.

Before getting in more detail into the issue of sessions in SQLAlchemy, I
want to know if its possible to execute SQL statements in Flask-SQLAlchemy
extension or should I think migrating to SQLAlchemy package. And if it's
easy to explain, how would you do?

Thanks in advance.

Miquel

Re: [flask] How to configure 'session' to execute raw SQL statements in Flask-SQLAlchemy

From:
Igor Davydenko
Date:
2012-09-19 @ 10:06
Hm, for me executing raw SQL with Flask-SQLAlchemy is easy as:

    def view():
        ...
        result = db.session.execute(raw_sql)
        db.session.commit()
        ...

On 19 September 2012 12:42, Miquel Joy <miquel.joy@gmail.com> wrote:
> Hello,
>
> I'm quite new to Flask and SQLAlchemy, and I've found a nice way to start
> with Flask-SQLAlchemy extension and maybe to go on.
>
> I'm managing acceptably well with it but I need to execute some SQL
> statements in an association table and despite it works OK in Python shell
> it does not work in Flask session.
>
> I'm following the suggestions from Flask-SQLAlchemy Tutorial:
>
>>   from flask import Flask
>>   from flask.ext.sqlalchemy import SQLAlchemy
>>   app = Flask(__name__)
>>   app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///tmp/mydb.db'
>>   db = SQLAlchemy(app)
>
> and to execute SQL statements I add to the header:
>
>>   engine = db.get_engine(app)
>>   Session = sessionmaker(bind=engine)
>>   conn = engine.connect()
>>   session = Session(bind=conn)
>
> and further:
>
>>   @app.route('/update', methods=['POST'])
>>   def update_entry():
>>   ...
>>   ins = tags.insert().values(entry_id=id, tag_id=tag_id)
>>   conn.execute(ins)
>>   db.session.commit()
>>   ...
>
>
> When I launch the application I get the following error in Flask Debugger:
>
> ""ProgrammingError: (ProgrammingError) SQLite objects created in a thread
> can only be used in that same thread.The object was created in thread id
> 11364 and this is thread id 580 None None"
>
> Well it's quite clear that there is a problem with the sessions, because
> there are two different threads.
>
> Before getting in more detail into the issue of sessions in SQLAlchemy, I
> want to know if its possible to execute SQL statements in Flask-SQLAlchemy
> extension or should I think migrating to SQLAlchemy package. And if it's
> easy to explain, how would you do?
>
> Thanks in advance.
>
> Miquel



-- 
Sincerely,
Igor Davydenko

Re: [flask] How to configure 'session' to execute raw SQL statements in Flask-SQLAlchemy

From:
Serge S. Koval
Date:
2012-09-19 @ 10:17
SQLAlchemy checks if requests are being made from the same thread.

Check following documentation:

http://docs.sqlalchemy.org/ru/latest/dialects/sqlite.html#using-a-memory-database-in-multiple-threads

Not sure how to pass these parameters when you use Flask-SQLAlchemy, but
maybe it is covered in documentation.

Serge.

On Wed, Sep 19, 2012 at 1:06 PM, Igor Davydenko
<playpauseandstop@gmail.com>wrote:

> Hm, for me executing raw SQL with Flask-SQLAlchemy is easy as:
>
>     def view():
>         ...
>         result = db.session.execute(raw_sql)
>         db.session.commit()
>         ...
>
> On 19 September 2012 12:42, Miquel Joy <miquel.joy@gmail.com> wrote:
> > Hello,
> >
> > I'm quite new to Flask and SQLAlchemy, and I've found a nice way to start
> > with Flask-SQLAlchemy extension and maybe to go on.
> >
> > I'm managing acceptably well with it but I need to execute some SQL
> > statements in an association table and despite it works OK in Python
> shell
> > it does not work in Flask session.
> >
> > I'm following the suggestions from Flask-SQLAlchemy Tutorial:
> >
> >>   from flask import Flask
> >>   from flask.ext.sqlalchemy import SQLAlchemy
> >>   app = Flask(__name__)
> >>   app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///tmp/mydb.db'
> >>   db = SQLAlchemy(app)
> >
> > and to execute SQL statements I add to the header:
> >
> >>   engine = db.get_engine(app)
> >>   Session = sessionmaker(bind=engine)
> >>   conn = engine.connect()
> >>   session = Session(bind=conn)
> >
> > and further:
> >
> >>   @app.route('/update', methods=['POST'])
> >>   def update_entry():
> >>   ...
> >>   ins = tags.insert().values(entry_id=id, tag_id=tag_id)
> >>   conn.execute(ins)
> >>   db.session.commit()
> >>   ...
> >
> >
> > When I launch the application I get the following error in Flask
> Debugger:
> >
> > ""ProgrammingError: (ProgrammingError) SQLite objects created in a thread
> > can only be used in that same thread.The object was created in thread id
> > 11364 and this is thread id 580 None None"
> >
> > Well it's quite clear that there is a problem with the sessions, because
> > there are two different threads.
> >
> > Before getting in more detail into the issue of sessions in SQLAlchemy, I
> > want to know if its possible to execute SQL statements in
> Flask-SQLAlchemy
> > extension or should I think migrating to SQLAlchemy package. And if it's
> > easy to explain, how would you do?
> >
> > Thanks in advance.
> >
> > Miquel
>
>
>
> --
> Sincerely,
> Igor Davydenko
>

Re: [flask] How to configure 'session' to execute raw SQL statements in Flask-SQLAlchemy

From:
Audrius Kažukauskas
Date:
2012-09-19 @ 10:27
On Wed, 2012-09-19 at 13:17:13 +0300, Serge S. Koval wrote:
> SQLAlchemy checks if requests are being made from the same thread.
> 
> Check following documentation:
> 
http://docs.sqlalchemy.org/ru/latest/dialects/sqlite.html#using-a-memory-database-in-multiple-threads
> 
> Not sure how to pass these parameters when you use Flask-SQLAlchemy, but
> maybe it is covered in documentation.

I think OP is messing things up by binding session to single connection:

> > >>   engine = db.get_engine(app)
> > >>   Session = sessionmaker(bind=engine)
> > >>   conn = engine.connect()
> > >>   session = Session(bind=conn)

Instead he should be using db.session as in Igor's example:

> >     def view():
> >         ...
> >         result = db.session.execute(raw_sql)
> >         db.session.commit()

-- 
Audrius Kažukauskas
http://neutrino.lt/

Re: [flask] How to configure 'session' to execute raw SQL statements in Flask-SQLAlchemy

From:
Miquel Joy
Date:
2012-09-19 @ 14:55
Wow, so simple! Thanks Igor!

For me it's time to start reading the SQLAlchemy Documentation to have a
wider view of the whole picture.

Regards,

Miquel



2012/9/19 Igor Davydenko <playpauseandstop@gmail.com>

> Hm, for me executing raw SQL with Flask-SQLAlchemy is easy as:
>
>     def view():
>         ...
>         result = db.session.execute(raw_sql)
>         db.session.commit()
>         ...
>
> On 19 September 2012 12:42, Miquel Joy <miquel.joy@gmail.com> wrote:
> > Hello,
> >
> > I'm quite new to Flask and SQLAlchemy, and I've found a nice way to start
> > with Flask-SQLAlchemy extension and maybe to go on.
> >
> > I'm managing acceptably well with it but I need to execute some SQL
> > statements in an association table and despite it works OK in Python
> shell
> > it does not work in Flask session.
> >
> > I'm following the suggestions from Flask-SQLAlchemy Tutorial:
> >
> >>   from flask import Flask
> >>   from flask.ext.sqlalchemy import SQLAlchemy
> >>   app = Flask(__name__)
> >>   app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///tmp/mydb.db'
> >>   db = SQLAlchemy(app)
> >
> > and to execute SQL statements I add to the header:
> >
> >>   engine = db.get_engine(app)
> >>   Session = sessionmaker(bind=engine)
> >>   conn = engine.connect()
> >>   session = Session(bind=conn)
> >
> > and further:
> >
> >>   @app.route('/update', methods=['POST'])
> >>   def update_entry():
> >>   ...
> >>   ins = tags.insert().values(entry_id=id, tag_id=tag_id)
> >>   conn.execute(ins)
> >>   db.session.commit()
> >>   ...
> >
> >
> > When I launch the application I get the following error in Flask
> Debugger:
> >
> > ""ProgrammingError: (ProgrammingError) SQLite objects created in a thread
> > can only be used in that same thread.The object was created in thread id
> > 11364 and this is thread id 580 None None"
> >
> > Well it's quite clear that there is a problem with the sessions, because
> > there are two different threads.
> >
> > Before getting in more detail into the issue of sessions in SQLAlchemy, I
> > want to know if its possible to execute SQL statements in
> Flask-SQLAlchemy
> > extension or should I think migrating to SQLAlchemy package. And if it's
> > easy to explain, how would you do?
> >
> > Thanks in advance.
> >
> > Miquel
>
>
>
> --
> Sincerely,
> Igor Davydenko
>