Execute SQL from file in SQLAlchemy
How can I execute whole sql file into database using SQLAlchemy? There can be many different sql queries in the file including begin and commit/rollback. ...
sqlalchemy
SQLAlchemy and scalar values
Hello, I have simple question regarding SQLAlchemy, is it possible to get the rows from the result as scalars instead of tuples? In other words I want an equivalent to: [i[0] for i in self.archive.query(IRTerm.term).distinct()] Thank you ...
SQLAlchemy subquery - average of sums
Hello, is there any way how to write the following SQL statement in SQLAlchemy ORM: SELECT AVG(a1) FROM (SELECT sum(irterm.n) AS a1 FROM irterm GROUP BY irterm.item_id); Thank you ...
How do I effectively use SQLAlchemy with multiple DDD Repositories?
I have been trying to find some examples of how to implement the Repository pattern with SQLAlchemy. Specifically, implementing more than one Repository. In the case of multiple Repositories, I believe each Repository would be best implemented by maintaining a separate SQLAlchemy session. However, I have been running into a problem tryi...
Discovering referers to SQLAlchemy object
I have a lot of model classes with ralations between them with a CRUD interface to edit. The problem is that some objects can't be deleted since there are other objects refering to them. Sometimes I can setup ON DELETE rule to handle this case, but in most cases I don't want automatic deletion of related objects till they are unbound man...
sqlalchemy 0.6 legacy database access?
I feel like this should be simple, but i cant find a single example of it being done. As an example I have the following existing tables: CREATE TABLE `source` ( `source_id` tinyint(3) unsigned NOT NULL auto_increment, `name` varchar(40) default NULL, PRIMARY KEY (`source_id`), UNIQUE KEY `source_name` (`name`) ) ENGINE=InnoDB...
SQLAlchemy.declarative and deferred column loading
Hi everybody, is it possible to specify some columns in the SQLAlchemy to be deferred-loading? I am using the sqlalchemy.ext.declarative module to define my mapping, example: from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class SomeClass(Base): __tablename__ = 'some_table' id = Column(Integer...
Updating a de-normalized attribute automatically with an AttributeExtension
I am having some troubles with the AttributeExtension of SQLAlchemy. Actually I am storing a de-normalized sum attribute in the Partent table, because I need it quite often for sorting purposes. However, I would like the attribute to get updated whenever the value of one of it's children is changed. Unfortunately, the set() method of t...
Rollback error when saving incorrect data into column
I have a fairly standard SQLA setup with the exception of reflecting the table I'm using. Here's the code: import sqlalchemy as sa from sqlalchemy import orm engine = sa.create_engine('mssql://<clip>') metadata = sa.MetaData(bind=engine) Session = orm.sessionmaker(autoflush=False, autocommit=False) event_table = sa.Table('XML_INTERFAC...
HTTP Base SQLAlchemy database
Hi all. I have a little problem, I want to use SQLAlchemy (Elixr to be exact) to do some task on a RDBMS MySql on a web server I have in hosting. My Hosting provider does not allowe me to connect to MySql directly, just by php scripts... I was think to use a simple php script to tunnel the queies done by SQLAlchemy. There is some way to...
sqlalchemy Resultproxy Slow on large tables?
Hi im using sqlalchemy on a db2 table with 500k rows. using plain sql like this: sql="select * from test.test" result=Session.execute(sql) for row in result: pdic[row.id]=row.val1 this takes 5min if i use ibm_db : sql="select * from test.test" stmt = ibm_db.exec_immediate(ibm_db_conn,sql) result =ibm_db.fetch_both(stmt)...
SQLAlchemy: checking unicode string's validity for a given type of db column
Hi folks! Am developing an extract-transform-load script with sqlalchemy. Scenario is as follows: take 30+ mln text file (csv, tab-delimited or any other...). parse it and generate file, suitable for 'Load data infile' mySQL import command (as described http://dev.mysql.com/doc/refman/5.0/en/load-data.html ) From within script, disabl...
Inserting data in Many to Many relationship in SQLAlchemy
Suppose I have 3 classes in SQLALchemy: Topic, Tag, Tag_To_Topic. Is it possible to write something like: new_topic = Topic("new topic") Topics.tags = ['tag1', 'tag2', 'tag3'] Which I would like to automatically insert 'tag1', 'tag2' and 'tag3' in Tag table, and also insert the correct relationship between new_topic and these 3 tags ...
Setting up relations/mappings for a SQLAlchemy many-to-many database
I'm new to SQLAlchemy and relational databases, and I'm trying to set up a model for an annotated lexicon. I want to support an arbitrary number of key-value annotations for the words which can be added or removed at runtime. Since there will be a lot of repetition in the names of the keys, I don't want to use this solution directly, a...
SQLAlchemy - maximum column length
Hi, is it possible in SQLAlchemy to enforce maximum string length of value assigned to mapped column? All I want is to raise an exception if an assigned string value is longer then the length of the corresponding table column of type STRING. Thank you ...
What exactly is Metadata in SQLAlchemy?
What exactly is MetaData SQLAlchemy? I have hard time understanding. And what is the difference between MetaData and Engine? Thanks, Boda Cydo. ...
Error using MSSet (MySQL SET extension) in SQLAlchemy
Hi, I'm trying to use MSSet datatype in SQL Alchemy I'm usinge declarative extension, here is the class: class SMSEmailOption(Base): __tablename__ = 'sms_email_options' id = Column(Integer, primary_key=True) sms_active = Column(Integer) email_active = Column(Integer) delivery_time = Column(Date) days = Column(...
SqlAlchemy optimizations for read-only object models.
I have a complex network of objects being spawned from a sqlite database using sqlalchemy ORM mappings. I have quite a few deeply nested: for parent in owner.collection: for child in parent.collection: for foo in child.collection: do lots of calcs with foo.property My profiling is showing me that the sqlalc...
Keeping ORM with stored procedures
Hello, I am developing a Python web app using sqlalchemy to communicate with mysql database. So far I have mostly been using sqlalchemy's ORM layer to speak with the database. The greatest benefit to me of ORM has been the speed of development, not having to write all these sql queries and then map them to models. Recently, however, I'...
How to increase a counter in SQLAlchemy
Suppose I have table tags which has a field count that indicates how many items have been tagged with the given tag. How do I increase this counter in SQLAlchemy after I add a new item with an existing tag? With plain SQL I would do the following: INSERT INTO `items` VALUES (...) UPDATE `tags` SET count=count+1 WHERE tag_id=5 But ho...