sqlalchemy

SQLAlchemy many-to-many orphan deletion

I'm trying to use SQLAlchemy to implement a basic users-groups model where users can have multiple groups and groups can have multiple users. When a group becomes empty, I want the group to be deleted, (along with other things associated with the group. Fortunately, SQLAlchemy's cascade works fine with these more simple situations). T...

Defining a table with sqlalchemy with a mysql unix timestamp

Background, there are several ways to store dates in MySQ. As a string e.g. "09/09/2009". As integer using the function UNIX_TIMESTAMP() this is supposedly the traditional unix time representation (you know seconds since the epoch plus/minus leap seconds). As a MySQL TIMESTAMP, a mysql specific data type not the same than unix timestam...

SQLAlchemy - Dictionary of tags

Hi everybody, I have question regarding the SQLAlchemy. How can I add into my mapped class the dictionary-like attribute, which maps the string keys into string values and which will be stored in the database (in the same or another table as original mapped object). I want this add support for arbitrary tags of my objects. I found the f...

Elixir reflection

I define some Entities wich works fine; for meta programming issues, i now need to reflect the field properties defined in the model. For example: class Foo(Entity): bar = OneToMany('Bar') baz = ManyToMany('Baz') here i need the information which type of relation is set: "ManyToMany", "OneToMany" or even a plain "Field", ...

SQLAlchemy - Mapper configuration and declarative base

Hi, I am writing a multimedia archive database backend and I want to use joined table inheritance. I am using Python with SQLAlchemy with the declarative extension. The table holding the media record is as follows: _Base = declarative_base() class Record(_Base): __tablename__ = 'records' item_id = Column(String(M_ITEM_ID), For...

SQLAlchemy - MappedCollection problem

Hi, I have some problems with setting up the dictionary collection in Python's SQLAlchemy: I am using declarative definition of tables. I have Item table in 1:N relation with Record table. I set up the relation using the following code: _Base = declarative_base() class Record(_Base): __tablename__ = 'records' item_id = Column...

I need a sample of python unit testing sqlalchemy model with nose

Can someone show me how to write unit tests for sqlalchemy model I created using nose. I just need one simple example. Thanks. ...

Mapping a database table to an attribute of an object

I've come across a place in my current project where I have created several classes for storing a complicated data structure in memory and a completed SQL schema for storing the same data in a database. I've decided to use SQLAlchemy as an ORM layer as it seems the most flexible solution that I can tailor to my needs. My problem is tha...

Elixir (SqlAlchemy): relations between 3 tables with composite primary keys

I've 3 tables: A Company table with (company_id) primary key A Page table with (company_id, url) primary key & a foreign key back to Company An Attr table with (company_id, attr_key) primary key & a foreign key back to Company. My question is how to construct the ManyToOne relation from Attr back to Page using the existing columns in...

SQLAlchemy is convoluted?

This may seems rather argumentative, but I just went through SQLAlchemy's ORM tutorial and ended up with the following code: from sqlalchemy import create_engine from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker eng...

How to generate a file with DDL in the engine's SQL dialect in SQLAlchemy?

Suppose I have an engine pointing at MySQL database: engine = create_engine('mysql://arthurdent:answer42@localhost/dtdb', echo=True) I can populate dtdb with tables, FKs, etc by: metadata.create_all(engine) Is there an easy way to generate the SQL file that contains all the DDL statements instead of actually applying these DDL stat...

SQLAlchemy - Database hits on every request?

I'm currently working with a web application written in Python (and using SQLAlchemy). In order to handle authentication, the app first checks for a user ID in the session, and providing it exists, pulls that whole user record out of the database and stores it for the rest of that request. Another query is also run to check the permissio...

Problems eagerloading a set of object using SQLAlchemy

I'm using Turbogears2 and SQLAlchemy to develop a webapp. I have two mapped tables O1 and O2. O2 has a sorted list of O1s in 'ones'. At some point I want to query all O2's and the referenced O1's. Unfortunately the query below fails because table O2 is aliased in the query and the column referenced by the order_by phrase is no longer kn...

Is there support for the IN-operator in the "SQL Expression Language" used in SQLAlchemy?

Is it possible to express a query like the one below in the "SQL Expression Language" used in SQLAlchemy? SELECT * FROM foo WHERE foo.bar IN (1,2,3) I want to avoid writing the WHERE-clause in plain text. Is there a way to express this similar to my examples below or in any way that doesn't use plain text? select([foo], in(foo.c.bar, ...

The right way to auto filter SQLAlchemy queries?

I've just introspected a pretty nasty schema from a CRM app with sqlalchemy. All of the tables have a deleted column on them and I wanted to auto filter all those entities and relations flagged as deleted. Here's what I came up with: class CustomizableQuery(Query): """An overridden sqlalchemy.orm.query.Query to filter entities ...

Override default in sqlalchemy reflected tables

Hi All, I've reflecting a load of tables in an exiting mysql db. I want to express that any columns of a certain name in any table default to datetime.now(). However naively looping through the tables and columns and just setting default on those I find that have a certain name doesn't work, When doing session.add(); session.flush() I g...

Sqlalchemy complex in_ clause

I'm trying to find a way to cause sqlalchemy to generate sql of the following form: select * from t where (a,b) in ((a1,b1),(a2,b2)); Is this possible? If not, any suggestions on a way to emulate it? Thanks kindly! ...

Create SQL query using SqlAlchemy select and join functions

I have two tables "tags" and "deal_tag", and table definition follows, Table('tags', metadata, Column('id', types.Integer(), Sequence('tag_uid_seq'), primary_key=True), Column('name', types.String()), ) Table('deal_tag', metadata, Column('dealid', types.Integer(), ForeignKey('deals.id')...

How can I determine if one PGArray is included in another using SQLAlchemy sessions?

I have an SqlAlchemy table like so: table = sql.Table('treeItems', META, sql.Column('id', sql.Integer(), primary_key=True), sql.Column('type', sql.String, nullable=False), sql.Column('parentId', sql.Integer, sql.ForeignKey('treeItems.id')), sql.Column('lineage', PGArray(sql.Integer)), sql.Column('depth', sql.Integer)...

Unicode Problem with SQLAlchemy

I know I'm having a problem with a conversion from Unicode but I'm not sure where it's happening. I'm extracting data about a recent Eruopean trip from a directory of HTML files. Some of the location names have non-ASCII characters (such as é, ô, ü). I'm getting the data from a string representation of the the file using regex. If i ...