sqlalchemy

SQLAlchemy with multiple primary keys does not automatically set any

I had a simple table: class test(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) title = Column(String) def __init__(self, title): self.title = title When using this table, id was set automatically. I want to add another field that is unique and efficient to search, so I added the field: ...

How to join the same table in sqlalchemy.

I'm trying to join the same table in sqlalchemy. This is a minimial version of what I tried: #!/usr/bin/env python import sqlalchemy as sa from sqlalchemy import create_engine from sqlalchemy.orm import mapper, sessionmaker, aliased engine = create_engine('sqlite:///:memory:', echo=True) metadata = sa.MetaData() device_table = sa.Tabl...

How do I delete a foreign key in SQLAlchemy?

I'm using SQLAlchemy Migrate to keep track of database changes and I'm running into an issue with removing a foreign key. I have two tables, t_new is a new table, and t_exists is an existing table. I need to add t_new, then add a foreign key to t_exists. Then I need to be able to reverse the operation (which is where I'm having troubl...

SQLAlchemy autocommiting?

I have an issue with SQLAlchemy apparently committing. A rough sketch of my code: trans = self.conn.begin() try: assert not self.conn.execute(my_obj.__table__.select(my_obj.id == id)).first() self.conn.execute(my_obj.__table__.insert().values(id=id)) assert not self.conn.execute(my_obj.__table__.select(my_obj.id...

SQLAlchemy - full load instance before detach

Hi, is there a way how to fully load some SQLAlchemy ORM mapped instance (together with its related objects) before detaching it from the Session? I want to send it via pipe into another processs and I don't want to merge it into session in this new process. Thank you Jan ...

How can I use geoalchemy with elixir autoload tables?

I'm using geoalchemy and autoloaded tables, but I'd like to use Elixir, because it has a nicer query syntax. Does anyone know how to get them to work together? I did get it working with this code -- http://pastie.textmate.org/private/y3biyvosuejkrtxbpdv1a -- but that still gives the ugly warning about not recognising the geometry column ...

Batch select with SQLAlchemy

I have a large set of values V, some of which are likely to exist in a table T. I would like to insert into the table those which are not yet inserted. So far I have the code: for value in values: s = self.conn.execute(mytable.__table__.select(mytable.value == value)).first() if not s: to_insert.appen...

How to discover table properties from SQLAlchemy mapped object

Hi, My point is i have a class mapped with a table, in my case in a declarative way, and i want to "discover" table properties, columns, names, relations, from this class : engine = create_engine('sqlite:///' + databasePath, echo=True) # setting up root class for declarative declaration Base = declarative_base(bind=engine) class Shi...

SQLAlchemy custom query column

I have a declarative table defined like this: class Transaction(Base): __tablename__ = "transactions" id = Column(Integer, primary_key=True) account_id = Column(Integer) transfer_account_id = Column(Integer) amount = Column(Numeric(12, 2)) ... The query should be: SELECT id, (CASE WHEN transfer_account_id=1 T...

Many to many relation SQLAlchemy (does relation exsist attribute)

I'm re-asking this question but with a different framework this time. I have two Models: User and Book with a M2M-relation. I want Book to have an attribute "read" that is True when the relation exists. Is this possible in SQLAlchemy? ...

How do you get SQLAlchemy to override MySQL "on update CURRENT_TIMESTAMP"

I've inherited an older database that was setup with a "on update CURRENT_TIMESTAMP" put on a field that should only describe an item's creation. With PHP I have been using "timestamp=timestamp" on UPDATE clauses, but in SQLAlchemy I can't seem to force the system to use the set timestamp. Do I have no choice and need to update the MyS...

How do I create self-relationships in polymorphic inheritance in Elixir and Pylons?

I am new to programming and am following the example in the Pylons documentation on creating a Wiki. The database I want to link to the wiki was created with Elixir so I rewrote the Wiki database schema and have continued from there. In the wiki there is a requirement for a Navigation table which is inherited by Pages and Sections. A s...

In Elixir or SQLAlchemy, is there a way to also store a comment for a/each field in my entities?

Our project is basically a web interface to several systems of record. We have many tables mapped, and the names of each column aren't as well named and intuitive as we'd like... The users would like to know what data fields are available (i.e. what's been mapped from the database). But, it's pointless to just give them column names like...

Sqlalchemy layout with WSGI application

I'm working on writing a small WSGI application using Bottle and SqlAlchemy and am confused on how the "layout" of my application should be in terms of SqlAlchemy. My confusion is with creating engines and sessions. My understanding is that I should only create one engine with the 'create_engine' method. Should I be creating an engine i...

Storing simulation results in a persistent manner for Python?

Background: I'm running multiple simuations on a set of data. For each session, I'm allocating projects to students. The difference between each session is that I'm randomising the order of the students such that all the students get a shot at being assigned a project they want. I was writing out some of the allocations in a spreadsheet ...

Map only certain parts of the class to a database using SQLAlchemy?

When mapping an object using SQLAlchemy, is there a way to only map certain elements of a class to a database, or does it have to be a 1:1 mapping? Example: class User(object): def __init__(self, name, username, password, year_of_birth): self.name = name self.username = username self.password = password ...

SQLAlchemy - instance consistency

Hi, I am using the column validator in the SQLAlchemy ( http://stackoverflow.com/questions/2317081/sqlalchemy-maximum-column-length ) but I have one problem which corresponds with the Session. First of all, I create a table using the declarative base (sqlalchemy.ext.declarative) where InstallValidatorListeners is the validator class (se...

How do I join three tables with SQLalchemy and keeping all of the columns in one of the tables?

So, I have three tables: The class defenitions: engine = create_engine('sqlite://test.db', echo=False) SQLSession = sessionmaker(bind=engine) Base = declarative_base() class Channel(Base): __tablename__ = 'channel' id = Column(Integer, primary_key = True) title = Column(String) description = Column(String) link = ...

How to make SQLAlchemy use Memcached?

Has anyone joined SQLAlchemy and Memcached together so that results returned by SQLAlchemy where cached in Memcached? At the moment I have no idea how to implement it myself, I am completely lost and looking for help. Thanks, Boda Cydo. ...

Can SQLAlchemy DateTime Objects Only Be Naive?

I am working with SQLAlchemy, and I'm not yet sure which database I'll use under it, so I want to remain as DB-agnostic as possible. How can I store a timezone-aware datetime object in the DB without tying myself to a specific database? Right now, I'm making sure that times are UTC before I store them in the DB, and converting to local...