Re: Switching python app from sqlite to postgres - Mailing list pgsql-novice

Josh Kupershmidt wrote:
> On Thu, Apr 12, 2012 at 9:51 AM, Bob McConnell <rmcconne@lightlink.com> wrote:
>> I have been looking at an application I downloaded from Sourceforge for
>> model railroading[1]. So far it has two strikes against it. It uses Python
>> 3, and it uses sqlite-3. Other than that, it looks like it might be a useful
>> program.
>>
>> I tried to build and run the software, but Slackware uses and bundles Python
>> 2 and although I also have 3.1 installed, the packaged installation of the
>> sqlite-3 drivers only runs with version 2. Py 3 still says they're not
>> installed.
>>
>> So the next step is likely to switch the code to use Postgres. The database
>> interface is limited to three Python source files, so I don't think it will
>> be too difficult, if I can find some guidelines on how to do it. Does anyone
>> know where I might find some information on how to make this conversion?
>
> Well, I imagine the path of least resistance to get the application
> working would be to install the sqlite library it wants (BTW,
> shouldn't the "sqlite3" module be built-in for all Python versions
> 2.5+ ?). But as to your question, converting an application from
> SQLite to Postgres shouldn't be all that hard. SQLite is quite limited
> in what it can do, which means less esoteric features or "SQL
> extensions" to worry about porting correctly. For instance, you don't
> have to worry about converting any stored procedures, since SQLite
> doesn't have them.
>
> You might have a few datatype conversions to look at, such as
> "datetime" -> "timestamp", "blob" -> "bytea", and some similar issues.
> My strategy is usually to build a test suite, if it doesn't have one
> already, make sure the suite passes with the SQLite backend,
> substitute in the SQLite connections for psycopg2, and see what breaks
> :-)

sqlite is not included in either version of Python I have installed
(2.5.2 and 3.1.1). I tried installing it after doing the parallel
installation of 3.1.1, but it bound to the older version of Python. I
don't know anything about either Python or sqlite, so I have no idea how
to get them to work together. I won't simply replace 2.5 with 3.1, since
Slackware uses Python scripts in a number of packages, and the language
apparently changed significantly between 2 and 3, so all of those
scripts have to be replaced simultaneously with the update. This
application won't work with 2.5, probably for the same reason.

So far, the only functions I have seen are connect, create a cursor
object, use that cursor to run several sql commands, and close. I
haven't looked too closely at the sql itself, but other than creating
some tables with primary keys, there doesn't seem to be much else beyond
adding and retrieving sets of records.

Since I already have Postgres servers running, it looks like my best
option is to install the Psycopg driver, modify the three files to use
it and see what happens. Of course, that assumes I can get that driver
to link to the correct version of Python as well. But, from your
comments it sounds a lot easier than most of the conversions I have done
while upgrading databases from MySQL.

(I just checked one of my CentOS 5 servers - based on RedHat EL 5.8. It
was updated earlier this morning, and is still running Python 2.4.3. It
looks like more than one distribution has postponed updating to 3.)

Thank you,

Bob McConnell
N2SPP