There was no need to modify
Posted: Sun Dec 22, 2024 8:24 am
There was no need to modify the Python code, as the differences between these databases were handled internally by SQLAlchemy. SQLAlchemy even created all the tables and indexes on my new PostgreSQL database, just like it did in SQLite. To make the application work fully with PostgreSQL, I also had to migrate the data currently stored in the SQLite database. I used pgloader , a fairly comprehensive open source script that imports data from multiple sources into a PostgreSQL database. Fortunately, SQLite is one of the supported data sources. The easiest way to import data with pgloaderis to create a configuration file that defines the source, destination, and options.
Here's the one I created: Text Copy the code LOAD DATABASE FROM ./db.sqlite INTO whatsapp philippines number postgresql://username:password@localhost/dbname WITH data only ; The only option I used was data only, which tells pgloadernot to create any tables or indexes, just to move the data from the source database to the destination database. This is important because I wanted the database schema to be as close to SQLite One's as possible, and the best way to do that was to let SQLAlchemy create all the tables and indexes. To perform the data migration, pgloaderis called with the name of the configuration file as the only argument: Bash Copy the code pgloader sqlite-to-pg.
conf After a few tries, the import process worked without any problems. The first few failed attempts alerted me to a few potentially serious problems I had encountered while designing my database, which SQLite decided not to report or take into account: For the article slugs, I defined a column VARCHARof 256 characters. The data migration failed for four articles whose slug length exceeded the maximum length. SQLite, with its unusual typing system , did not care and stored the longer slugs without complaint. In contrast, PostgreSQL strictly enforced its column types, so these four posts could not be migrated.
Here's the one I created: Text Copy the code LOAD DATABASE FROM ./db.sqlite INTO whatsapp philippines number postgresql://username:password@localhost/dbname WITH data only ; The only option I used was data only, which tells pgloadernot to create any tables or indexes, just to move the data from the source database to the destination database. This is important because I wanted the database schema to be as close to SQLite One's as possible, and the best way to do that was to let SQLAlchemy create all the tables and indexes. To perform the data migration, pgloaderis called with the name of the configuration file as the only argument: Bash Copy the code pgloader sqlite-to-pg.
conf After a few tries, the import process worked without any problems. The first few failed attempts alerted me to a few potentially serious problems I had encountered while designing my database, which SQLite decided not to report or take into account: For the article slugs, I defined a column VARCHARof 256 characters. The data migration failed for four articles whose slug length exceeded the maximum length. SQLite, with its unusual typing system , did not care and stored the longer slugs without complaint. In contrast, PostgreSQL strictly enforced its column types, so these four posts could not be migrated.