sqlalchemy

Does SQLAlchemy have an equivalent of Django’s get_or_create?

by

I want to get an object from the database if it already exists (based on provided parameters) or create it if it does not. Django’s get_or_create (or source) does this. Is there an equivalent shortcut in SQLAlchemy? I’m currently writing it out explicitly like this: def get_or_create_instrument(session, serial_number): instrument = session.query(Instrument).filter_by(serial_number=serial_number).first() if instrument: return instrument … Read more

How to delete a record by id in Flask-SQLAlchemy

by

I have users table in my MySql database. This table has id, name and age fields. How can I delete some record by id? Now I use the following code: user = User.query.get(id) db.session.delete(user) db.session.commit() But I don’t want to make any query before delete operation. Is there any way to do this? I know, … Read more

How to update SQLAlchemy row entry?

by

Assume table has three columns: username, password and no_of_logins. When user tries to login, it’s checked for an entry with a query like user = User.query.filter_by(username=form.username.data).first() If password matches, he proceeds further. What I would like to do is count how many times the user logged in. Thus whenever he successfully logs in, I would … Read more

SQLAlchemy: engine, connection and session difference

by

I use SQLAlchemy and there are at least three entities: engine, session and connection, which have execute method, so if I e.g. want to select all records from table I can do this engine.execute(select([table])).fetchall() and this connection.execute(select([table])).fetchall() and even this session.execute(select([table])).fetchall() – the results will be the same. As I understand it, if someone uses … Read more

sqlalchemy unique across multiple columns

by

Let’s say that I have a class that represents locations. Locations “belong” to customers. Locations are identified by a unicode 10 character code. The “location code” should be unique among the locations for a specific customer. The two below fields in combination should be unique customer_id = Column(Integer,ForeignKey(‘customers.customer_id’) location_code = Column(Unicode(10)) So if i have … Read more

SQLAlchemy default DateTime

by

This is my declarative model: import datetime from sqlalchemy import Column, Integer, DateTime from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Test(Base): __tablename__ = ‘test’ id = Column(Integer, primary_key=True) created_date = DateTime(default=datetime.datetime.utcnow) However, when I try to import this module, I get this error: Traceback (most recent call last): File “<stdin>”, line 1, in <module> … Read more

How to serialize SqlAlchemy result to JSON?

by

Django has some good automatic serialization of ORM models returned from DB to JSON format. How to serialize SQLAlchemy query result to JSON format? I tried jsonpickle.encode but it encodes query object itself. I tried json.dumps(items) but it returns TypeError: <Product(‘3’, ‘some name’, ‘some desc’)> is not JSON serializable Is it really so hard to … Read more

Using OR in SQLAlchemy

by

I’ve looked through the docs and I cant seem to find out how to do an OR query in SQLAlchemy. I just want to do this query. SELECT address FROM addressbook WHERE city=’boston’ AND (lastname=”bulger” OR firstname=”whitey”) Should be something like addr = session.query(AddressBook).filter(City == “boston”).filter(????) 6 Answers 6

How to execute raw SQL in Flask-SQLAlchemy app

by

How do you execute raw SQL in SQLAlchemy? I have a python web app that runs on flask and interfaces to the database through SQLAlchemy. I need a way to run the raw SQL. The query involves multiple table joins along with Inline views. I’ve tried: connection = db.session.connection() connection.execute( <sql here> ) But I … Read more