Questions tagged [sqlalchemy]
SQLAlchemy is a Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.
23,518
questions
676
votes
8
answers
263k
views
SQLAlchemy: What's the difference between flush() and commit()?
What the difference is between flush() and commit() in SQLAlchemy?
I've read the docs, but am none the wiser - they seem to assume a pre-understanding that I don't have.
I'm particularly interested ...
- 92k
655
votes
6
answers
645k
views
SQLAlchemy ORDER BY DESCENDING?
How can I use ORDER BY descending in a SQLAlchemy query like the following?
This query works, but returns them in ascending order:
query = (model.Session.query(model.Entry)
.join(model....
- 92k
433
votes
46
answers
544k
views
How to convert SQLAlchemy row object to a Python dict?
Is there a simple way to iterate over column name and value pairs?
My version of SQLAlchemy is 0.5.6
Here is the sample code where I tried using dict(row):
import sqlalchemy
from sqlalchemy import *
...
- 87.4k
417
votes
5
answers
294k
views
Difference between filter and filter_by in SQLAlchemy
Could anyone explain the difference between filter and filter_by functions in SQLAlchemy?
Which one should I be using?
- 77.5k
413
votes
8
answers
400k
views
SQLAlchemy IN clause
I'm trying to do this query in sqlalchemy
SELECT id, name FROM user WHERE id IN (123, 456)
I would like to bind the list [123, 456] at execution time.
- 7,974
338
votes
10
answers
435k
views
How to execute raw SQL in Flask-SQLAlchemy app
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 ...
- 3,493
317
votes
13
answers
440k
views
SQLAlchemy default DateTime
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):
...
- 24.5k
310
votes
6
answers
320k
views
Using OR in SQLAlchemy
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' ...
- 7,200
310
votes
37
answers
363k
views
How to serialize SqlAlchemy result to JSON?
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 ...
- 7,085
302
votes
11
answers
285k
views
SQLAlchemy: print the actual query
I'd really like to be able to print out valid SQL for my application, including values, rather than bind parameters, but it's not obvious how to do this in SQLAlchemy (by design, I'm fairly sure).
...
- 38k
292
votes
3
answers
180k
views
SQLAlchemy: engine, connection and session difference
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 on the Engine level:
...
- 3,848
266
votes
4
answers
168k
views
sqlalchemy unique across multiple columns
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 ...
- 5,581
258
votes
7
answers
439k
views
How to update SQLAlchemy row entry?
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)....
- 46.1k
234
votes
8
answers
182k
views
sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres
I'm trying to connect to a Postgres database with SQLAlchemy. I've installed psycopg2. However, I get the error sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres. How ...
- 2,341
226
votes
4
answers
283k
views
How to delete a record by id in Flask-SQLAlchemy
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)
...
- 5,436
218
votes
10
answers
101k
views
Does SQLAlchemy have an equivalent of Django's get_or_create?
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 ...
- 75.7k
212
votes
12
answers
298k
views
Bulk insert with SQLAlchemy ORM
Is there any way to get SQLAlchemy to do a bulk insert rather than inserting each individual object. i.e.,
doing:
INSERT INTO `foo` (`bar`) VALUES (1), (2), (3)
rather than:
INSERT INTO `foo` (`...
- 3,689
208
votes
9
answers
251k
views
Flask SQLAlchemy query, specify column names
How do I specify the column that I want in my query using a model (it selects all columns by default)? I know how to do this with the sqlalchmey session: session.query(self.col1), but how do I do it ...
- 4,560
204
votes
8
answers
91k
views
Strange SQLAlchemy error message: TypeError: 'dict' object does not support indexing
I am using hand crafted SQL to fetch data from a PG database, using SqlAlchemy. I am trying a query which contains the SQL like operator '%' and that seems to throw SqlAlcjhemy through a loop:
sql = "...
- 66.8k
191
votes
10
answers
152k
views
sqlalchemy flush() and get inserted id?
I want to do something like this:
f = Foo(bar='x')
session.add(f)
session.flush()
# do additional queries using f.id before commit()
print f.id # should be not None
session.commit()
But f.id is ...
- 21.3k
190
votes
9
answers
302k
views
Flask-SQLalchemy update a row's information
How can I update a row's information?
For example I'd like to alter the name column of the row that has the id 5.
- 3,635
188
votes
10
answers
171k
views
SQLAlchemy: cascade delete
I must be missing something trivial with SQLAlchemy's cascade options because I cannot get a simple cascade delete to operate correctly -- if a parent element is a deleted, the children persist, with ...
- 50.2k
185
votes
3
answers
207k
views
sqlalchemy IS NOT NULL select
How can I add the filter as in SQL to select values that are NOT NULL from a certain column ?
SELECT *
FROM table
WHERE YourColumn IS NOT NULL;
How can I do the same with SQLAlchemy filters?
...
- 3,701
184
votes
8
answers
294k
views
How to write DataFrame to postgres table
There is DataFrame.to_sql method, but it works only for mysql, sqlite and oracle databases. I cant pass to this method postgres connection or sqlalchemy engine.
- 3,286
180
votes
13
answers
196k
views
How do I get a raw, compiled SQL query from a SQLAlchemy expression?
I have a SQLAlchemy query object and want to get the text of the compiled SQL statement, with all its parameters bound (e.g. no %s or other variables waiting to be bound by the statement compiler or ...
- 4,944
179
votes
11
answers
186k
views
How can I use UUIDs in SQLAlchemy?
Is there a way to define a column (primary key) as a UUID in SQLAlchemy if using PostgreSQL (Postgres)?
- 37.2k
177
votes
15
answers
152k
views
SQLAlchemy ORM conversion to pandas DataFrame
Is there a solution converting a SQLAlchemy <Query object> to a pandas DataFrame?
Pandas has the capability to use pandas.read_sql but this requires use of raw SQL. I have two reasons for ...
- 3,762
174
votes
3
answers
212k
views
Group by & count function in sqlalchemy
I want a "group by and count" command in sqlalchemy. How can I do this?
- 6,960
173
votes
16
answers
304k
views
ImportError: No module named MySQLdb
I am referring the following tutorial to make a login page for my web application.
http://code.tutsplus.com/tutorials/intro-to-flask-signing-in-and-out--net-29982
I am having issue with the database.
...
- 1,769
173
votes
6
answers
158k
views
How to create a new database using SQLAlchemy?
Using SQLAlchemy, an Engine object is created like this:
from sqlalchemy import create_engine
engine = create_engine("postgresql://localhost/mydb")
Accessing engine fails if the database specified ...
- 4,297
170
votes
17
answers
199k
views
SQLAlchemy - Getting a list of tables
I couldn't find any information about this in the documentation, but how can I get a list of tables created in SQLAlchemy?
I used the class method to create the tables.
- 3,133
169
votes
3
answers
109k
views
How do I execute inserts and updates in an Alembic upgrade script?
I need to alter data during an Alembic upgrade.
I currently have a 'players' table in a first revision:
def upgrade():
op.create_table('player',
sa.Column('id', sa.Integer(), nullable=...
- 4,509
164
votes
4
answers
140k
views
SQLAlchemy query to return only n results?
I have been googling and reading through the SQLAlchemy documentation but haven't found what I am looking for.
I am looking for a function in SQLAlchemy that limits the number of results returned by ...
- 7,784
161
votes
2
answers
59k
views
SQLAlchemy: Creating vs. Reusing a Session
Just a quick question: SQLAlchemy talks about calling sessionmaker() once but calling the resulting Session() class each time you need to talk to your DB. For me that means the second I would do my ...
- 7,354
159
votes
4
answers
191k
views
SQLAlchemy: how to filter date field?
Here is model:
class User(Base):
...
birthday = Column(Date, index=True) #in database it's like '1987-01-17'
...
I want to filter between two dates, for example to choose all users in ...
- 10.8k
156
votes
6
answers
274k
views
Efficiently updating database using SQLAlchemy ORM
I'm starting a new application and looking at using an ORM -- in particular, SQLAlchemy.
Say I've got a column 'foo' in my database and I want to increment it. In straight sqlite, this is easy:
db =...
- 41.7k
152
votes
2
answers
145k
views
Debugging (displaying) SQL command sent to the db by SQLAlchemy
I have an ORM class called Person, which wraps around a person table:
After setting up the connection to the db etc, I run the statement:
people = session.query(Person).all()
The person table does ...
- 16.6k
151
votes
8
answers
96k
views
Case Insensitive Flask-SQLAlchemy Query
I'm using Flask-SQLAlchemy to query from a database of users; however, while
user = models.User.query.filter_by(username="ganye").first()
will return
<User u'ganye'>
doing
user = models....
- 2,551
147
votes
6
answers
52k
views
flask-sqlalchemy or sqlalchemy
I am new in both flask and sqlalchemy, I just start working on a flask app, and I am using sqlalchemy for now. I was wondering if there is any significant benefit I can get from using flask-sqlalchemy ...
- 1,963
146
votes
5
answers
195k
views
Flask-SQLAlchemy how to delete all rows in a single table
How do I delete all rows in a single table using Flask-SQLAlchemy?
Looking for something like this:
>>> users = models.User.query.all()
>>> models.db.session.delete(users)
# but ...
- 8,863
146
votes
7
answers
135k
views
SqlAlchemy - Filtering by Relationship Attribute
I don't have much experience with SQLAlchemy and I have a problem, which I can't solve. I tried searching and I tried a lot of code.
This is my Class (reduced to the most significant code):
class ...
- 1,463
146
votes
1
answer
91k
views
When do I need to use sqlalchemy back_populates?
When I try SQLAlchemy Relation Example following this guide: Basic Relationship Patterns
I have this code
#!/usr/bin/env python
# encoding: utf-8
from sqlalchemy import create_engine
from sqlalchemy ...
- 1,774
141
votes
15
answers
198k
views
jsonify a SQLAlchemy result set in Flask [duplicate]
I'm trying to jsonify a SQLAlchemy result set in Flask/Python.
The Flask mailing list suggested the following method http://librelist.com/browser//flask/2011/2/16/jsonify-sqlalchemy-pagination-...
- 4,426
141
votes
5
answers
312k
views
sqlalchemy: how to join several tables by one query?
I have the following SQLAlchemy mapped classes:
class User(Base):
__tablename__ = 'users'
email = Column(String, primary_key=True)
name = Column(String)
class Document(Base):
...
- 1,883
139
votes
8
answers
189k
views
Best way to do enum in Sqlalchemy?
I'm reading about sqlalchemy and I saw following code:
employees_table = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('name', String(50)),
Column('...
- 12.7k
138
votes
12
answers
136k
views
How to do an upsert with SqlAlchemy?
I have a record that I want to exist in the database if it is not there, and if it is there already (primary key exists) I want the fields to be updated to the current state. This is often called an ...
- 11k
137
votes
5
answers
232k
views
sqlalchemy filter multiple columns
How do I combine two columns and apply filter? For example, I want to search in both the "firstname" and "lastname" columns at the same time. Here is how I have been doing it if searching only one ...
- 6,165
136
votes
15
answers
55k
views
Is it possible to store the alembic connect string outside of alembic.ini?
I'm using Alembic with SQLAlchemy. With SQLAlchemy, I tend to follow a pattern where I don't store the connect string with the versioned code. Instead I have file secret.py that contains any ...
- 65k
134
votes
6
answers
170k
views
How to query database by id using SqlAlchemy?
I need to query a SQLAlchemy database by its id something similar to
User.query.filter_by(username='peter')
but for id. How do I do this? [Searching over Google and SO didn't help]
user507220
132
votes
12
answers
194k
views
SQLAlchemy equivalent to SQL "LIKE" statement
A tags column has values like "apple banana orange" and "strawberry banana lemon". I want to find the SQLAlchemy equivalent statement to
SELECT * FROM table WHERE tags LIKE "%banana%";
What should I ...
- 1,652