sqlalchemy

Dictionary of tags in declarative SQLAlchemy?

I am working on a quite large code base that has been implemented using sqlalchemy.ext.declarative, and I need to add a dict-like property to one of the classes. What I need is the same as in this question, but in a declarative fashion. Can anyone with more knowledge in SQLAlchemy give me an example? Thanks in advance... ...

Setting up/Inserting into Many-to-Many Database with Python, SQLALchemy, Sqlite

I am learning Python, and as a first project am taking Twitter RSS feeds, parsing the data, and inserting the data into a sqlite database. I have been able to successfully parse each feed entry into a content variable (e.g., "You should buy low..."), a url variable (e.g., [u'http://bit.ly/HbFwL']), and a hashtag list (e.g., #stocks', u'#...

Locking in sqlalchemy

I'm confused about how to concurrently modify a table from several different processes. I've tried using Query.with_lockmode(), but it doesn't seem to be doing what I expect it to do, which would be to prevent two processes from simultaneously querying the same rows. Here's what I've tried: import time from sqlalchemy.orm import session...

SQLAlchemy session management in long-running process

Scenario: A .NET-based application server (Wonderware IAS/System Platform) hosts automation objects that communicate with various equipment on the factory floor. CPython is hosted inside this application server (using Python for .NET). The automation objects have scripting functionality built-in (using a custom, .NET-based language). T...

Writing a connection string when password contains special characters

I'm using SQLalchemy for a Python project, and I want to have a tidy connection string to access my database. So for example: engine = create_engine('postgres://user:pass@host/database') The problem is my password contains a sequence of special characters that get interpreted as delimiters when I try to connect. I realize I could ju...

Reverse engineer SQLAlchemy declarative class definition from existing MySQL database?

I have a pre-existing mysql database containing around 50 tables. Rather than hand code a declarative style SqlAlchemy class (as shown here) for each table, is there a tool/script/command I can run against the mysql database that will generate a python class in the declarative style for each table in the database? To take just one tabl...

Load an existing many-to-many table relation with sqlalchemy

I'm using SqlAlchemy to interact with an existing PostgreSQL database. I need to access data organized in a many-to-many relationship. The documentation describes how to create relationships, but I cannot find an example for neatly loading and query an existing one. ...

Star schema in SQLAlchemy

I have a star-schema architectured database that I want to represent in SQLAlchemy. Now I have the problem on how this can be done in the best possible way. Right now I have a lot of properties with custom join conditions, because the data is stored in different tables. It would be nice if it would be possible to re-use the dimensions fo...

Werkzeug and SQLAlchemy 0.5x session

Updated: Hi all, Going through the Werkzeug link text tutorial, got stack with creating SQLAlchemy session using sessionmaker() instead of create_session() as recommended. Note: it is not about SA, it is about Werkzeug. Werkzeug tutorial: session = scoped_session(lambda: create_session(bind=application.database_engine, autoflush...

Should I create mapper objects or use the declarative syntax in SQLAlchemy?

There are two (three, but I'm not counting Elixir, as its not "official") ways to define a persisting object with SQLAlchemy: Explicit syntax for mapper objects from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey from sqlalchemy.orm import mapper metadata = MetaData() users_table = Table('users', metadata, ...

How to filter query in sqlalchemy by year (datetime column)

I have table in sqlalchemy 0.4 that with types.DateTime column: Column("dfield", types.DateTime, index=True) I want to select records, that has specific year in this column, using model. How to do this? I though it should be done like this: selected_year = 2009 my_session = model.Session() my_query = my_session.query(model.MyRecord)....

Allowing user to rollback from db audit trail with SQLAlchemy

I'm starting to use SQLAlchemy for a new project where I was planning to implement an audit trail similar to the one proposed on this quiestions: http://stackoverflow.com/questions/328898/implementing-audit-trail-for-objects-in-c http://stackoverflow.com/questions/315240/audit-trails-and-implementing-sox-hipaa-etc-best-practices-for-se...

Do not require non-NULL field (allow empty strings) in FormAlchemy

I'm fairly novice to FormAlchemy and it seems that I don't get something. I have a SQLAlchemy model defined like this: ... class Device(meta.Base): __tablename__ = 'devices' id = sa.Column('id_device', sa.types.Integer, primary_key=True) serial_number = sa.Column('sn', sa.types.Unicode(length=20), nullable=False) mac = ...

Sqlalchemy : association table for many-to-many relationship between groups and members. How can I delete a relationship?

I have those tables setup : http://pastie.org/627764 ... # This is the association table for the many-to-many relationship between # groups and members - this is, the memberships. user_group_table = Table('user_group', metadata, Column('user_name', Integer, ForeignKey('user.user_name', onupdate="CASCA...

Creating tables with pylons and SQLAlchemy

I'm using SQLAlchemy and I can create tables that I have defined in /model/__init__.py but I have defined my classes, tables and their mappings in other files found in the /model directory. For example I have a profile class and a profile table which are defined and mapped in /model/profile.py To create the tables I run: paster setu...

replace/delete field using sqlalchemy

Two part question. Using postgres in python. 1) How do I replace all fields from the same column that match a specified value? For example, let's say I want to replace any fields that match "green" with "red" in the "Color" column. 2) How to delete all fields from the same column that match a specified value? For example, I'm trying to...

SQLAlchemy - MapperExtension.before_delete not called

Hi everybody, I have question regarding the SQLAlchemy. I have database which contains Items, every Item has assigned more Records (1:n). And the Record is partially stored in the database, but it also has an assigned file (1:1) on the filesystem. What I want to do is to delete the assigned file when the Record is removed from the datab...

Sequence / Identity support in python webframeworks

Currently I'm evaluating web frameworks with an ORM layer and I've stumbled upon an interesting issue. I used tables with an ID column in a JEE (EJB3/JPA) application with different databases. In SAPDB I could define a sequence and use the jpa sequence generator annotation to deal with it, the same way I did on an oracle database previou...

How to tell when nosetest is running programmatically

nosetest is the default test framework in Turbogeras 2.0. The application has a websetup.py module that initialise the database. I use mysql for my development and production environment and websetup works fine, but nosetest uses sqlite on memory and when it tries to initialise the DB sends an error: TypeError: SQLite Date, Time, a...

Duplicate Insertions in Database using sqlite, sqlalchemy, python

I am learning Python and, through the help of online resources and people on this site, am getting the hang of it. In this first script of mine, in which I'm parsing Twitter RSS feed entries and inserting the results into a database, there is one remaining problem that I cannot fix. Namely, duplicate entries are being inserted into one o...