Re: Switching python app from sqlite to postgres

On Thu, Apr 12, 2012 at 9:51 AM, Bob McConnell <rmcconne(at)lightlink(dot)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
:-)

Josh