[sqlalchemy] translating many-to-many relationship from CSV to database?
I have a CSV file with lots of redundant data which models many-to-many relationships. I'm needing to scrub the data as it is inserted into the database littered with unique constraints. Is there a way to insert the data once without querying for each object before inserting? I'm sure this is a common CSV translation nit, but it is new to me. Thanks for any insight provided! -- 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/d/optout.
[sqlalchemy] How to execute multiple statements as a single call?
Hi, I would like to execute multiple sqlalchemy statements in a single call so that all of the inserted rows would get the same time stamp. The same could be achieved with transactions, but out of lazyness I prefer not do a transactions there. I would like to do: query_one = tbl_foo.insert().values(some_values) query_two = tbl_bar.insert().values(other_values) list_of_statements = [query_one, query_two] engine.execute(list_of_statements) How to do it in SQLAlchemy (if it is possible at all)? Thanks in advance, Priidu Kull -- 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/d/optout.
Re: [sqlalchemy] translating many-to-many relationship from CSV to database?
typically I load all the records I'm going to be working with at once, and assemble them into dictionaries or other data structures so that as I process the csv data, I can look things up in the local dictionary for the modify. if the data you get from your CSV can all relate to the primary key of the objects, then you can use session.merge(), applying the existing primary key to a new object, which will be merged into the state of the existing row. This also emits a SELECT for those rows, but again if you load them up ahead of time, the check is against the local identity map. On Oct 13, 2014, at 2:22 AM, James Hartley jjhart...@gmail.com wrote: I have a CSV file with lots of redundant data which models many-to-many relationships. I'm needing to scrub the data as it is inserted into the database littered with unique constraints. Is there a way to insert the data once without querying for each object before inserting? I'm sure this is a common CSV translation nit, but it is new to me. Thanks for any insight provided! -- 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/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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] SQLAlchemy 0.9.8
Hey lists - SQLAlchemy release 0.9.8 is now available. As the 1.0 series is well underway, 0.9.8 continues to close out bugs that can still be applied without significant risk of destabilization. In contrast to 0.9.7 where we were still hitting upon regressions, 0.9.8 fixes a set of garden variety bugs, many of which are related to changes in DBAPIs (new SQL Server version strings, new Psycopg2 error conditions, changes in MySQL-connector-python and pg8000). That being said, 0.9.8 has a relatively large amount of fixes overall. Users should carefully review the changelog [1] to note which behaviors and issues are affected. We'd like to thank the many contributors who helped with this release. SQLAlchemy 0.9.8 is available on the download page [2]. [1] - http://docs.sqlalchemy.org/en/latest/changelog/changelog_09.html#change-0.9.8 [2] - http://www.sqlalchemy.org/download.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/d/optout.
Re: [sqlalchemy] how to add an order column to an existing table
in Oracle I'd update the table based on a SELECT from a window function:
SELECT row number OVER (PARTITION BY employee_id ORDER BY id) FROM employee
that should give you 1, 2, 3, ... for each row within an employee id group
On Oct 13, 2014, at 1:44 PM, Ofir Herzas herz...@gmail.com wrote:
I have found a solution although it seems a bit messy and doesn't work on
Oracle (throws ORA-00933: SQL command not properly ended):
my_table = sa.sql.table('my_table',
sa.Column('id', sa.BigInteger),
sa.Column('employee_id', sa.BigInteger),
sa.Column('level', sa.Integer)
)
level = sa.case([(sa.text(@curType), sa.text(@curRow := @curRow +
1))],
value=my_table.c.employee_id,
else_=sa.and_(sa.text(@curRow := 1), sa.text(@curType
:= my_table.employee_id))).label(rank)
temp = sa.alias(sa.select([my_table.c.id, level]).\
select_from(my_table).\
select_from(sa.text((SELECT @curRow:=0, @curType:='')
r)).\
order_by(my_table.c.employee_id, my_table.c.id))
query = my_table.update().values({my_table.c.level:
temp.c.rank}).where(my_table.c.id == temp.c.id)
engine.execute(query)
Can someone help me out here?
I need to fix this query to work on both mysql and Oracle and if there is a
better way to achieve what I need, all the better ...
On Sunday, October 12, 2014 7:16:27 PM UTC+3, Ofir Herzas wrote:
I have the following table:
my_table = sa.sql.table('my_table',
sa.Column('id', sa.BigInteger),
sa.Column('employee_id', sa.BigInteger)
)
and I want to add a 'rank' column using alembic. The addition itself is
pretty straight forward using add_column but I also need to populate that
column using sqlalchemy based on existing information (each employee_id has
its own rank ordered by the id).
For example, the following table:
idemployee_idrank
-----
1 1 1
2 1 2
5 1 3
3 2 1
4 3 1
6 3 2
The problem is similar to this one.
The only difference is that I need it working for sqlalchemy on top of both
mysql and oracle (a more generic solution is needed).
Any advice?
(BTW: I've posted the same question in SO here)
--
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/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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] how to add an order column to an existing table
On Oct 13, 2014, at 2:03 PM, Ofir Herzas herz...@gmail.com wrote:
Thanks Michael but I'm trying to find a generic syntax that would work on
both mysql and Oracle and AFAIK mysql does not support window functions.
there might not be one in this case. I'd try stack overflow to see what you
get from a SQL perspective.
The result SQL in Oracle is:
UPDATE my_table SET level=anon_1.rank FROM (SELECT my_table.id AS id, CASE
my_table.employee_id WHEN @curType THEN @curRow := @curRow + 1 ELSE @curRow
:= 1 AND @curType := my_table.employee_id END AS rank FROM my_table, (SELECT
@curRow:=0, @curType:=\'\') r ORDER BY my_table.employee_id, my_table.id)
anon_1 WHERE my_table.id = anon_1.id
And the exception is ORA-00933: SQL command not properly ended
I'm unfamiliar with this syntax (update ... from ... where). Was this
properly constructed?
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Monday, October 13, 2014 8:53 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] how to add an order column to an existing table
in Oracle I'd update the table based on a SELECT from a window function:
SELECT row number OVER (PARTITION BY employee_id ORDER BY id) FROM employee
that should give you 1, 2, 3, ... for each row within an employee id group
On Oct 13, 2014, at 1:44 PM, Ofir Herzas herz...@gmail.com wrote:
I have found a solution although it seems a bit messy and doesn't work on
Oracle (throws ORA-00933: SQL command not properly ended):
my_table = sa.sql.table('my_table',
sa.Column('id', sa.BigInteger),
sa.Column('employee_id', sa.BigInteger),
sa.Column('level', sa.Integer)
)
level = sa.case([(sa.text(@curType), sa.text(@curRow := @curRow +
1))],
value=my_table.c.employee_id,
else_=sa.and_(sa.text(@curRow := 1), sa.text(@curType
:= my_table.employee_id))).label(rank)
temp = sa.alias(sa.select([my_table.c.id, level]).\
select_from(my_table).\
select_from(sa.text((SELECT @curRow:=0, @curType:='')
r)).\
order_by(my_table.c.employee_id, my_table.c.id))
query = my_table.update().values({my_table.c.level:
temp.c.rank}).where(my_table.c.id ==temp.c.id)
engine.execute(query)
Can someone help me out here?
I need to fix this query to work on both mysql and Oracle and if there is a
better way to achieve what I need, all the better ...
On Sunday, October 12, 2014 7:16:27 PM UTC+3, Ofir Herzas wrote:
I have the following table:
my_table = sa.sql.table('my_table',
sa.Column('id', sa.BigInteger),
sa.Column('employee_id', sa.BigInteger)
)
and I want to add a 'rank' column using alembic. The addition itself is
pretty straight forward using add_column but I also need to populate that
column using sqlalchemy based on existing information (each employee_id has
its own rank ordered by the id).
For example, the following table:
idemployee_idrank
-----
1 1 1
2 1 2
5 1 3
3 2 1
4 3 1
6 3 2
The problem is similar to this one.
The only difference is that I need it working for sqlalchemy on top of both
mysql and oracle (a more generic solution is needed).
Any advice?
(BTW: I've posted the same question in SO here)
--
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 tosqlalchemy+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/d/optout.
--
You received this message because you are subscribed to a topic in the Google
Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/-rFuk112Q8I/unsubscribe.
To unsubscribe from this group and all its topics, 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/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 tosqlalchemy+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/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy
