[sqlalchemy] will bulk insertion of data is auto committed for each row insertion ????
Hi all , I am trying bulk data into mysql database through sqlalchemy. In the table there may be primary key values before. If we get the same primary key values we get Integrity Error so I am using transaction management here. But while inserting bulk data the rows inserted before this error werent get rolled back only the row where error is got is rolled back. Can anyone please help me out in gettin gout this issue. Thanks in advance -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/mxq1e7yJoOQJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] will bulk insertion of data is auto committed for each row insertion ????
sounds like you aren't using InnoDB and therefore there's really no transaction in place at all. The (often default) MyISAM engine of MySQL doesn't support transactions. On Sep 20, 2012, at 7:19 AM, kampy wrote: Hi all , I am trying bulk data into mysql database through sqlalchemy. In the table there may be primary key values before. If we get the same primary key values we get Integrity Error so I am using transaction management here. But while inserting bulk data the rows inserted before this error werent get rolled back only the row where error is got is rolled back. Can anyone please help me out in gettin gout this issue. Thanks in advance -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/mxq1e7yJoOQJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] will bulk insertion of data is auto committed for each row insertion ????
HI Michael Bayer,
I am using InnoDB only.
users_table = Table(XX',metadata,
Column('id1, Integer, primary_key=True,
autoincrement=False),
Column('id2', String(255), primary_key=True),
Column('d3', String(255), primary_key=True),
Column('t_id',String(255)),
Column('ie_name',String(255)),
Column('n_name',String(255)),
Column('in_status',String(255)),
Column('ud_at',DateTime),
Column('c_at',DateTime),
Column('d_at',DateTime),
mysql_engine='InnoDB'
)
On Thursday, 20 September 2012 19:06:28 UTC+5:30, Michael Bayer wrote:
sounds like you aren't using InnoDB and therefore there's really no
transaction in place at all. The (often default) MyISAM engine of MySQL
doesn't support transactions.
On Sep 20, 2012, at 7:19 AM, kampy wrote:
Hi all ,
I am trying bulk data into mysql database through sqlalchemy. In the table
there may be primary key values before. If we get the same primary key
values we get Integrity Error so I am using transaction management here.
But while inserting bulk data the rows inserted before this error werent
get rolled back only the row where error is got is rolled back. Can anyone
please help me out in gettin gout this issue.
Thanks in advance
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/mxq1e7yJoOQJ.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
To unsubscribe from this group, send email to
sqlalchemy+...@googlegroups.com javascript:.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/6vP2FZqsZEYJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] will bulk insertion of data is auto committed for each row insertion ????
i created table manually not in the code .
On Thursday, 20 September 2012 20:00:04 UTC+5:30, Michael Bayer wrote:
if rows that were inserted in the transaction are not removed when the
transaction rolls back, then that's the only possibility here. are you
sure you emitted a CREATE TABLE statement for the users_table definition
which included InnoDB ?
On Sep 20, 2012, at 10:20 AM, kampy wrote:
HI Michael Bayer,
I am using InnoDB only.
users_table = Table(XX',metadata,
Column('id1, Integer, primary_key=True,
autoincrement=False),
Column('id2', String(255), primary_key=True),
Column('d3', String(255), primary_key=True),
Column('t_id',String(255)),
Column('ie_name',String(255)),
Column('n_name',String(255)),
Column('in_status',String(255)),
Column('ud_at',DateTime),
Column('c_at',DateTime),
Column('d_at',DateTime),
mysql_engine='InnoDB'
)
On Thursday, 20 September 2012 19:06:28 UTC+5:30, Michael Bayer wrote:
sounds like you aren't using InnoDB and therefore there's really no
transaction in place at all. The (often default) MyISAM engine of MySQL
doesn't support transactions.
On Sep 20, 2012, at 7:19 AM, kampy wrote:
Hi all ,
I am trying bulk data into mysql database through sqlalchemy. In the
table there may be primary key values before. If we get the same primary
key values we get Integrity Error so I am using transaction management
here. But while inserting bulk data the rows inserted before this error
werent get rolled back only the row where error is got is rolled back. Can
anyone please help me out in gettin gout this issue.
Thanks in advance
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/mxq1e7yJoOQJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/6vP2FZqsZEYJ.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
To unsubscribe from this group, send email to
sqlalchemy+...@googlegroups.com javascript:.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/PCyh3Gh31MwJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] will bulk insertion of data is auto committed for each row insertion ????
this would need to include engine='InnoDB' in the CREATE TABLE statement.
On Sep 20, 2012, at 10:40 AM, kampy wrote:
i created table manually not in the code .
On Thursday, 20 September 2012 20:00:04 UTC+5:30, Michael Bayer wrote:
if rows that were inserted in the transaction are not removed when the
transaction rolls back, then that's the only possibility here. are you sure
you emitted a CREATE TABLE statement for the users_table definition which
included InnoDB ?
On Sep 20, 2012, at 10:20 AM, kampy wrote:
HI Michael Bayer,
I am using InnoDB only.
users_table = Table(XX',metadata,
Column('id1, Integer, primary_key=True,
autoincrement=False),
Column('id2', String(255), primary_key=True),
Column('d3', String(255), primary_key=True),
Column('t_id',String(255)),
Column('ie_name',String(255)),
Column('n_name',String(255)),
Column('in_status',String(255)),
Column('ud_at',DateTime),
Column('c_at',DateTime),
Column('d_at',DateTime),
mysql_engine='InnoDB'
)
On Thursday, 20 September 2012 19:06:28 UTC+5:30, Michael Bayer wrote:
sounds like you aren't using InnoDB and therefore there's really no
transaction in place at all. The (often default) MyISAM engine of MySQL
doesn't support transactions.
On Sep 20, 2012, at 7:19 AM, kampy wrote:
Hi all ,
I am trying bulk data into mysql database through sqlalchemy. In the table
there may be primary key values before. If we get the same primary key
values we get Integrity Error so I am using transaction management here.
But while inserting bulk data the rows inserted before this error werent
get rolled back only the row where error is got is rolled back. Can anyone
please help me out in gettin gout this issue.
Thanks in advance
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/mxq1e7yJoOQJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/6vP2FZqsZEYJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/PCyh3Gh31MwJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] SELECT * FROM function() with declarative
I've googled around can't seem to find an answer to this, so hopefully someone knows how to do it here. I'm using PostgreSQL and I have a PL/PGSQL function that filters and modifies a particular table based on a number of conditions and then returns a set of rows as the result. This pattern has allowed the system to use the functions as if they were tables so that joins can still be done on the resulting values. So instead of: SELECT * FROM table1 INNER JOIN table2 ON table1.t2_id = table2.id I do: SELECT * FROM my_function( ...args... ) as table1 INNER JOIN table2 ON table1.t2_id = table2.id That part works ok in plain SQL (and as well in the system I'm converting from) So now with SQLAlchemy I have my declarative definitions for those tables: class Table1(Base): __tablename__ = 'table1' id = Column() t2_id = Column(ForeignKey()) table2 = Relationship( ... ) # Join condition is specified explicitly class Table2(Base); __tablename__ = 'table2' id = Column() and I'm trying to figure out how I would execute a query that looks like this: result = session.query(Table1).join(Table1.table2).options(contains_eager(Table1.table2)) but using a function to 'fake' Table1 instead. So basically I'm attempting to get SQLAlchemy to treat the result of my function as if it was the normal Table1 object. I've tried using select_from() to inject my call to func.my_function() but that doesn't seem to work and since what I'm doing seems like it might be tricky (or not portable across SQL) I thought I'd ask if it's even possible. Thanks for any help! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/bYCl5PH0yNIJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SELECT * FROM function() with declarative
On Sep 20, 2012, at 11:49 AM, David McKeone wrote: I've googled around can't seem to find an answer to this, so hopefully someone knows how to do it here. I'm using PostgreSQL and I have a PL/PGSQL function that filters and modifies a particular table based on a number of conditions and then returns a set of rows as the result. This pattern has allowed the system to use the functions as if they were tables so that joins can still be done on the resulting values. So instead of: SELECT * FROM table1 INNER JOIN table2 ON table1.t2_id = table2.id I do: SELECT * FROM my_function( ...args... ) as table1 INNER JOIN table2 ON table1.t2_id = table2.id That part works ok in plain SQL (and as well in the system I'm converting from) So now with SQLAlchemy I have my declarative definitions for those tables: class Table1(Base): __tablename__ = 'table1' id = Column() t2_id = Column(ForeignKey()) table2 = Relationship( ... ) # Join condition is specified explicitly class Table2(Base); __tablename__ = 'table2' id = Column() and I'm trying to figure out how I would execute a query that looks like this: result = session.query(Table1).join(Table1.table2).options(contains_eager(Table1.table2)) but using a function to 'fake' Table1 instead. So basically I'm attempting to get SQLAlchemy to treat the result of my function as if it was the normal Table1 object. I've tried using select_from() to inject my call to func.my_function() but that doesn't seem to work and since what I'm doing seems like it might be tricky (or not portable across SQL) I thought I'd ask if it's even possible. Thanks for any help! Perhaps the easiest way is to create a view: CREATE VIEW table1 AS SELECT * FROM my_function(...); Cheers, M -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SELECT * FROM function() with declarative
you can make a function act like a table by creating a select() out of it, check out the fifth example at http://docs.sqlalchemy.org/en/rel_0_7/core/tutorial.html#functions On Sep 20, 2012, at 1:09 PM, David McKeone wrote: Hi M, Is creating something in the database the only way to do it? How would I coerce the view's return type into my object? How do I substitute the view in the FROM part of my clause instead? On Thursday, September 20, 2012 5:52:28 PM UTC+2, A.M. wrote: On Sep 20, 2012, at 11:49 AM, David McKeone wrote: I've googled around can't seem to find an answer to this, so hopefully someone knows how to do it here. I'm using PostgreSQL and I have a PL/PGSQL function that filters and modifies a particular table based on a number of conditions and then returns a set of rows as the result. This pattern has allowed the system to use the functions as if they were tables so that joins can still be done on the resulting values. So instead of: SELECT * FROM table1 INNER JOIN table2 ON table1.t2_id = table2.id I do: SELECT * FROM my_function( ...args... ) as table1 INNER JOIN table2 ON table1.t2_id = table2.id That part works ok in plain SQL (and as well in the system I'm converting from) So now with SQLAlchemy I have my declarative definitions for those tables: class Table1(Base): __tablename__ = 'table1' id = Column() t2_id = Column(ForeignKey()) table2 = Relationship( ... ) # Join condition is specified explicitly class Table2(Base); __tablename__ = 'table2' id = Column() and I'm trying to figure out how I would execute a query that looks like this: result = session.query(Table1).join(Table1.table2).options(contains_eager(Table1.table2)) but using a function to 'fake' Table1 instead. So basically I'm attempting to get SQLAlchemy to treat the result of my function as if it was the normal Table1 object. I've tried using select_from() to inject my call to func.my_function() but that doesn't seem to work and since what I'm doing seems like it might be tricky (or not portable across SQL) I thought I'd ask if it's even possible. Thanks for any help! Perhaps the easiest way is to create a view: CREATE VIEW table1 AS SELECT * FROM my_function(...); Cheers, M -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/4EQ3O5IWOX0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
