sqlalchemy

Importing fixtures with foreign keys and SQLAlchemy?

I've been experimenting with using fixture to load test data sets into my Pylons / PostgreSQL app. This works great except that it fails to properly create foreign keys if they reference an auto increment id field. My fixture looks like this: class AuthorsData(DataSet): class frank_herbert: first_name = "Frank" la...

Trouble with copying dictionaries and using deepcopy on an SQLAlchemy ORM object

Hi there, I'm doing a Simulated Annealing algorithm to optimise a given allocation of students and projects. This is language-agnostic pseudocode from Wikipedia: s ← s0; e ← E(s) // Initial state, energy. sbest ← s; ebest ← e // Initial "best" solution k ← 0 ...

Using Elixir, how can I get the table object of a self-referential relationship to perform inserts on?

I'm using Elixir with SQLite and I'd like to perform multiple inserts as per the docs: http://www.sqlalchemy.org/docs/05/sqlexpression.html#executing-multiple-statements However, my ManyToMany relationship is self-referential and I can't figure out where to get the insert() object from. Can anyone help? Thanks! ...

Help with copy and deepcopy in Python

Hi there, I think I tried to ask for far too much in my previous question so apologies for that. Let me lay out my situation in as simple a manner as I can this time. Basically, I've got a bunch of dictionaries that reference my objects, which are in turn mapped using SQLAlchemy. All fine with me. However, I want to make iterative chan...

SQLAlchemy Expression Language problem

I'm trying to convert this to something sqlalchemy expression language compatible, I don't know if it's possible out of box and are hoping someone more experienced can help me along. The backend is PostgreSQL and if I can't make it as an expression I'll create a string instead:: SELECT DISTINCT date_trunc('month', x.x) as date, ...

Using SQL Alchemy and pyodbc with IronPython 2.6.1

I'm using IronPython and the clr module to retrieve SQL Server information via SMO. I'd like to retrieve/store this data in a SQL Server database using SQL Alchemy, but am having some trouble loading the pyodbc module. Here's the setup: IronPython 2.6.1 (installed at D:\Program Files\IronPython) CPython 2.6.5 (installed at D:\Python26...

sqlalchemy date type in 0.6 migration using mssql

I'm connection to mssql server through pyodbc, via FreeTDS odbc driver, on linux ubuntu 10.04. Sqlalchemy 0.5 uses DATETIME for sqlalchemy.Date() fields. Now Sqlalchemy 0.6 uses DATE, but sql server 2000 doesn't have a DATE type. How can I make DATETIME be the default for sqlalchemy.Date() on sqlalchemy 0.6 mssql+pyodbc dialect? I'd l...

Pylons 1.0 AttributeError: 'module' object has no attribute 'metadata'

Python noob trying to learn Pylons. I'm using the QuickWiki tutorial (http://pylonshq.com/docs/en/1.0/tutorials/quickwiki_tutorial/) from the 1.0 documentation, but this alleged "1.0" doc seems to just be "0.9.7"; I suspect that this has something to do with the error I'm getting. When I execute "paster setup-app development.ini", I get...

How can I get the types of entities in an SQLALchemy query

I know how to get the columns that would be returned from a select statement but how do I get the entities that would be returned from an sqlalchemy.orm.Query object? >>> sess = Session() >>> q = sess.query(Entity1, Entity2) >>> q.statement.c.keys() ['e1_col1', 'e1_col2', ..., 'e2_col1', 'e2_col2, ...] I want [Entity1, Entity2] or som...

Twisted + SQLAlchemy and the best way to do it.

So I'm writing yet another Twisted based daemon. It'll have an xmlrpc interface as usual so I can easily communicate with it and have other processes interchange data with it as needed. This daemon needs to access a database. We've been using SQL Alchemy in place of hard coding SQL strings for our latest projects - those mostly done f...

SQLAlchemy - how to map against a read-only (or calculated) property

I'm trying to figure out how to map against a simple read-only property and have that property fire when I save to the database. A contrived example should make this more clear. First, a simple table: meta = MetaData() foo_table = Table('foo', meta, Column('id', String(3), primary_key=True), Column('description', String(64), ...

Handle mysql restart in SQLAlchemy

My Pylons app uses local MySQL server via SQLAlchemy and python-MySQLdb. When the server is restarted, open pooled connections are apparently closed, but the application doesn't know about this and apparently when it tries to use such connection it receives "MySQL server has gone away": File '/usr/lib/pymodules/python2.6/sqlalchemy/engi...

SQLAlchemy automatically converts str to unicode on commit

Hello, When inserting an object into a database with SQLAlchemy, all it's properties that correspond to String() columns are automatically transformed from <type 'str'> to <type 'unicode'>. Is there a way to prevent this behavior? Here is the code: from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData from sql...

How can I "override" deepcopy in Python?

Hi there, I'd like to override __deepcopy__ for a given SQLAlchemy-mapped class such that it ignores any SQLA attributes but deepcopies everything else that's part of the class. I'm not particularly familiar with overriding any of Python's built-in objects in particular but I've got some idea as to what I want. Let's just make a very ...

SQLAlchemy DetachedInstanceError with regular attribute (not a relation)

I just started using SQLAlchemy and get a DetachedInstanceError and can't find much information on this anywhere. I am using the instance outside a session, so it is natural that SQLAlchemy is unable to load any relations if they are not already loaded, however, the attribute I am accessing is not a relation, in fact this object has no r...

Many-to-many relationship on same table with association object

Related (for the no-association-object use case): http://stackoverflow.com/questions/1889251/sqlalchemy-many-to-many-relationship-on-a-single-table Building a many-to-many relationship is easy. Building a many-to-many relationship on the same table is almost as easy, as documented in the above question. Building a many-to-many relation...

SQLAlchemy: How to group by two fields and filter by date

So I have a table with a datestamp and two fields that I want to make sure that they are unique in the last month. table.id table.datestamp table.field1 table.field2 There should be no duplicate record with the same field1 + 2 compound value in the last month. The steps in my head are: Group by the two fields Look back over the las...

how do i take advantage of sqlite manifest typing / type affinity using sqlalchemy?

I like the idea of sqlite's manifest typing / type affinity: http://www.sqlite.org/datatype3.html Essentially, if I set a column's affinity as 'numeric', it will duck type integers or floats to store them as such, but still allow me to store strings if I want to. Seems to me this is the best 'default' type for a column when i'm not su...

Is there something like SqlAlchemy's SqlSoup for Java?

I'm looking for a Java ORM that works from database reflection - I need to just point it at a DB, and start being able to walk through the entire set of tables, etc. Ideas? ...

SQLAlchemy: select over multiple tables

Hi, I wanted to optimize my database query: link_list = select( columns=[link_table.c.rating, link_table.c.url, link_table.c.donations_in], whereclause=and_( not_(link_table.c.id.in_( select( columns=[request_table.c.recipient], whereclause=request_table.c.donator==donator.id ...