Re: [sqlite] Guidance with Python and nested cursors

[Joseph L. Casale]

> It is perfectly allowed to open multiple cursors against a single connection. 
>  You can only execute one
> statement per cursor at a time, but you can have multiple cursors running 
> from the same connection:
> 
> cr1 = cn.cursor()
> cr2 = cn.cursor()
> 
> cr1.execute('select ...')
> while True:
> row = cr1.fetchone()
> if not row:
> break
> ...
> cr2.execute('INSERT ...')
> 
> for example.  If you are inserting into one of the tables used in the outer 
> select, simply make sure that
> select has an order by with a + in front of one of the column names to avoid 
> side effects (ie, changes
> made to the database by the insert are visible to all statements/cursors on 
> that connection even before
> those changes are committed).

Right,
I read this can be a problem, but I ran several tests validating results and it 
worked perfectly.

Thank you very much for the confirmation.
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Guidance with Python and nested cursors

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 17/07/13 18:37, Keith Medcalf wrote:
> cr1 = cn.cursor() cr2 = cn.cursor()
> 
> cr1.execute('select ...') while True: row = cr1.fetchone() if not row: 
> break

While that is normal DBAPI, it is far more verbose and unpythonic than the
SQLite wrappers (disclosure: I'm the author of one of them).  Code can use
iterators and look something like this:

  for id,one,two in db.cursor().execute("select id, one, two from XXX"):
   three=one+two
   db.cursor().execute("insert into  ", (three,))

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHnV8sACgkQmOOfHg372QTfbwCeIYJTKpLSn+RGlsDcMA/S18WI
4QcAoOHgcf3mcRwfWwR2IrB87DbS0oQY
=gtkt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Guidance with Python and nested cursors

[Keith Medcalf]

It is perfectly allowed to open multiple cursors against a single connection.  
You can only execute one statement per cursor at a time, but you can have 
multiple cursors running from the same connection:

cr1 = cn.cursor()
cr2 = cn.cursor()

cr1.execute('select ...')
while True:
row = cr1.fetchone()
if not row:
break
...
cr2.execute('INSERT ...')

for example.  If you are inserting into one of the tables used in the outer 
select, simply make sure that select has an order by with a + in front of one 
of the column names to avoid side effects (ie, changes made to the database by 
the insert are visible to all statements/cursors on that connection even before 
those changes are committed).

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Joseph L. Casale
> Sent: Wednesday, 17 July, 2013 13:41
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Guidance with Python and nested cursors
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org on behalf of Petite Abeille
> Sent: Wednesday, July 17, 2013 1:25 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Guidance with Python and nested cursors
> 
> On Jul 17, 2013, at 9:07 PM, Joseph L. Casale
> <jcas...@activenetwerx.com> wrote:
> 
> >> I am using Python to query a table for all its rows, for each row,
> I query related rows from a
> >> second table, then perform some processing and insert in to a third
> table.
> >>
> >> What is the technically correct approach for this?
> >
> >From the above outline, one SQL statement:
> 
> Hi,
> Problem is I need to perform some Python processing of the data, then
> insert.
> 
> Thanks!
> jlc
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Guidance with Python and nested cursors

[Keith Medcalf]

If the table you are modifying is not being used in the in progress queries 
then you can just do the inserts -- wrapping the whole process in a single big 
transaction if you like.  

If the inserts may affect an open cursor (query) then you can specify an ORDER 
BY on the affected query, and put a + in front of one of the column names.  
This has the effect of forcing the query results to be put into a temp b-tree 
on the first call to step, and your row retrievals cursor through the temp 
table (as it were).  This is effectively the same as retrieving the entire 
result set into a list except that the buffer is within the database engine and 
not within your application.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Joseph L. Casale
> Sent: Wednesday, 17 July, 2013 13:07
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Guidance with Python and nested cursors
> 
> I am using Python to query a table for all its rows, for each row, I
> query related rows from a
> second table, then perform some processing and insert in to a third
> table.
> 
> What is the technically correct approach for this? I would rather not
> accumulate all of the first
> tables data to make one off selects from table two, then insert to
> table three. I would prefer to
> iterate over table one etc.
> 
> How does one setup the connection and cursor for this style of task?
> 
> Thanks for any guidance,
> jlc
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Guidance with Python and nested cursors

[Joseph L. Casale]

From: sqlite-users-boun...@sqlite.org on behalf of Petite Abeille
Sent: Wednesday, July 17, 2013 1:25 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Guidance with Python and nested cursors

On Jul 17, 2013, at 9:07 PM, Joseph L. Casale <jcas...@activenetwerx.com> wrote:

>> I am using Python to query a table for all its rows, for each row, I query 
>> related rows from a
>> second table, then perform some processing and insert in to a third table.
>>
>> What is the technically correct approach for this?
>
>From the above outline, one SQL statement:

Hi,
Problem is I need to perform some Python processing of the data, then insert.

Thanks!
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Guidance with Python and nested cursors

[Petite Abeille]

On Jul 17, 2013, at 9:07 PM, Joseph L. Casale  wrote:

> I am using Python to query a table for all its rows, for each row, I query 
> related rows from a
> second table, then perform some processing and insert in to a third table.
> 
> What is the technically correct approach for this? 

>From the above outline, one SQL statement:

insert
intotableA
(
  ...
)

select  ...
fromtableB

jointableC
on  tableC... = tableB…

"When in Rome, do as the Romans do" -- Ambrose, allegedly

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users