[sqlalchemy] update using query - joint table inheritance
Hi all,
I am getting some unexpected behaviour when trying to update selected rows
from a query when using joint table inheritance. Using MySQL, a query that
is filtered based on items in the parent table, does not honour this filter
when updating items in the child table (all children are updated, not only
those matching the filter). It is easier to describe in code than in words.
The code is attached.
In an attempt to make it easier to run, I did try and make the script use
sqlite instead of MySQL, however in this case an entirely different error
resulted. I have therefore included both (MySQL enabled, sqlite disabled by
default).
Can anyone please tell me if there is something that I am doing wrong? is
this a bug with sqlalchemy?
Thankyou,
Mark Eastwood
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
'''
Demonstrate a (bug?) in updating records via a query
I am getting some unexpected behaviour when trying to update selected rows from
a query when using joint table inheritance. Using MySQL, a query that is
filtered based on items in the parent table, does not honour this filter when
updating items in the child table (all children are updated, not only those
matching the filter).
In an attempt to make it easier to run, I did try and make the script use
sqlite instead of MySQL, however in this case an entirely different error
resulted. I have therefore included both (MySQL enabled, sqlite disabled by
default).
With MySQL
--
All records within the Child table get updated. What should happen is just the
one record that matches the query gets updated
With SQLite
---
The query raises an OperationalError
Other Debug Info
I have tried this on two different systems with the same result. There are:
System 1:
Operating system: Windows 7 64 bit
Python version = 3.3.1
SQLAlchemy version = 0.8.0
MySQL version = 5.6.13
- included mysqlconnector from windows installer (v1.0.11)
System 2:
Operating system: Windows 7 32 bit
Python v3.3.1
SQLAlchemy v0.8.2
Mariadb version = 5.5.32
MySQLConnector version = 1.0.11
To run this script, MySQL (or Mariadb) needs to be installed and a user by the
name of 'tester' with password 'test_password' needs to have access to the
database 'test'@'localhost'. Of course, you can change these.
'''
from sqlalchemy import *
from sqlalchemy.exc import DatabaseError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
###
# Uncomment one of the following engines
###
#engine = create_engine('sqlite://')
engine = create_engine('mysql+mysqlconnector://%s:%s@%s:%s/%s' % \
('tester', 'test_password', '127.0.0.1', '3306', 'test'))
#--
Base = declarative_base()
Base.metadata.bind = engine
session = sessionmaker(bind=engine)()
class Parent(Base):
__tablename__ = 'parent'
id = Column(INTEGER(), primary_key=True)
name = Column(VARCHAR(255), unique=True)
type = Column(VARCHAR(255))
__mapper_args__ = {'polymorphic_identity': 'Parent',
'polymorphic_on': type}
class Child(Parent):
__tablename__ = 'child'
__mapper_args__ = {'polymorphic_identity': 'Child'}
id = Column(INTEGER(), ForeignKey(Parent.id), primary_key=True)
value = Column(INTEGER(255))
def __repr__(self):
return '%s %s' % (self.name, self.value)
###
# Start with a clean database
###
# MySQL warns if these tables don't exist, despite using IF EXISTS
# SQLAlchemy interprets these as errors, just ignore
try:
session.execute('DROP TABLE IF EXISTS child, parent;')
except DatabaseError:
pass
###
# Build a simple table
###
Base.metadata.create_all()
session.add_all([Child(name='Steven', value=1),
Child(name='Mark', value=2),
Child(name='Daniel', value=3)])
session.flush()
print('We have three records in the database')
print(session.query(Child).all())
query = session.query(Child).filter(C
[sqlalchemy] Creating one-to-many relationship: child class returns empty list after trying to enter data
This is driving me a little crazy so hopefully someone here can help. This
is my first time working with sqlalchemy (v0.8). Python is v2.7.2 and MySQL
is v14.14.
The (heavily) summarized code is as follows:
class Price(Base):
__tablename__ = "prices"
id = Column(Integer, primary_key = True)
company_id = Column(Integer, ForeignKey('companies.id'))
date = Column(DateTime)
close = Column(Float)
volume = Column(Integer)
def __init__(self, date, close, volume):
self.date = date
self.close = close
self.volume = volume
class Company(Base):
__tablename__ = "companies"
id = Column(Integer, primary_key = True)
ticker = Column(String(10))
company = Column(String(100))
prices = relationship("Price")
def __init__(self, ticker, company):
self.ticker = ticker
self.company = company
def get_prices(self):
csv_data = get_csv()
for row in csv_data:
date = row[0].strip()
date = datetime.datetime.strptime(date, '%Y-%m-%d')
close = float(row[4])
volume = int(row[5])
prices = Price(date = date, close = close, volume = volume)
session.add(prices)
So, what the code* *should do is have a table of companies and a table of
daily pricing data on all companies. I want to be able to access the prices
via company.prices. Instead, when I try to do this, Python returns an empty
list []. I know that the data is getting picked up somewhere because I see
the SQL activity when I do session.commit(). I've also tried modifying the
get_prices() function by changing the prices variable to a list + append() and
then at the end of the for loop doing a session.add_all(prices), but that
didn't work either. What am I doing incorrectly?
Thanks,
Chris
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Boolean Mapper property from exists subquery
On Aug 8, 2013, at 7:47 PM, askel wrote:
> Hello everyone,
>
> I've been using SQLAlchemy for a while and it's been a pleasure. But
> sometimes I feel I can get a little more from it or do something more
> properly. For instance, imagine two tables with one-to-one relationship bosth
> sharing the same primary key and second table containing primary key only
> (please don't ask me why it wasn't made just a nullable boolean column to the
> first table).
>
> Base = declarative_base()
>
> class Item(Base):
> __table_name__ = 'items'
>
> i_id = Column(Integer, primary_key=True)
> # other columns omitted
>
> special_items = Table('special_items', Base.metadata,
> Column('i_id', Integer, ForeignKey('items.i_id'), primary_key=True),
> # no any other columns
> )
>
> Item.is_special = exists([1]).where(Item.i_id == special_items.c.i_id)
>
> And sometimes I need to use that is_special property in query filter as the
> following:
>
> query(Item).filter(and_(Item.is_special, ...))
>
> which works well in queries -- exists sub-query is added if/whenever
> is_special is mentioned in filter.
>
> What I would also like to see is is_special becoming a boolean property of
> Items instance. And it would be awesome to be able to assign to it so that it
> fires correct insert statement when session is flushed.
>
> To make first thing happen it sounds like I need to use hybrid_property but I
> couldn't figure out what is_special method should return when it's accessed
> as instance property. More over that property should be lazy, i. e. I don't
> want every query have that exists sub-query UNLESS it's explicitly used in
> filter or any other way.
>
> class Item(Base):
> ...
> @hybrid_property
> def is_special(self):
> return ???
>
> @is_special.statement
> def is_special(cls):
> return exists([1]).where(cls.i_id == special_items.c.i_id)
>
>
> I'd greatly appreciate if anybody gives me tip on how to make above happen
> (may be partially). It's not like I cannot live without it though.
seems like you'd create Item.special_items as a relationship() to the
special_items table (using some class), then the python is_special() just says
return bool(self.special_items). If you create the relationship() with
lazy=dynamic you could just say bool(self.special_items.count()).
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Mapping views as Table/ORM
On 09/08/2013 10:55, temp4...@gmail.com wrote: It seems that SQLAlchemy has no support for creating views by a "View" construct or something like that but you can map them as a "Table" or even an ORM class when applicable, and query from them, the problem is that SQLAlchemy will than try to create them as a new table when you issue metadata.create_all(), is there a convenient way around this, without having to pass a list of tables to create_all? Maybe this recipe will help. http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views Werner -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Mapping views as Table/ORM
It seems that SQLAlchemy has no support for creating views by a "View" construct or something like that but you can map them as a "Table" or even an ORM class when applicable, and query from them, the problem is that SQLAlchemy will than try to create them as a new table when you issue metadata.create_all(), is there a convenient way around this, without having to pass a list of tables to create_all? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
