Lastly, I count the number of columns and create the number of string substitutions I’ll need in my SQL statement in a place holder variable (minus the last character, to remove a comma from the end of the statement). Then I fetch all the rows for that table and save them in a tuple called rows. Then I loop through that list of tables and get the CREATE TABLE statement that’s stored in the master table and save that in string variable called create.
COPY DB SQLITE SERIES
This returns a series of tuples where the name of the table is in the first position I grab these and save them in a list. I connect to the SQLite database and read all the names of the tables that match my LIKE statement from the master table. The remaining variables are for the Postgres database: pgdb (database name), pguser, pgpswd (my username and password), pghost, pgport (address of the database server on port 5432), and pgschema which is the name of the schema I want to write to. For example, if my series of tables starts with yr followed by a year (yr2017) my string would be ‘yr%’. The SQLite variables are the name and path to the database (sqdb) and a string that I’ll use in a LIKE clause to grab certain tables (sqlike).
COPY DB SQLITE CODE
The code is below.įirst I define a number of variables that I hard code for each batch of tables. The original CREATE TABLE statements are stored in a master table if I grab those statements and then the data that goes with them, I can simply recreate everything. I could connect to the SQLite database and load the tables into Python’s data structures, and then simply connect to my PostgreSQL database and write them out. That gave me a better idea – Python has SQLite and PostgreSQL modules ( sqlite3 and psycopg2 respectfully). The solutions I found involved lousy things like opening the dump file in an editor (not feasible if the file is huge) and finding and replacing parentheses and commas, or running the file through a script to remove them. I encountered a number of problems in doing this there are slight differences in how each database creates and handles dump files.
My initial idea was to simply create a SQL dump file out of SQLite and then restore it in Postgres. We have a couple of different SQLite / Spatialite projects that we produce and I needed to move a large number of attribute tables from them into the Postgres database. Once you are comfortable dumping and restoring databases, make a cron job to do a nightly backup of a SQLite database on your system.We recently created a PostgreSQL / PostGIS database on a server on our local campus network and spent the last few months loading data into it. That way if you make a mistake it's easily corrected.) (Warning: make sure to rename the original database and not delete it. After dumping the database to a backup, try to restore it. To gain confidence with SQLite backups and restores, try backing up the database from phpBB or Serendipity. Alternatively you may want to delete or rename the destination database and let sqlite create a new one for you. Restoring the database from a backup is just as easy as backing up, except we must make sure the destination database is empty first. Now use the file you just created to populate your sample database. Insert into music values('Parliment','Mothership Connection') Insert into music values('John, Elton','Goodbye Yellow Brick Road') Insert into music values('Beastie Boys','Paul''s Boutique') Use your favorite text editor to create the following file and save it as music.sql:Ĭreate table music(artist varchar(40), title varchar(60)) The following example shows SQLite database creation that is consistent with the examples used in the ELS Howto: phpBB Web Forum and ELS Howto: Serendipity Blog documents. You will need to create a directory for databases and a sample database.
COPY DB SQLITE INSTALL
However, you may wish to install a phpBB web forum or a Serendipity blog to have some live data to work with. SQLite is included in the base packages and no installation is required.
COPY DB SQLITE HOW TO
This document will demonstrate how to backup and restore those databases. Many of the PHP-based applications that can run on an Elemental Linux Server (ELS) feature a SQLite database backend. ELS Howto: SQLite Backup and Restore ELS Howto: SQLite Backup and Restore
0 kommentar(er)
