Re: [sqlalchemy] Re: Outer joins?

[Alex Hall]

> On Mar 16, 2016, at 03:23, Jonathan Vanasco  wrote:
> 
> The database design you have is less than perfect.

I didn't make it, I came in long after it had been set up and now have to work 
with it. I can't re-do anything. They did it this way so that, for instance, a 
single attribute or attachment could apply to multiple items. If a thousand 
items are "large", and ten thousand have a "size" attribute, "size" and 'large" 
can both be written once instead of thousands of times. The problem is that it 
makes this hard to query, at least for someone not very experienced in DBA.

> The goal of having to reformat the relational DB into a CSV is less than 
> perfect.

The CSV is to give product details to our resellers in a format they can import 
automatically. As you say, flattening a relational database into what is 
essentially a single table isn't ideal, but it's what my work has always done 
so it's what I have to do. We have this running as a very convoluted SQL job I 
didn't write, with a ton of temporary tables, repeated code, and other fun 
things that make it hard to figure out what's going on. I know Python better 
than SQL, even if I have to learn SA and some DB concepts along the way, so 
this will be far easier to maintain once I get it working.

> 
> If I were you, I would think about 3 questions:
> 
> 1. How often do you have to run this?
Once a day or less. There's another script I'll eventually have to write that 
runs every fifteen minutes, but it has far less columns. Still, I'll meet to be 
able to grab items that lack related information, and the related information 
for items that have it.

> 2. Does it take too long?
No, I don't think it will. Even with the query that fails to get all items, it 
only takes 30 seconds total. I'm okay with it taking a few minutes.
> 3. Does it use up too much DB/Python memory?
That I don't know.
> 
> If this isn't a resource issue, and a 1x a day task... don't worry about it.  
> Let the non-optimal code run.
> 
> If you need to run this every 5 minutes, then I'd start to worry.
> 
> -- 
> 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 https://groups.google.com/group/sqlalchemy 
> .
> For more options, visit https://groups.google.com/d/optout 
> .

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] alchemy expressionconfusion in insert statement having jsonb field

[Simon King]

> On 16 Mar 2016, at 06:45, Krishnakant  wrote:
> 
> Dear all,
> I have a challenge which is confusing me.
> I have a table called voucher with the following field.
> (vid, vdate,Cr) where vid is integer, vdate  is date and Cr is jsonb in
> postgresql.
> Can some one tell me how do I write an sql expression insert query
> involving all the 3 fields?
> some thing like con.execute(voucher.insert(),...) I don't know how I do
> this.
> In the documentation there are 2 fields id and data and it seems id is
> auto_increment so inserting with only one field which is only json is
> easy, but here I have 3 fields involved in the insert.
> So how do I do this?
> Happy hacking.
> Krishnakant.Krishnakant.

I’m not sure I understand the question. Here are the docs for INSERT 
expressions:

http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html#executing-multiple-statements

you should be able to write something like this:

con.execute(voucher.insert(), vid=1, vdate=somedate, Cr=somedict)

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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Search in result

[Simon King]

> On 15 Mar 2016, at 22:13, Cecilio Ruiz  wrote:
> 
> Hi !
> 
> Assuming this result:
> 
> n = session.query(xx).all()
> 
> 
> How I can find the id 5 on the 'n' ?
> something like that.:
> 
> v = n.get(5)

When you call “all()”, you’ve already loaded all the rows from the database 
into Python. “n” is just a Python list at this point.

To load an object given its primary key you can use:

  v = session.query(xx).get(5)

This checks the session first to see if the object has already been loaded. If 
it hasn’t, it will query the database.

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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Outer joins?

[Jonathan Vanasco]

The database design you have is less than perfect.
The goal of having to reformat the relational DB into a CSV is less than 
perfect.

If I were you, I would think about 3 questions:

1. How often do you have to run this?
2. Does it take too long?
3. Does it use up too much DB/Python memory?

If this isn't a resource issue, and a 1x a day task... don't worry about 
it.  Let the non-optimal code run.

If you need to run this every 5 minutes, then I'd start to worry.

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.