[sqlalchemy] Selecting across columns with func.min()
Hi all,
I've searched through the documentation and google on this and haven't been
able to find an answer. I have the following class:
class Price(Base):
__tablename__ = prices
id = Column(Integer, primary_key = True)
company_id = Column(Integer, ForeignKey('companies.id'))
date = Column(DateTime, nullable=False)
close = Column(Float)
I'm trying to query Price for the minimum price during a certain period of
time. The query for that is:
session.query(func.min(Price.close)).join(Company).filter(and_(Company.ticker==AAPL,
Price.date=datetime object)).one()
But, how does one query the Price.date that corresponds to
func.min(Price.close)? I had thought I could do session.query(Price.date,
func.min(Price.close)... but that instead returned the first date in the
column alongside the min price.
I also thought that I could do:
session.query(Price.date).join(Company).filter(Company.ticker==AAPL).having(func.min(Price.adj_close)==low[0]).all()
But that returns the empty set for some reason. (Just as well--there has to
be an easier way to do this.)
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] Updating a one-to-many relationship
Thanks Simon -- that's what I was missing.
On Thursday, August 15, 2013 5:01:31 AM UTC-4, Simon King wrote:
(Sorry for any mistakes, I'm on my phone)
Think of the database structure that you've got: your creators table has
id, creator and company_id columns. company_id is a foreign key
pointing at the id column of the companies table.
This means that a single row in the creators table can only point at one
company. Also, multiple creators can point at the same company. In other
words, your many-to-one relationship is probably the opposite way round
from the way you've intended. SQLAlchemy uses the foreign keys to determine
the direction of the relationship, and so this is why Company.creator is a
list, and you are only ever seeing a single value stored for
Creator.companies.
You probably want to remove the creators.company_id column and put a
creator_id column on the companies table instead. This will mean that
each company points at a single creator, but multiple companies may point
at the same creator. In SQLAlchemy terms, Company.creator will now be a
scalar value rather than a list, and Creator.companies will be a list.
(Note that this means you'll need to change the code in your
Company.__init__ method so that it assigns to self.creator rather than
appending to it, and get rid of all the [0] indexing and so on)
Hope that helps,
Simon
On 15 Aug 2013, at 02:12, csd...@gmail.com javascript:
csd...@gmail.com javascript: wrote:
Simon, your idea about putting together a script is a good one. Please see
the attached. I think all these errors are related but I'm scratching my
head about what the problem is.
The reason I use self.creator[0] versus self.creator is for aesthetics.
And, to your point about creator not being a list, SQLAlchemy is treating
creator as a list-like object. Since any company can only have one creator,
I wasn't concerned about indexing creator[0].
a=session.query(Company).first()
a
Company1, created by mike
rather than
a=session.query(Company).first()
a
Company1, created by [mike]
a.creator
[mike]
More info on creator:
type(a.creator)
class 'sqlalchemy.orm.collections.InstrumentedList'
type(a.creator[0])
class '__main__.Creator'
Chris
On Wednesday, August 14, 2013 6:18:51 AM UTC-4, Simon King wrote:
I think you may be confused about the relationship properties you have
here. As far as I can tell, a Creator can have many companies, but
each Company has only one creator, correct? So Company.creator should
only ever be an instance of Creator (or None), whereas
Creator.companies should be a list.
In your __repr__ example:
class Creator(Base):
def __repr__(self):
return '%s' % self.creator
class Company(Base):
def __repr__(self):
return '%s, created by %s' % (self.company, self.creator[0])
Why are you using self.creator[0] here? self.creator is not a list,
it should either be an instance of Creator, or None.
Overriding __repr__ is also a good way to make debugging difficult.
For example, if you had a list of Creator instances and you printed
them at the python prompt, it would just look like a list of strings.
When I want extra information from __repr__, I normally write it
something like this:
def __repr__(self):
classname = type(self).__name__
return '%s name=%r' % (classname, self.name)
In your second example:
a=session.query(Creator).first()
a[0].companies
a.companies
Query.first() returns a single value, not a list. So typing a[0]
doesn't make any sense.
Please try to create a self-contained script that demonstrates your
problem. Here is a good example:
https://groups.google.com/d/msg/sqlalchemy/jQtIRJXVfH8/LgwX-bomEIQJ
Thanks,
Simon
On Wed, Aug 14, 2013 at 2:45 AM, csd...@gmail.com wrote:
I'm afraid there are still some bugs in here that hopefully you can
help
with.
class Creator(Base):
__tablename__ = creators
id = Column(Integer, primary_key = True)
company_id = Column(Integer, ForeignKey('companies.id'))
creator = Column(String(100), nullable=False, unique=True)
def __init__(self, creator):
self.creator = creator
def __repr__(self):
return '%s' % self.creator # otherwise returns a single entry
list
for some reason (e.g. would display [user])
class Company(Base):
__tablename__ = companies
id = Column(Integer, primary_key = True)
company = Column(String(100), unique=True, nullable=False) #might
want
to revise string sizes at some point
creator = relationship(Creator, backref=companies,
cascade=all)
def __init__(self, company, creator):
self.company = company
#self.creator.append(Creator(creator))
existing_creator =
session.query(Creator).filter_by(creator=creator).first()
Re: [sqlalchemy] Updating a one-to-many relationship
Simon, your idea about putting together a script is a good one. Please see
the attached. I think all these errors are related but I'm scratching my
head about what the problem is.
The reason I use self.creator[0] versus self.creator is for aesthetics.
And, to your point about creator not being a list, SQLAlchemy is treating
creator as a list-like object. Since any company can only have one creator,
I wasn't concerned about indexing creator[0].
a=session.query(Company).first()
a
Company1, created by mike
rather than
a=session.query(Company).first()
a
Company1, created by [mike]
a.creator
[mike]
More info on creator:
type(a.creator)
class 'sqlalchemy.orm.collections.InstrumentedList'
type(a.creator[0])
class '__main__.Creator'
Chris
On Wednesday, August 14, 2013 6:18:51 AM UTC-4, Simon King wrote:
I think you may be confused about the relationship properties you have
here. As far as I can tell, a Creator can have many companies, but
each Company has only one creator, correct? So Company.creator should
only ever be an instance of Creator (or None), whereas
Creator.companies should be a list.
In your __repr__ example:
class Creator(Base):
def __repr__(self):
return '%s' % self.creator
class Company(Base):
def __repr__(self):
return '%s, created by %s' % (self.company, self.creator[0])
Why are you using self.creator[0] here? self.creator is not a list,
it should either be an instance of Creator, or None.
Overriding __repr__ is also a good way to make debugging difficult.
For example, if you had a list of Creator instances and you printed
them at the python prompt, it would just look like a list of strings.
When I want extra information from __repr__, I normally write it
something like this:
def __repr__(self):
classname = type(self).__name__
return '%s name=%r' % (classname, self.name)
In your second example:
a=session.query(Creator).first()
a[0].companies
a.companies
Query.first() returns a single value, not a list. So typing a[0]
doesn't make any sense.
Please try to create a self-contained script that demonstrates your
problem. Here is a good example:
https://groups.google.com/d/msg/sqlalchemy/jQtIRJXVfH8/LgwX-bomEIQJ
Thanks,
Simon
On Wed, Aug 14, 2013 at 2:45 AM, csd...@gmail.com javascript: wrote:
I'm afraid there are still some bugs in here that hopefully you can help
with.
class Creator(Base):
__tablename__ = creators
id = Column(Integer, primary_key = True)
company_id = Column(Integer, ForeignKey('companies.id'))
creator = Column(String(100), nullable=False, unique=True)
def __init__(self, creator):
self.creator = creator
def __repr__(self):
return '%s' % self.creator # otherwise returns a single entry
list
for some reason (e.g. would display [user])
class Company(Base):
__tablename__ = companies
id = Column(Integer, primary_key = True)
company = Column(String(100), unique=True, nullable=False) #might
want
to revise string sizes at some point
creator = relationship(Creator, backref=companies,
cascade=all)
def __init__(self, company, creator):
self.company = company
#self.creator.append(Creator(creator))
existing_creator =
session.query(Creator).filter_by(creator=creator).first()
#self.creator.append(existing_creator or Creator(creator))
if existing_creator:
print True
self.creator.append(existing_creator)
else:
self.creator.append(Creator(creator))
def __repr__(self):
return '%s, created by %s' % (self.company, self.creator[0])
1) Weird __repr__ error:
class Creator(Base):
def __repr__(self):
return '%s' % self.creator
class Company(Base):
def __repr__(self):
return '%s, created by %s' % (self.company, self.creator[0])
c=Company(Company1, mike)
session.add(c)
c=Company(Company2, mike)
True
session.add(c)
c=Company(Company3, john)
session.add(c)
c=Company(Company4, mike)
True
session.add(c)
session.query(Company).all()
[Traceback (most recent call last):
File stdin, line 1, in module
File stdin, line 17, in __repr__
However, if I divide the query lines among every add() statement, there
is
no __repr__ error.
c=Company(Company1, mike)
session.add(c)
session.query(Company).all()
[Company1, created by mike]
c=Company(Company2, mike)
True
session.add(c)
session.query(Company).all()
[Company1, created by mike, Company2, created by mike]
c=Company(Company3, john)
session.add(c)
session.query(Company).all()
[Company1, created by mike, Company2, created by mike, Company3, created
by
john]
c=Company(Company4, mike)
True
session.add(c)
Re: [sqlalchemy] Updating a one-to-many relationship
I'm afraid there are still some bugs in here that hopefully you can help
with.
class Creator(Base):
__tablename__ = creators
id = Column(Integer, primary_key = True)
company_id = Column(Integer, ForeignKey('companies.id'))
creator = Column(String(100), nullable=False, unique=True)
def __init__(self, creator):
self.creator = creator
def __repr__(self):
return '%s' % self.creator # otherwise returns a single entry list
for some reason (e.g. would display [user])
class Company(Base):
__tablename__ = companies
id = Column(Integer, primary_key = True)
company = Column(String(100), unique=True, nullable=False) #might want
to revise string sizes at some point
creator = relationship(Creator, backref=companies, cascade=all)
def __init__(self, company, creator):
self.company = company
#self.creator.append(Creator(creator))
existing_creator =
session.query(Creator).filter_by(creator=creator).first()
#self.creator.append(existing_creator or Creator(creator))
if existing_creator:
print True
self.creator.append(existing_creator)
else:
self.creator.append(Creator(creator))
def __repr__(self):
return '%s, created by %s' % (self.company, self.creator[0])
1) Weird __repr__ error:
class Creator(Base):
def __repr__(self):
return '%s' % self.creator
class Company(Base):
def __repr__(self):
return '%s, created by %s' % (self.company, self.creator[0])
c=Company(Company1, mike)
session.add(c)
c=Company(Company2, mike)
True
session.add(c)
c=Company(Company3, john)
session.add(c)
c=Company(Company4, mike)
True
session.add(c)
session.query(Company).all()
[Traceback (most recent call last):
File stdin, line 1, in module
File stdin, line 17, in __repr__
However, if I divide the query lines among every add() statement, there is
no __repr__ error.
c=Company(Company1, mike)
session.add(c)
session.query(Company).all()
[Company1, created by mike]
c=Company(Company2, mike)
True
session.add(c)
session.query(Company).all()
[Company1, created by mike, Company2, created by mike]
c=Company(Company3, john)
session.add(c)
session.query(Company).all()
[Company1, created by mike, Company2, created by mike, Company3, created by
john]
c=Company(Company4, mike)
True
session.add(c)
session.query(Company).all()
[Company1, created by mike, Company2, created by mike, Company3, created by
john, Company4, created by mike]
2) Creator.companies only shows the most recently added company:
session.query(Company).all()
[Company1, created by mike, Company2, created by mike, Company3, created by
john, Company4, created by mike]
session.query(Creator).all()
[mike, john]
a=session.query(Creator).first()
a[0].companies
a.companies
Company4, created by mike
3) Weird Company.creator error:
session.query(Company).all()
[Company1, created by mike, Company2, created by mike, Company3, created by
john, Company4, created by mike]
session.query(Company.creator).all()
[(False,), (False,), (False,), (False,), (True,), (False,), (False,),
(True,)]
a=session.query(Company).first()
a.creator
[mike]
Anyone have any ideas?
--
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] Issue with session.expunge with mutually dependent tables
Thanks Michael! On Sunday, August 11, 2013 4:33:20 PM UTC-4, Michael Bayer wrote: On Aug 11, 2013, at 3:06 PM, csd...@gmail.com javascript: wrote: I'm having problem with the following code which is designed to remove an object identified as malformed prior to session.commit(): if tickers[x] not in existing_tickers and company_names[x] not in existing_companies: company = Company(tickers[x], company_names[x], creators[x], links[x]) session.add(company) new_companies.append(company) bad_ticks = [] for company in new_companies: if company.get_prices() == False: bad_ticks.append(company) for tick in bad_ticks: session.expunge(tick) session.commit() I receive the following error: /Library/Python/2.7/site-packages/sqlalchemy/orm/dependency.py:746: SAWarning: Object of type Company not in session, add operation along 'Creator.companies' won't proceed uowcommit, add) this is not an error, it's a warning. It means you have a Company object inside of the companies collection of a Creator; the Creator object is being flushed. However, as the unit of work traverses the companies collection, it will skip this particular Company object since it is not part of this Session and continue with the rest of the collection. There's no error, it's just a warning that this might not be what you want. My guess is that the Company is getting expunged from the session but the corresponding Creator isn't, if you want Creator to be expunged when a collected Company is expunged, you'd need to add the expunge cascade to the Company.creator relationship: class Company(Base): # ... creator = relationship(Creator, cascade=save-update, merge, expunge, ...) -- 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] Updating a one-to-many relationship
I have another question about a piece of code that I posted the other day.
Namely, I have a one-to-many relationship between Creator and Company. A
Creator can have a relationship with multiple Companies but any one Company
can have a relationship with only one Creator.
class Company(Base):
__tablename__ = companies
id = Column(Integer, primary_key = True)
company = Column(String(100), unique=True, nullable=False)
creator = relationship(Creator, backref=companies, cascade=all)
def __init__(self, company, creator):
self.company = company
self.creator.append(Creator(creator))
class Creator(Base):
__tablename__ = creators
company_id = Column(Integer, ForeignKey('companies.id'))
creator = Column(String(100), nullable=False, unique=True)
def __init__(self, creator):
self.creator = creator
So, to create a Company, the code calls company = Company(company name,
creator name) and that in turn calls Creator().
The problem is that the Companies get added one by one, and if a new
company being entered has a Creator with a name of a preexisting company,
SQLalchemy errors due to the unique=True flag:
sqlalchemy.exc.IntegrityError: (IntegrityError) (1062, Duplicate entry
'Viking' for key 'creator') 'INSERT INTO creators (company_id, creator)
VALUES (%s, %s)' (17L, u'Viking')
If unique=True isn't enabled, it will create another Creator of the same
name. Instead, the code should reflect the additional Company assigned to
this particular Creator. How might I go about fixing this?
Thanks!
--
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] Updating a one-to-many relationship
Sorry I don't understand what you're trying to say.
If the Creator already exists, and I'm to append it again, isn't that the
same as what my code is currently doing? (That is, appending in every
instance.) I don't see how this wouldn't result in the same error message.
And what would it mean to create a new one and append that? I don't know
what this code would look like.
Apologies if I'm being dense.
On Monday, August 12, 2013 9:33:31 PM UTC-4, Tim wrote:
In `Company.__init__()`, instead of blindly creating a new `Creator`
instance, you need to first query for an existing Creator with that name.
If it exists, append it, otherwise, create a new one and append that.
--
Tim Van Steenburgh
On Monday, August 12, 2013 at 9:26 PM, csd...@gmail.com javascript:wrote:
I have another question about a piece of code that I posted the other day.
Namely, I have a one-to-many relationship between Creator and Company. A
Creator can have a relationship with multiple Companies but any one Company
can have a relationship with only one Creator.
class Company(Base):
__tablename__ = companies
id = Column(Integer, primary_key = True)
company = Column(String(100), unique=True, nullable=False)
creator = relationship(Creator, backref=companies, cascade=all)
def __init__(self, company, creator):
self.company = company
self.creator.append(Creator(creator))
class Creator(Base):
__tablename__ = creators
company_id = Column(Integer, ForeignKey('companies.id'))
creator = Column(String(100), nullable=False, unique=True)
def __init__(self, creator):
self.creator = creator
So, to create a Company, the code calls company = Company(company name,
creator name) and that in turn calls Creator().
The problem is that the Companies get added one by one, and if a new
company being entered has a Creator with a name of a preexisting company,
SQLalchemy errors due to the unique=True flag:
sqlalchemy.exc.IntegrityError: (IntegrityError) (1062, Duplicate entry
'Viking' for key 'creator') 'INSERT INTO creators (company_id, creator)
VALUES (%s, %s)' (17L, u'Viking')
If unique=True isn't enabled, it will create another Creator of the same
name. Instead, the code should reflect the additional Company assigned to
this particular Creator. How might I go about fixing this?
Thanks!
--
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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] Updating a one-to-many relationship
Very helpful, thanks Tim :)
On Monday, August 12, 2013 9:53:48 PM UTC-4, Tim wrote:
Ad, one more try:
existing_creator = DBSession.query(Creator).filter_by(name=creator).first()
--
Tim Van Steenburgh
On Monday, August 12, 2013 at 9:50 PM, Tim Van Steenburgh wrote:
Sorry, that should have been:
existing_creator =
DBSession(Creator).query.filter_by(creator=creator).first()
On Monday, August 12, 2013 at 9:49 PM, Tim Van Steenburgh wrote:
It's not the append that's causing the error, it's the fact that you're
creating a new Creator() instance, which ultimately results in an INSERT
statement being issued.
You want to append a Creator instance to `company.creator`, but you don't
necessarily want to make a new Creator every time you instantiate a
Company. If a Creator with the given name already exists, you'll want use
that instead.
So, roughly:
class Company(Base):
__tablename__ = companies
id = Column(Integer, primary_key = True)
company = Column(String(100), unique=True, nullable=False)
creator = relationship(Creator, backref=companies, cascade=all)
def __init__(self, company, creator):
self.company = company
existing_creator =
DBSession(Creator).query.filter_by(name=creator).first()
self.creator.append(existing_creator or Creator(creator))
--
Tim Van Steenburgh
On Monday, August 12, 2013 at 9:41 PM, csd...@gmail.com javascript:wrote:
Sorry I don't understand what you're trying to say.
If the Creator already exists, and I'm to append it again, isn't that the
same as what my code is currently doing? (That is, appending in every
instance.) I don't see how this wouldn't result in the same error message.
And what would it mean to create a new one and append that? I don't know
what this code would look like.
Apologies if I'm being dense.
On Monday, August 12, 2013 9:33:31 PM UTC-4, Tim wrote:
In `Company.__init__()`, instead of blindly creating a new `Creator`
instance, you need to first query for an existing Creator with that name.
If it exists, append it, otherwise, create a new one and append that.
--
Tim Van Steenburgh
On Monday, August 12, 2013 at 9:26 PM, csd...@gmail.com wrote:
I have another question about a piece of code that I posted the other day.
Namely, I have a one-to-many relationship between Creator and Company. A
Creator can have a relationship with multiple Companies but any one Company
can have a relationship with only one Creator.
class Company(Base):
__tablename__ = companies
id = Column(Integer, primary_key = True)
company = Column(String(100), unique=True, nullable=False)
creator = relationship(Creator, backref=companies, cascade=all)
def __init__(self, company, creator):
self.company = company
self.creator.append(Creator(creator))
class Creator(Base):
__tablename__ = creators
company_id = Column(Integer, ForeignKey('companies.id'))
creator = Column(String(100), nullable=False, unique=True)
def __init__(self, creator):
self.creator = creator
So, to create a Company, the code calls company = Company(company name,
creator name) and that in turn calls Creator().
The problem is that the Companies get added one by one, and if a new
company being entered has a Creator with a name of a preexisting company,
SQLalchemy errors due to the unique=True flag:
sqlalchemy.exc.IntegrityError: (IntegrityError) (1062, Duplicate entry
'Viking' for key 'creator') 'INSERT INTO creators (company_id, creator)
VALUES (%s, %s)' (17L, u'Viking')
If unique=True isn't enabled, it will create another Creator of the same
name. Instead, the code should reflect the additional Company assigned to
this particular Creator. How might I go about fixing this?
Thanks!
--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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] Issue with session.expunge with mutually dependent tables
I'm having problem with the following code which is designed to remove an
object identified as malformed prior to session.commit():
if tickers[x] not in existing_tickers and company_names[x] not in
existing_companies:
company = Company(tickers[x], company_names[x], creators[x], links[x])
session.add(company)
new_companies.append(company)
bad_ticks = []
for company in new_companies:
if company.get_prices() == False:
bad_ticks.append(company)
for tick in bad_ticks:
session.expunge(tick)
session.commit()
I receive the following error:
/Library/Python/2.7/site-packages/sqlalchemy/orm/dependency.py:746:
SAWarning: Object of type Company not in session, add operation along
'Creator.companies' won't proceed
uowcommit, add)
Class Company() has a relationship with Class Creator() (referenced in
error message). The relationship between Company and Creator is summarized
briefly as follows:
class Company(Base):
__tablename__ = companies
creator = relationship(Creator, backref=companies)
def __init__(self, ticker, company, creator, link):
self.creator.append(Creator(creator))
class Creator(Base):
__tablename__ = creators
company_id = Column(Integer, ForeignKey('companies.id'))
creator = Column(String(100), nullable=False)
A Creator can have a relationship with multiple Companies but any one
Company can have a relationship with only one Creator.
My guess is that the Company is getting expunged from the session but the
corresponding Creator isn't, and that's confusing MySql and/or sqlalchemy.
However, I don't know how to try to expunge the Creator object since it's
only ever called within the Company class. Anyone know the proper way to
fix this?
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] Creating one-to-many relationship: child class returns empty list after trying to enter data
Thank you! That worked.
On Saturday, August 10, 2013 5:39:31 AM UTC-4, Simon King wrote:
On 10 Aug 2013, at 03:42, csd...@gmail.com javascript: wrote:
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 haven't associated your Price instances with the Company instance. If
you looked at the database, all the company_id values would be NULL.
If you put self.prices.append(prices) as the last line of your loop, it
should all work. In fact, if you do that, the session.add will be
unnecessary because the prices will be automatically added to the session
(assuming that the company is already in the session).
Hope that helps,
Simon
--
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] 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.
