sqlalchemy

SQLAlchemy memory hog on select statement

As per the SQLAlchemy, select statements are treated as iterables in for loops. The effect is that a select statement that would return a massive amount of rows does not use excessive memory. I am finding that the following statement on a MySQL table: for row in my_connections.execute(MyTable.__table__.select()): yield row Does ...

django orm versus sqlachemy, are they basically the same thing?

When using django, I believe you can swap out the built-in orm for sqlalchemy (not sure how though?). Are they both basically the same thing or there is a clear winner between the 2? ...

QuerySelectField works on one server, breaks on another with same code.

I'm in the process of setting up a test installation of my current Python/Django project. Everything works great on my dev server, but we recently set up a new VM for the test and eventual production copies of the project. I'm using Python, Django, SqlAlchemy (with a MSSQL backend), and WTForms as my main packages. I'm having a proble...

How badly should I avoid surrogate primary keys in SQL?

Short story I have a technical problem with a third-party library at my hands that I seem to be unable to easily solve in a way other than creating a surrogate key (despite the fact that I'll never need it). I've read a number of articles on the Net discouraging the use of surrogate keys, and I'm a bit at a loss if it is okay to do what ...

SqlAlchemy: UPDATE and INSERT order wrong with foreign key to self

I have a table which has a foreign key relationship with itself (on a compound primary key) E.g. something like the following: CREATE TABLE graph ( start_id character varying(50) NOT NULL, end_id character varying(50) NOT NULL, weight integer, other_start_id character varying(50), other_end_id character varying(50), CONSTRA...

SQLAlchemy 0.5.8 Max function

using SQLAlchemy 0.5.8 how do you run a max query? i.e. Select max(id) from some_table ...

Encoding calling from pyodbc to a MS SQL Server

I am connecting to a MS SQL server through SQL Alchemy, using pyodbc module. Everything appears to be working fine, until I began having problems with the encodings. Some of the non-ascii characters are being replaced with '?' The DB has a collation 'Latin1_General_CI_AS' (I've checked also the specific fields and they keep the same col...

Why is my data not being represented properly in my SQLAchemy model?

I have a peculiar SQLAlchemy ORM problem. This is occurring in a Pylons application, against a Postgresql 8.2 database using psycopg2 as my database driver under SQLAlchemy 0.6.0 (and tried with 0.6.4 as well) I have defined a User model object that has (at minimum) the following properties: class User(Base): __tablename__ = 'users...

Raw SQL within Pylons app that uses SQLAlchemy?

I've inherited a Pylons app that uses SQLAlchemy. I know nothing about SQLAlchemy and very little about Pylons :) I need to run some raw SQL from within the app. The SQLAlchemy currently seems to be working in the following way (example code): import myapp.model as model model.Session.query(model.KeyValue) # existing code ....

How to use subqueries in SQLAlchemy to produce a moving average?

My problem is that I want to retrieve both a list of measurements along with a moving average of those measurements. I can do that with this SQL statement (postgresql interval syntax): SELECT time, value, ( SELECT AVG(t2.value) FROM measurements t2 WHERE t2.time BETWEEN t1.time - interval '5 days...

Python: interact with complex data warehouse

We've worked hard to work up a full dimensional database model of our problem, and now it's time to start coding. Our previous projects have used hand-crafted queries constructed by string manipulation. Is there any best/standard practice for interfacing between python and a complex database layout? I've briefly evaluated SQLAlchemy, S...

How to map more than one table to a class with SQLAlchemy

I'm new to sqlalchemy and am trying to map several tables to a class. The tables are constructed like so: Types -typeID -typeName -groupID ... TypeAttributes -typeID -attributeID -attributeValueInt -attributeValueFloat The idea is to have sqlalchemy map a from TypeAttributes to normal class attributes. As added comp...

sqlalchemy polymorhic_identity not working

Hi, I'm trying to use polymorphic_on on a python class with several inheritances: engine = create_engine( 'mysql://xxx:yyy@localhost:3306/zzz?charset=utf8&use_unicode=0', pool_recycle=3600, echo=True) Base = declarative_base() class AbstractPersistent(object): version = Column('VERSION', Integer) last_modified_by = Co...

What type is on the other end of relation in sqlalchemy without creating objects?

How to obtain a name of the class that should be on the other end of the relation? It was declared when creating relationship. I guess that information should be somwhere in sqlalchemy.orm.util.class_mapper Let's say we have these three classes and a relations between them. Book * --- 1 Shelf and Book * --- * Author class Shelf(Base)...

Elixir - deleting rows in a ManyToMany intermediate table

Hi, I have two tables with a ManyToMany relation between them. Sometimes I need to refresh the database so I delete elements from both tables. However relations between deleted rows are still stored inside the automatically created intermediary table. To clarify the problem, here is a small code: from elixir import * metadata.bind =...

Is there a DB/ORM pattern for attributes?

i want to create an object with different key-value as attributes, for example: animal id name attribute id name and mapping animal_attribute animal_id attribute_id so i can have a entry "duck", which has multiple attribute "flying", "swimming", etc. Each attribute type would have its own table defining so...

Expunge object from SQLAlchemy session

I want to pass an instance of a mapped class to a non-SQLAlchemy aware method (in another process) and only need the values of my attributes. The problem is, that an UnboundExecutionError occurs, every time the method wants to read an attribute value. I do understand, why this happens, but I would like to have a solution for this problem...

SQL Alchemy + Testing webserver with InnoDB fails.

Hi, I am currently trying to move my DB tables over to InnoDB from MyISAM. I am having timing issues with requests and cron jobs that are running on the server that is leading to some errors. I am quite sure that transaction support will help me with the problem. I am therefore transitioning to InnoDB. I have a suite of tests which ...

Flask - how do I combine Flask-WTF and Flask-SQLAlchemy to edit db models?

I'm trying to create an edit page for an existing model (already saved to db). The form object expects a multidict instance to populate its fields. This is what I have: # the model - assumes Flask-SQLAlchemy from flaskext.sqlalchemy import SQLAlchemy db = SQLAlchemyd(app) class Person(db.Model): id = db.Column(db.Integer, primary...

joinedload / eager loading whole (sub)-graphs in sqlalchemy

Let's say I have a Task object which can be dependent on other Tasks. Is there a way to sensibly eager/joinedload all of a given set of task's subtasks? class Task(DeclarativeBase): __tablename__ = 'task' task_id = Column(Integer, primary_key=True) name = Column(String, unique=True) def add_dependencies(self, *tasks): ...