Importing CSV file into SQLite3 from PHP

Question

To create an SQLite3 database and insert values from an external CSV file from PHP I ran :

$db=new SQLite3("QNH.sqlite");
$q="CREATE TABLE IF NOT EXISTS QNH(id INTEGER PRIMARY KEY,unixtimeutc INTEGER,stationqnh TEXT)";
$db->exec($q);unset($regel);$id=0;
if(($fp=fopen("QNH.csv","r"))!==false)
  {while(($regel=fgetcsv($fp,32,","))!==false)
        {$id++;
         $q="INSERT INTO QNH VALUES('".$id."','".$regel[0]."','".$regel[1]."')";
         $db->exec($q);}}
$v=fclose($fp);$db->close();

It works... but ridiculously and unuseably slow and it hits the 60 seconds timeout well before finishing. Only 733 lines were added of the 105132 in total to be added. At this pace it would need > 143 minutes. And the HD makes a lot of fuss doing this. This is obviously NOT the way to do it. I know how to create the DB and import the CSV file at the SQLite3 command prompt, this works in a matter of seconds, but I need to do this from a PHP script. I looked everywhere but couldn't find anything specific to SQLite3 and PHP. Any ideas?

" I know how to create the DB and import the CSV file at the SQLite3 command prompt, this works in a matter of seconds" you can call the command prompt from php — , Mar 14 '16 at 22:52
You could also do multiple inserts like so : http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database (if that is applicable to your use case) — Mathieu de Lorimier, Mar 14 '16 at 23:04

Parfait · Accepted Answer · 2016-03-15T12:49:14.740

1

Like most general programming languages, PHP can execute external programs with command line arguments using exec() or shell_exec(). This is similar to Python's subprocess.call(), R's os.system(), Java's ProcessBuilder(), Perl's system(), C#'s and VB.NET's Process.Start().

Consider running your sqlite csv import in an .sql file and call it in php:

SQL Script (save as import.sql)

CREATE TABLE IF NOT EXISTS QNH(id INTEGER PRIMARY KEY,unixtimeutc INTEGER,stationqnh TEXT);
.separator ,
.mode csv
.import output.csv QNH

PHP Script (adjust path names as needed)

exec("sqlite3 yourdb.sqlite < import.sql")

edited Mar 15 '16 at 12:49

answered Mar 15 '16 at 01:48

Parfait

104,375
17
94
125

The proposed solution throws an error. It works when the CREATE line ends in a semi-colon. The ".mode csv" is not required. – Petoetje59 Mar 15 '16 at 11:08
Fixed semicolon. Mode might be needed for future readers with [no EOL marker in csv](http://stackoverflow.com/questions/14947916/import-csv-to-sqlite). Curious, did append run quicker? – Parfait Mar 15 '16 at 12:57
@Parfait, can you please explain PHP Script, where you are passing Table Name? I can see you have given only database name `yourdb.sqlite`. – Himanshu Sep 17 '16 at 05:58
@Himanshu - PHP does not run any SQL but calls a separate program, sqlite3.exe, the command line executable, to run an external .sql script on specific database. – Parfait Sep 17 '16 at 14:29

Petoetje59 · Answer 2 · 2016-03-16T16:27:11.360

0

I implemented the shell idea because it's simple to code and fast in execution time :

$sql="shell_commands.qsl";$fp=fopen($sql,"w");
$v="create table QNH(unixtimeutc integer,stationqnh text);\n";
$v.=".separator \",\"\n.import ".$csv." QNH\n.exit";$v=fwrite($fp,$v);
$v=fclose($fp);$v=shell_exec("sqlite3.exe ".$db."<".$sql);

As expected, this runs as fast as the manual import. What I also noticed is the inconsistency of SQLite3 : keywords like "create" can be in uppercase, but "separator" etc. cannot...

edited Mar 16 '16 at 16:27

answered Mar 16 '16 at 09:24

Petoetje59

113
1
5

Generally the SQL keywords, such as CREATE and TABLE are case insensitive. Anything happening at the shell level is not. – Manngo Aug 15 '16 at 03:13

score 0 · Answer 3 · answered Aug 15 '16 at 03:17

I know it’s too late, but have you tried using a prepared statement? Here is a sample:

$elements=file('elements.txt');
$db=new PDO('sqlite:elements.sqlite');
$sql='CREATE TABLE IF NOT EXISTS elements(number int primary key,element varchar, symbol varchar, grp varchar)';
$db->exec($sql);
$sql='INSERT INTO elements(element,number,symbol,grp) VALUES(?,?,?,?)';
$prepared=$db->prepare($sql);
$headers=array_shift($elements);
foreach($elements as $element) {
    $element=explode("\t",trim($element));
    $prepared->execute($element);
}

Prepared statements help to prevent SQL injection, but they are also useful in speeding up execution.

Preparing allows the database to interpret and plan the query before running it. This is the sort of thing that takes time, so you really want to do this once only. Subsequent multiple executes can take advantage of this.

I'll stick to the shell idea because it's simple and works fast as intended, but will try your method in case this might come in handy for some other project. — Petoetje59, Aug 16 '16 at 08:34

score 0 · Answer 4 · answered Oct 03 '19 at 16:06

You need to use transactions. See https://www.php.net/manual/en/pdo.begintransaction.php

By default SQLite is writing each query to disk and syncing the disk. You say that you can hear the disk, which means this is not SSD. Therefore you are limited by the rotational speed of the disk. At 5400 RPM, with several accesses required (create backup, sync filesystem directory, commit inserted row, delete backup, sync filesystem directory) you are getting the expected performance.

Source: https://www.sqlite.org/lang_transaction.html

For your full solution consider PHP array_chunk with 1000 rows at a time. You will see at least 100x performance increase.

Importing CSV file into SQLite3 from PHP

4 Answers4