On Friday, 19 April, 2019 18:05, Andy Hegedus <andy.hege...@aghanalytics.com> wrote:
>Newbie here so please be gentle. That's OK. Just to let you know this is a little off topic since it is more discussing the Python pysqlite2 (sqlite3) wrapper than being about SQLite3 iteself -- however -- I will endeavour to answer your questions. >Just started and created data base, actually a big one with 10+tables >and 100GB of data. Basically patent database. All works and DB >Browser for SQLite works without issue. >Testing within Python using Jupyter from Anaconda using Python 3.7.3 Firstly, the version of SQLite3.dll included with most Python versions is rather old, and the default sqlite3 interface is oftentimes not quite optimal (it behaves somewhat confusingly and does silly magical behaviour that is not obvious. the most egregious is the weird transaction handling which you can fix by turning it off with isolation_level=None when opening the database and then using your own transactions). You *may* want to look at APSW (Another Python SQLite3 Wrapper) written by Roger Binns who is on this mailing list. The main page is here: https://github.com/rogerbinns/apsw And the documentation is here: https://rogerbinns.github.io/apsw/pysqlite.html Roger's documentation is more thorough than the Python sqlite3 documentation, so reading it is probably helpful even if you are not going to use it. >Code thus far >import sqlite3 >db= sqlite3.connect('/Volumes/Patents/sqlitetest/test.db’) >cursor = db.cursor() >cursor.execute('''Select * FROM wipo_field'’') >for row in cursor: > print(row[0],row[1]) > >1 Electrical engineering >10 Instruments >11 Instruments >12 Instruments >13 Instruments >14 Chemistry >15 Chemistry >16 Chemistry >17 Chemistry >18 Chemistry >19 Chemistry >2 Electrical engineering >20 Chemistry >Good so far. Now here is the issue I now issue this command just for >testing and learning purposes. Note the only difference is that I am >asking for one column instead of two. >for row in cursor: > print(row[1]) >I get nothing. No response/output. db.cursor() returns a Cursor object which is really nothing more than an iterator object that can execute an SQLite3 statement. By default the Cursor is not connected to an SQLite3 statement and attempting to iterate and "empty" Cursor gets nothing back. You associate a compiled SQLite3 statement object with a Cursor by using the .execute(...) method. This compiles the statement (with sqlite3_prepare_v2), optionally binds the parameters to the statement, and prepares to execute it. It return the same Cursor object, which is now a Python "generator" object which will "generate" the row tuples when you iterate over it. Once you have completed the iterations, the "generator" is done and cannot return more data. If you want to retrieve the rows again, you need to recreate the statement and generator by using the .execute() method again. >If I now execute this again >cursor.execute('''Select * FROM wipo_field'’') >and follow with >for row in cursor: > print(row[1]) >I get this as expected >Electrical engineering >Instruments >Instruments >Instruments >Instruments >Chemistry >Chemistry >Chemistry >Chemistry >Chemistry >Chemistry >But if I now follow that command with this >for row in cursor: > print(row[0],row[1]) >I again get no response/output. >So is the cursor a 1 use only object? Does it null itself out after >the first reading? No, it is a "row generator". Iterating over it will return one at a time the rows generated by the SQL statement. Once the generator has run to completion, you must give it a new statement to execute. You can, for example, return all the rows in a list and then iterate over the list, without re-executing the SQL statement. Note that if your query generated billions of rows you would have those billions of rows in memory "all at once" instead of "one at a time". data = list(cursor.execute('select * from wipo_field')) for row in data: print(row[0], row[1]) for row in data: print(row[1]) Hope this helps. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
