Re: [sqlalchemy] Creating one-to-many relationship: child class returns empty list after trying to enter data
On 10 Aug 2013, at 03:42, csdr...@gmail.com 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.
Re: [sqlalchemy] Why no Raw Query Builder?
Thank you so much. Another question:
$this-select(col1);
if(1 0) {
$this-select(col2, col3);
}
*$this-from(tbl)-where(1 = 1);*
if( 2 1) {
*$this-where(2 1);*
}
$this-left_outer_join(tbl2, tbl2.t_id = tbl.id);
// output: SELECT col1, col2, col3
// FROM tbl
// LEFT OUTER JOIN tbl2
// ON tbl2.t_id = tbl.id
// WHERE 1=1 AND 2 1
On sqlalchemy?
s = select([col1])
if 1 0:
s.append_column(col2, col3)
s.from(tbl) # not found from(...) in sqlalchemy.sql.expression.Select or
_SelectBase
s.where(1 = 1)
if 2 1:
s.where(2 1)
s.outerjoin(tbl2, tbl2.t_id = tbl.id) # not works
print s
Thank you in advance!
On Saturday, August 3, 2013 11:38:48 PM UTC+7, Michael Bayer wrote:
On Aug 3, 2013, at 12:34 PM, Taba Taba beta...@gmail.com javascript:
wrote:
Hi all,
I switch from php to python I'm using Codeigniter Query Builder:
$this-db-select('*');
$this-db-from('blogs');
$this-db-join('comments', 'comments.id = blogs.id');
$this-db-limit(10, 20);
$query = $this-db-get();
// Produces:
// SELECT * FROM blogs
// JOIN comments ON comments.id = blogs.id
// LIMIT 20, 10
I need model name in string ('blog') not class name (Blog).
How can i do it with SQLAlchemy?
try reading the SQL Expression tutorial:
http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html
--
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.
Re: [sqlalchemy] update using query - joint table inheritance
On Aug 9, 2013, at 10:45 PM, Mark Eastwood markeastwoo...@gmail.com wrote:
It is easier to describe in code than in words.
absolutely, this is much preferred.
Can anyone please tell me if there is something that I am doing wrong? is
this a bug with sqlalchemy?
it's kind of a bug, yes, the biggest bug here is that the docs for
query.update() are very underdetailed in this respect, as there is a huge
caveat regarding multi-table mappings here.
your query will work on MySQL, Postgresql, and SQL Server if you do it like
this:
updated = query.filter(Parent.id==Child.id).update({'value': 4})
this because an UPDATE is traditionally against a single table only, however in
SQLA 0.8 the ability to support so-called multiple table updates was added, a
feature supported in very different ways by these three backends. So updating
against Child, which is really a combination of the parent, child tables
particularly since your WHERE criteria is against the parent, puts both tables
into the UPDATE statement, but the mechanism currently does not add the
joining criteria for the Parent to Child.Such an UPDATE wasn't even
possible prior to 0.8 and also won't work on backends like SQLite or Oracle at
all since they don't support multi-table updates.
These kinds of issues can be diagnosed by setting echo=True on your Engine,
then looking to see what's actually going wrong with the query.
but we need a ticket for this in trac b.c. the docs don't even provide a hint
of this.
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Why no Raw Query Builder?
On Sat, Aug 10, 2013 at 11:31 AM, Taba Taba betak...@gmail.com wrote:
Thank you so much. Another question:
$this-select(col1);
if(1 0) {
$this-select(col2, col3);
}
$this-from(tbl)-where(1 = 1);
if( 2 1) {
$this-where(2 1);
}
$this-left_outer_join(tbl2, tbl2.t_id = tbl.id);
// output: SELECT col1, col2, col3
// FROM tbl
// LEFT OUTER JOIN tbl2
// ON tbl2.t_id = tbl.id
// WHERE 1=1 AND 2 1
On sqlalchemy?
s = select([col1])
if 1 0:
s.append_column(col2, col3)
s.from(tbl) # not found from(...) in sqlalchemy.sql.expression.Select or
_SelectBase
s.where(1 = 1)
if 2 1:
s.where(2 1)
s.outerjoin(tbl2, tbl2.t_id = tbl.id) # not works
print s
Thank you in advance!
Where's the question?
I think your problem, though, is that you didn't read the tutorial[0].
Or if you did, not carefully enough.
[0] http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html#selecting
--
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] update using query - joint table inheritance
Hi Michael, Thankyou very much for your reply, which is exactly what I needed. I also saw the new ticket that was raised for documentation of this, thankyou zzzeek. Mark -- 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.
