I have a SQLAlchemy model set up in my application that should mimic the functionality of "followers" on Twitter, ie. users have have a many-to-many relationship with eachother (both followers and following). The tables are structured as follows (sa is the sqlalchemy module): t_users = sa.Table("users", meta.metadata, sa.Column("id...
I'm trying to use association proxy for tags, in a very similar scenario to the example in the docs. Here is a subset of my schema (it's a blog), using declarative: class Tag(Base): __tablename__ = 'tags' id = Column(Integer, primary_key=True) tag = Column(Unicode(255), unique=True, nullable=False) clas...
I have a SQLAlchemy model with a one-to-many relationship between table x and table y. The record (if any) with the greatest id in table y where y.x_id = x.id is special. Class X and class Y map tables x and y. I know how to define X.all_y (ORDER BY y.id). How do I define X.latest_y equivalent to X.all_y[-1]? ...
The sqlalchemy.sql module seems to assume that I've created Table instances, but I've specified my ORM using the Declarative style. Is there a way to extract the table instances from the Declarative classes? using session.query() seems to have worse syntax than the sqlalchemy.sql methods do. ...
Hi there, I'm working on a web app and an db which deals with the following entities: computers, computer containers (computer_sets) and rules. It a firewall management tool. I'm having some troubles dealing with computer objects, because those computers stored within a computer_set are not the same objects as those computers not contain...
Hi, I'm trying to create a simple unique username function for use in a Formencode schema. Here is the function: class UniqueUsername(formencode.FancyValidator): def _to_python(self, value, state): user = DBSession.query(User.user_name).filter(User.username==value) if user is not None: raise form...
I have been trying to create an association relation between two tables, intake and module . Each intake has a one-to-many relationship with the modules. However there is a coursework assigned to each module, and each coursework has a duedate which is unique to each intake. I tried this but it didnt work: intake_modules_table = Tabl...
or a simple way to iterate over columnName, value pairs? My version of sqlalchemy is 0.5.6 Here is the sample code where I tried using dict(row), but it throws exception , TypeError: 'User' object is not iterable import sqlalchemy from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm impo...
Hi I have a table in 3NF form ftype_table = Table( 'FTYPE', Column('ftypeid', Integer, primary_key=True), Column('typename', String(50)), base.metadata, schema='TEMP') file_table = Table( 'FILE', base.metadata, Column('fileid', Integer, primary_key=True), Column('datatypeid', Integer, ForeignKey(ftype...
I have three tables (users, articles, and tags) defined in SQLAlchemy and mapped with orm.mapper(). As you can see below, I'm adding a property "author" to each article which ties that article to the user that created it. orm.mapper(User, t_users) orm.mapper(Tag, t_tags) orm.mapper(Article, t_articles, properties={ 'author' : orm.r...
I would like to represent a datatype as a single column in my model, but really the data will be stored in multiple columns in the database. I cannot find any good resources on how to do this in SQLAlchemy. I would like my model to look like this(this is a simplified example using geometry instead of my real problem which is harder to ...
Hello everybody Is it possible to use raw SQL rather than the TABLE construct for creating tables in SQL Alchemy? I would still like to use the rest of SQLAlchemy though, such as the object mapper and session module. I'm just not fond of the SQLAlchemy syntax used to create tables (I've spent too long mired in SAS and SQL to learn ano...
My database structure is something like this (I'm using declarative style): class Character(Base): __tablename__="characters" id = Column(Integer, primary_key=True) name = Column(String) player = Column(String) inventory = relation(Inventory) class Item(Base): __tablename__="items" id = Column(Integer, prima...
I'm using the SQLAlchemy ORM to construct the MySQL queries in my application, and am perfectly able to add basic filters to the query, like so: query = meta.Session.query(User).filter(User.user_id==1) Which gives me something basically equivalent to this: SELECT * FROM users WHERE user_id = 1 My question is how I would integrate s...
Using SQLAlchemy, I have a one to many relation with two tables - users and scores. I am trying to query the top 10 users sorted by their aggregate score over the past X amount of days. users: id user_name score scores: user score_amount created My current query is: top_users = DBSession.query(User).opt...
Hi I'm trying to update a user row upon the user logging in. I simply want to increase the users login count by one. Here is the code in the post_login controller method: @expose() def post_login(self, came_from=url('/')): """ Redirect the user to the initially requested page on successful authentication or redirect ...
Hello there, Two questions: i want to generate a View in my PostGIS-DB. How do i add this View to my geometry_columns Table? What i have to do, to use a View with SQLAlchemy? Is there a difference between a Table and View to SQLAlchemy or could i use the same way to use a View as i do to use a Table? sorry for my poor english. If t...
The following query returns data right away: SELECT time, value from data order by time limit 100; Without the limit clause, it takes a long time before the server starts returning rows: SELECT time, value from data order by time; I observe this both by using the query tool (psql) and when querying using an API. Questions/issues: ...
hmm, is there any reason why sa tries to add Nones to for varchar columns that have defaults set in in database schema ?, it doesnt do that for floats or ints (im using reflection). so when i try to add new row : like u = User() u.foo = 'a' u.bar = 'b' sa issues a query that has a lot more cols with None values assigned to those, and d...
After calling update(), I know that the return value has a .rowcount attribute that will reveal how many rows were changed. Is there a way to get the actual new values that they were changed to? For example, if I do the SQLAlchemy equivalent of: UPDATE t SET x=x+1 WHERE y=z ...is there a way to get the new value for x? Or do I have t...