[Ironpython-users] IronPython SQLite interface

[Jeff Hardy]

On Fri, May 9, 2014 at 12:01 PM, Doug Blank <doug.blank at gmail.com> wrote:
> On Fri, May 9, 2014 at 4:42 AM, Jeff Hardy <jdhardy at gmail.com> wrote:
>>
>> On Thu, May 8, 2014 at 5:32 PM, Doug Blank <doug.blank at gmail.com> wrote:
>> > Just getting back to attempting to use sqlite3 on computers other than
>> > Windows, and it looks like this C# library has other problems on other
>> > OSes.
>> >
>> > The first issue is that it appears that all activity must happen in the
>> > same
>> > thread (connection, cursor, execution). That can mitigated against if
>> > that
>> > is the way that it has to be. (A wrapper could be supplied to run
>> > everything
>> > in a specific thread).
>> >
>> > The next problem is a show stopper:
>> >
>> > import sqlite3
>> > conn = sqlite3.connect("test.db")
>> > cur = conn.cursor()
>> > cur.execute("select * from tables;")
>> >
>> > This works on Windows, but gives a SystemError: LockFileEx on Linux. I
>> > suspect that the information that Ivan pointed to (below) indicates that
>> > these patches screwed up sqlite port so that it no longer will work on
>> > Linux
>> > and Mac.
>> >
>> > Anyone have ideas, on either a fix, or using a different library,
>> > different
>> > version?
>>
>> Right now, I don't know if there's much that can be done. The C#
>> SQLite port it uses is pretty Windows-specific, and I doubt it's worth
>> the effort to fix it since it's no longer maintained anyway. I've been
>> pondering replacing the guts of the SQLite module with
>> https://github.com/ericsink/SQLitePCL.raw, which wraps the *actual*
>> SQLite library instead of the C# port.
>>
>> The module implementation shouldn't have to change that much, since
>> most of the function names should be the same. I don't yet know if
>> SQLitePCL works on Unix/Mac but it does support Android and iOS so
>> it's probably going to be a lot easier to fix it than it would be for
>> C#-SQLite.
>>
>> I hadn't planned on this until IronPython 3, but I'd be very happy to
>> include it in 2.7.5 (or a possible 2.7.6) if someone else does the
>> work.
>
>
> Thanks for the information!
>
> As I am also interested in other .NET/Mono languages (and other DB
> connections), I wonder if we are going to spend any time on this, if it
> wouldn't be better to use (and perhaps refine, if needed) the ADO.NET Sqlite
> interface:
>
> http://www.mono-project.com/SQLite
>
> Perhaps it is better to move away from the sqlite-specific API and towards a
> standard that would allow people to move between db backends? I suspect Vern
> may have some information on this point, considering his recent announcement
> here on adodbapi version 2.6.

I actually tried to use that, originally, but Python's sqlite module
exposes some low-level features of SQLite that can't be emulated with
an ADO-type interface. The only way to implementing it faithfully is
to have direct(ish) access to the SQLite library.

Most real apps will use a higher-level wrapper, but IronPython still
needs to provide the sqlite module, since most of the high-level
wrappers will expect it to be there.

- Jeff