sqlalchemy

method of iterating over sqlalchemy model's defined columns?

I've been trying to figure out how to iterate over the list of columns defined in a SqlAlchemy model. I want it for writing some serialization and copy methods to a couple of models. I can't just iterate over the obj.dict since it contains a lot of SA specific items. Anyone know of a way to just get the id, and desc names from the foll...

Does SQLAlchemy have an equivalent of Django's get_or_create?

I want to get an object from the database if it already exists (based on provided parameters) or create it if it does not. Django's get_or_create does this. Is there an equivalent shortcut in SQLAlchemy? I'm currently writing it out explicitly like this: def get_or_create_instrument(session, serial_number): instrument = session.q...

Purpose of SQLAlchemy over MySQLdb

Why do people use SQLAlchemy instead of MySQLdb? What advantages does it offer? ...

How do I do a semijoin using SQLAlchemy?

http://en.wikipedia.org/wiki/Relational_algebra#Semijoin Let's say that I have two tables: A and B. I want to make a query that would work similarly to the following SQL statement using the SQLAlchemy orm: SELECT A.* FROM A, B WHERE A.id = B.id AND B.type = 'some type'; The thing is that I'm trying to separate out A and B's logic i...

sqlalchemy dynamic mapping

Hi, I have the following problem: I have the class: class Word(object): def __init__(self): self.id = None self.columns = {} def __str__(self): return "(%s, %s)" % (str(self.id), str(self.columns)) self.columns is a dict which will hold (columnName:columnValue) values. The name of the columns are ...

Database on the fly with scripting languages

I have a set of .csv files that I want to process. It would be far easier to process it with SQL queries. I wonder if there is some way to load a .csv file and use SQL language to look into it with a scripting language like python or ruby. Loading it with something similar to ActiveRecord would be awesome. The problem is that I don't w...

How to set up global connect to datebase in pylons(python), sqlalchemy.

Hi! I just start lern python, pylons. i have problem with setting up datebase connection. i won't to set connection, where i can see this connection in all my controllers. Now i use: some thing like this in my controller: 45 ' db = create_engine('mysql://root:password@localhost/python') 46 ' metadata = MetaData(db) 47 48 ' ...

How to improve the speed of a loop containing a sqlalchemy query statement as conditional

This loop checks if a record is in the sqlite database and builds a list of dictionaries for those records that are missing and then executes a multiple insert statement with the list. This works but it is very slow (at least i think it is slow) as it takes 5 minutes to loop over 3500 queries. I am a complete newbie in python, sqlite and...

'NoneType' object has no attribute 'get' error using SQLAlchemy

I've been trying to map an object to a database using SQLAlchemy but have run into a snag. Edit: Basically changed a whole bunch of stuff. Version info if handy: [OS: Mac OSX 10.5.8 | Python: 2.6.4 | SQLAlchemy: 0.5.8] The class I'm going to map: class Student(object): def __init__(self, id, name): self.id = id ...

Multiple foreign keys to same table using Elixir

Hello, I am new to ORMs in general. I have a table (lets call it Entity) with columns -- ID expression type ( can be 'long_word', 'short_word' or 'sentence' ) Often, the first two types occur in a 'sentence', so, I want to maintain another table which maps 'Sentences' to 'long_word' or 'short_word' using the 'ID' field. All i need...

SQLAlchemy unsupported type error - and table design issues?

Hi there, back again with some more SQLAlchemy shenanigans. Let me step through this. My table is now set up as so: engine = create_engine('sqlite:///:memory:', echo=False) metadata = MetaData() students_table = Table('studs', metadata, Column('sid', Integer, primary_key=True), Column('name', String), Column('preferences...

SQLAlchemy: who is in charge of the "session"? ( and how to unit-test with sessions )

I need some guidance on how to use session objects with SQLAlchemy, and how to organize Unit Tests of my mapped objects. What I would like to able to do is something like this: thing = BigThing() # mapped object child = thing.new_child() # create and return a related object thing.save() # will also save the child object In order to a...

Sqlite / SQLAlchemy: how to enforce Foreign Keys?

The new version of SQLite has the ability to enforce Foreign Key constraints, but for the sake of backwards-compatibility, you have to turn it on for each database connection separately! sqlite> PRAGMA foreign_keys = ON; I am using SQLAlchemy -- how can I make sure this always gets turned on? What I have tried is this: engine = sqlal...

SQLAlchemy Mapping problem

Dear Everyone, I am trying to sqlalchemy to correctly map my data. Note that a unified group is basically a group of groups. (One unifiedGroup maps to many groups but each group can only map to one ug). So basically this is the definition of my unifiedGroups: CREATE TABLE `unifiedGroups` ( `ugID` INT AUTO_INCREMENT, `gID`...

getting last insert id .sqlalchemy orm

Hi i use sqlalchemy, i need some help. import hashlib import sqlalchemy as sa from sqlalchemy import orm from allsun.model import meta t_user = sa.Table("users",meta.metadata,autoload=True) class Duplicat(Exception): pass class LoginExistsException(Exception): pass class EmailExistsException(Exception): pass class User(object):...

Starter question of declarative style SQLAlchemy relation()

I am quite new to SQLAlchemy, or even database programming, maybe my question is too simple. Now I have two class/table: class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(40)) ... class Computer(Base): __tablename__ = 'comps' id = Column(Integer, primary_key=Tr...

SQLAlchemy, one to many vs many to one

Dear Everyone, I have the following data: CREATE TABLE `groups` ( `bookID` INT NOT NULL, `groupID` INT NOT NULL, PRIMARY KEY(`bookID`), KEY( `groupID`) ); and a book table which basically has books( bookID, name, ... ), but WITHOUT groupID. There is no way for me to determine what the groupID is at the time of ...

Problem with between join for sqlalchemy orm relation.

I'm trying to create a relation that has a between join. Here is a shortish example of what I'm trying to do: #!/usr/bin/env python import sqlalchemy as sa from sqlalchemy import orm from sqlalchemy.ext.declarative import declarative_base metadata = sa.MetaData() Base = declarative_base(metadata=metadata) engine = sa.create_engine('s...

sqlalchemy relation through another (declarative)

Is anyone familiar with ActiveRecord's "has_many :through" relations for models? I'm not really a Rails guy, but that's basically what I'm trying to do. As a contrived example consider Projects, Programmers, and Assignments: from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy import Column, Fo...

SQLAlchemy: a better way for update with declarative?

I am a SQLAlchemy noob. Let's say I have an user table in declarative mode: class User(Base): __tablename__ = 'user' id = Column(u'id', Integer(), primary_key=True) name = Column(u'name', String(50)) When I know user's id without object loaded into session, I update such user like this: ex = update(User.__table__).where(...