Roger, Sorry for unintended slights. My haste and terseness may have confused matters.
Another long story (below), but if you are in a hurry, my question is: TERSE QUESTION Is the sqlite3_table_column_metadata() SQLite C API function also wrapped by the APSW Python Library? http://www.sqlite.org/capi3ref.html#sqlite3_table_column_metadata Or is there another way to get the primary key without scraping the SQL (string) field in the SQLite3 master table? LONG STORY I like Jessica Hamrick's Python dbtools library for the final conversion to the Python pandas library data structures. I have posted the necessary changes to her blog: I like it, but I need to import a VIEW from SQLite to Pandas (I want to shield my users from scary SQL JOINS and just present them one flat table). Underlying Python/SQLite libraries support SQL VIEWS (see SQLite mailing list "Views as Virtual Tables -- Command line vs. Called Interface". So, in your code, need to change cmd = "SELECT name FROM sqlite_master WHERE type='table'" to cmd = "SELECT name FROM sqlite_master WHERE type IN ('table','view')" SQLite VIEWs are read only (not update-able) so also need error msg-s if type='view' in insert, update and delete. I would do myself, but I just downloaded Git yesterday and am not yet familiar with version control." http://www.jesshamrick.com/2013/04/13/on-collecting-data/ I also noticed that Jessica Hamrick's dbtools was scraping the SQL (string) column in the SQLite master table with regular expressions to obtain the column names, column types and the primary key (to use in creating pandas object). I pointed out that the Python APSW library has wrapped the SQLite3 C API functions (sqlite3_column_name & sqlite3_column_decltype) for the column name and column type. But, I don't see how to get the primary key in APSW. Is the sqlite3_table_column_metadata() SQLite C API function http://www.sqlite.org/capi3ref.html#sqlite3_table_column_metadata also wrapped by the APSW Python Library? I posted: Also, you don't have to scrape the colnames and types with regular expressions; there is an api for that. In the APSW SQLite Python library, there is a cursor.getdescription() method that: "returns information about each column is a tuple of (column_name, declared_column_type). The type is what was declared in the CREATE TABLE statement - the value returned in the row will be whatever type you put in for that row and column. (This is known as manifest typing which is also the way that Python works. The variable a could contain an integer, and then you could put a string in it. Other static languages such as C or other SQL databases only let you put one type in - eg a could only contain an integer or a string, but never both.)" The APW calls the SQLite C API functions: sqlite3_column_name sqlite3_column_decltype So, [with APSW] you [would be] are relying on SQLite3's native parsing and not someone else's random regex or homebrew parser. http://rogerbinns.github.io/apsw/cursor.html#cursor-class BTW, open source, Notepad++, has nice syntax highlighting for Python. I have an afternoon and evening full of meetings, but I will attempt this fix myself tomorrow (Tuesday) morning. Cheers. Jim Callahan Orlando, FL On Sun, Aug 3, 2014 at 11:31 AM, Roger Binns <rog...@rogerbinns.com> wrote: > Disclosure: I am the apsw author > > > On 08/02/2014 10:19 AM, Jim Callahan wrote: > >> I got apsw to work, but it had a curious side-effect >> -- it clobbered my IPython prompt (replaced prompt with smiley faces). >> > > APSW certainly didn't do that. It doesn't do anything - you have to make > calls and get responses. > > If you use the APSW shell then it will use ANSI escape sequences to colour > the output. However this is only done if the output is a terminal, and can > be turned off. (That is the case for Linux & Mac. For Windows you also > need to install colorama.) > > > For those who are interested. >> 1. downloaded apsw -- does not work with Python's package manager pip >> http://apidoc.apsw.googlecode.com/hg/download.html#source-and-binaries >> > > APSW moved from googlecode a while back. It is at: > > https://github.com/rogerbinns/apsw > > This explains why: > > http://www.rogerbinns.com/blog/moving-to-github.html > > APSW is actually now on pypi. Someone else put it up there and it has no > connection to me. It is also extremely unlikely to install because it > doesn't handle the SQLite dependency, nor have Windows binaries. > > > 3. commented out "import apswrow" from suggested script (not found, not >> needed) >> > > That has no connection to APSW either. It is written by someone else to > turn rows returned from a tuple into also having the column names. > > Roger > > _______________________________________________ > 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
