headers
Gerhard Häring | 1 Apr 2010 08:31
Picon
Gravatar

Re: use sqlite3.Row when subclassing connection

Hello,

On Wed, Mar 31, 2010 at 4:29 PM, hjebbers <hjebbers@...> wrote:
> hi,
>
> I am trying to use the conn.row_factory = sqlite3.Row functionality,
> but have not succeeded so far.
> reason might be that subclassed the connection class (in order to use
> a subclassed cursor)
>
> def connect2sqldb(database):
> [...]

First, please send an exact minimalist example if you send code again.
The code in your mail couldn't possibly work at all. "conn terug"
"sqlite" vs "sqlite3".

The problem is that there's an undocumented part with row_factory.
What's really used is Cursor.row_factory, which is undocumented.
Cursor.row_factory is copied from Connection.cursor() into the cursor
instance. Of course this can only happen if you do not completely
override Connection.cursor. Here's example code that works as
intentended:

import sqlite3

def connect2sqldb(database):
    con = sqlite3.connect(database, factory=MyConnection)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA synchronous=OFF")
(Continue reading)

Permalink | Reply | 1 comment |
headers
steve | 1 Apr 2010 10:10
Picon

Can APSW import CSV data?

I would like to be able to (on the fly) import from a CSV file and
then perform queries. Is this possible with APSW?

Here is an example of the kind of thing I'd like to do:
cnx = apsw.Connection("")
cursor = cnx.cursor()
cursor.execute("CREATE TABLE t (...)")
cnx.somehow(".import mydata.csv t")
cursor.execute("SELECT blah FROM t ...")

I can do the import using popen2 of course, but I would rather have
proper SQL access after doing the import.

Or I could read the files and use APSW to do lots of INSERTs before
the query, but that doesn't feel very efficient.

I am happy for the sqlite database to disappear when I close the
connection - the permanent record is the CSV files.

--

-- 
To unsubscribe, reply using "remove me" as the subject.
Permalink | Reply | 3 comments |
headers
Roger Binns | 1 Apr 2010 10:22

Re: Can APSW import CSV data?


steve wrote:
> I would like to be able to (on the fly) import from a CSV file and
> then perform queries. Is this possible with APSW?

Yes.

One approach is to use virtual tables with a CSV backend.  There were at
least 4 different C based implementations the last time I checked and I
wouldn't touch any of them as they had all sorts of issues with encoding,
dialects, usability, bug handling etc.  You can write your own:

  http://apsw.googlecode.com/svn/publish/vtable.html

> Here is an example of the kind of thing I'd like to do:

Do you know that APSW has a shell?

  http://apsw.googlecode.com/svn/publish/shell.html

> cnx = apsw.Connection("")
> cursor = cnx.cursor()
> cursor.execute("CREATE TABLE t (...)")
> cnx.somehow(".import mydata.csv t")
> cursor.execute("SELECT blah FROM t ...")

Untested:

shell=apsw.Shell("testdb") # or :memory:
shell.ensuredb()
(Continue reading)

Permalink | Reply | 2 comments |
headers
steve | 1 Apr 2010 11:53
Picon

Re: Can APSW import CSV data?

Woo-Hoo! Thank you!

That Shell is just what I needed. It's not there in Ubuntu 9.10, I get
an error that "module has no such attribute Shell" when I try to
import it (I guess the version is too old), but it is there in the up-
coming Ubuntu 10.04. I guess I'll have to upgrade in the next few
weeks (it's overdue for an upgrade anyway).

Thanks for the quick answer. I really appreciate your help.
And thanks for apsw of course.

Steve

--

-- 
To unsubscribe, reply using "remove me" as the subject.
Permalink | Reply | 1 comment |
headers
Roger Binns | 1 Apr 2010 12:12

Re: Re: Can APSW import CSV data?


steve wrote:
> That Shell is just what I needed. It's not there in Ubuntu 9.10, I get
> an error that "module has no such attribute Shell" when I try to
> import it (I guess the version is too old), but it is there in the up-
> coming Ubuntu 10.04. I guess I'll have to upgrade in the next few
> weeks (it's overdue for an upgrade anyway).

Unfortunately the distros tend to trail APSW and SQLite by a year or more.

As an alternative you can use --user to the install which puts the
extension below your home directory, or build_ext --inplace to produce
the extension and copy it wherever you want.  More details at

  http://apsw.googlecode.com/svn/publish/build.html

Roger
Permalink | Reply | 0 comments |
headers
hjebbers-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org | 1 Apr 2010 12:09
Picon

Re: use sqlite3.Row when subclassing connection

Hi Gerhard,

thanks for your answer. I really appreciate this.

you are right about the code, I cut out a lot, but could have done that 
better.

it's really great that python has a sql database by default.

kind regards,
henk-jan

Gerhard Häring wrote:
> Hello,
>
> On Wed, Mar 31, 2010 at 4:29 PM, hjebbers <hjebbers@...> wrote:
>   
>> hi,
>>
>> I am trying to use the conn.row_factory = sqlite3.Row functionality,
>> but have not succeeded so far.
>> reason might be that subclassed the connection class (in order to use
>> a subclassed cursor)
>>
>> def connect2sqldb(database):
>> [...]
>>     
>
> First, please send an exact minimalist example if you send code again.
> The code in your mail couldn't possibly work at all. "conn terug"
(Continue reading)

Permalink | Reply | 0 comments |
headers
il.NoNNo | 4 Apr 2010 22:39
Picon

problem compining on AIX 6

Dear all,
I'm trying to compile the pysqlite module on a AIX 6 machine. These
errors appears

-bash-3.2$ python setup.py build
running build
running build_py
running build_ext
building 'pysqlite2._sqlite' extension
xlc_r -DNDEBUG -g -O2 -q64 -qarch=pwr6 -qtune=pwr6 -qsuppress=1501-245
-DMODULE_NAME="pysqlite2.dbapi2" -DSQLITE_OMIT_LOAD_EXTENSION=1 -I/
cineca/prod/tools/python/2.6.2/xl--10.1/include/python2.6 -c src/
module.c -o build/temp.aix-6.1-2.6/src/module.o
"src/connection.h", line 33.10: 1506-296 (S) #include file "sqlite3.h"
not found.
"src/connection.h", line 38.5: 1506-046 (S) Syntax error.
"src/statement.h", line 29.10: 1506-296 (S) #include file "sqlite3.h"
not found.
"src/statement.h", line 37.5: 1506-046 (S) Syntax error.
"src/module.c", line 265.19: 1506-045 (S) Undeclared identifier
SQLITE_OK.
"src/module.c", line 266.21: 1506-045 (S) Undeclared identifier
SQLITE_DENY.
"src/module.c", line 267.23: 1506-045 (S) Undeclared identifier
SQLITE_IGNORE.
"src/module.c", line 268.29: 1506-045 (S) Undeclared identifier
SQLITE_CREATE_INDEX.
"src/module.c", line 269.29: 1506-045 (S) Undeclared identifier
SQLITE_CREATE_TABLE.
"src/module.c", line 270.34: 1506-045 (S) Undeclared identifier
(Continue reading)

Permalink | Reply | 1 comment |
headers
Roger Binns | 5 Apr 2010 05:41

Re: problem compining on AIX 6

On 04/04/2010 01:39 PM, il.NoNNo wrote:
> "src/connection.h", line 33.10: 1506-296 (S) #include file "sqlite3.h"
> not found.
...
> On that machine both python 2.6 and sqlite3 are correctly installed

pysqlite cannot find sqlite3.h at compile time.

Roger

--

-- 
To unsubscribe, reply using "remove me" as the subject.
Permalink | Reply | 0 comments |
headers
Roger Binns | 5 Apr 2010 09:16

APSW 3.6.23.1-r1 released

APSW 3.6.23.1-r1 is now available. The home page is at
http://code.google.com/p/apsw/ which includes full documentation, source
and binary distributions for Windows (Python 2.3 onwards including
Python 3).

APSW is a wrapper around SQLite that provides all SQLite API
functionality in Python.  It is not DBAPI compliant as it provides
SQLite semantics.  pysqlite provides DBAPI semantics.  You can see the
two approaches contrasted at
http://apsw.googlecode.com/svn/publish/pysqlite.html

Changelist is below and a clickable version at
http://apsw.googlecode.com/svn/publish/changes.html

Shell CSV output under Python 3.1 is corrected (work around Python 3.1 
StringIO bug/incompatibility with other Python versions).

Simplified access to the shell’s database from the API.

Added a shell example.

Roger

--

-- 
To unsubscribe, reply using "remove me" as the subject.
Permalink | Reply | 0 comments |
headers
Chris Phillips | 8 Apr 2010 10:21

High number of "database is locked" errors

Hi,

I'm sure I'm missing something pretty obvious, but I can't for the
life of me stop my pysqlite scripts crashing out with a database is
locked error. I have two scripts, one to load data into the database,
and one to read data out, but both will continually crash depending on
what the other is doing with the database at any given time. I've got
the timeout on both scripts set to 30 seconds:

 cx = sqlite.connect("database.sql", timeout=30.0)

and think I can see some evidence of the timeouts in that i get what
appears to be a timing stamp (e.g 0.12343827e10 1) dumped occasionally
in the middle of my curses formatted output screen. but still one of
the other keeps crashing again and again from this. I'm running
RHEL5.4 on a 64 bit HS21 IBM blade, and have heard some mention about
issues about multi-threading and am not sure if this might be
relevant. Packages in use are sqlite-3.3.6-5 and python-
sqlite-1.1.7-1.2.1, and upgrading to newer versions outside of
RedHat's official provisions is not a great option for me. Possible,
but not desirable due to the environment in general.

I have had autocommit=1 on previously on both scripts, but have since
disabled on both, and am now cx.commit()ing on the inserting script
and not committing on the select script. Ultimately as I only ever
have one script actually making any modifications, I don't really see
why this locking should ever ever happen...

Any pointers very much appreciated.
(Continue reading)

Permalink | Reply | 1 comment |

Gmane