Questions tagged [sqlalchemy]
SQLAlchemy is a Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.
sqlalchemy
23,637
questions
681
votes
9
answers
272k
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 ...
- 92.6k
662
votes
6
answers
660k
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....
- 92.6k
447
votes
46
answers
564k
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.9k
420
votes
5
answers
301k
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?
- 78k
417
votes
8
answers
409k
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.
- 8,054
341
votes
10
answers
446k
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,523
320
votes
14
answers
455k
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
314
votes
37
answers
373k
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,115
313
votes
6
answers
329k
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,270
310
votes
11
answers
297k
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).
...
- 38.1k
300
votes
3
answers
189k
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,928
269
votes
4
answers
172k
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,611
261
votes
7
answers
452k
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.4k
242
votes
8
answers
192k
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,421
227
votes
4
answers
289k
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,446
220
votes
10
answers
102k
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 ...
- 76.1k
217
votes
13
answers
309k
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,739
209
votes
9
answers
254k
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,580
205
votes
8
answers
92k
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 = "...
- 67.3k
191
votes
10
answers
177k
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.3k
191
votes
10
answers
157k
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.4k
190
votes
9
answers
305k
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,645
188
votes
3
answers
213k
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,741
186
votes
8
answers
302k
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,316
185
votes
13
answers
205k
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 ...
- 5,014
182
votes
12
answers
194k
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.6k
180
votes
15
answers
157k
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,822
175
votes
17
answers
308k
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,789
175
votes
7
answers
162k
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,317
174
votes
3
answers
216k
views
Group by & count function in sqlalchemy
I want a "group by and count" command in sqlalchemy. How can I do this?
- 6,990
171
votes
17
answers
205k
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,143
170
votes
3
answers
114k
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,559
165
votes
2
answers
62k
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,404
164
votes
4
answers
144k
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,804
160
votes
4
answers
194k
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.9k
159
votes
6
answers
282k
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.9k
154
votes
2
answers
151k
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.8k
154
votes
8
answers
98k
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,611
149
votes
1
answer
96k
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,804
149
votes
6
answers
53k
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,993
148
votes
7
answers
138k
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,483
147
votes
5
answers
199k
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,923
142
votes
6
answers
320k
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,893
142
votes
12
answers
141k
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 ...
- 11.1k
141
votes
15
answers
59k
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 ...
- 65.1k
140
votes
8
answers
198k
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
140
votes
15
answers
199k
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,436
138
votes
5
answers
237k
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,195
135
votes
12
answers
199k
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,682
134
votes
6
answers
174k
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