SQLite

SQL on flat files

https://news.ycombinator.com/item?id=7175830

$sqlite3 logs.db

sqlite3> CREATE TABLE logs (id integer primary key autoincrement, logtype text, logname text, logmessage text, logtime datetime)

The id is unique, and increments automatically. i.e. You don't have to keep track of it.

After this, quit out of the logger;

sqlite3> .exit

insert into logs (id,logtype,logname,logmessage,logtime) values (NULL, 'trace', 'UIEvent', 'mousepress', datetime('now'));

Points to note:

1. Putting in NULL in the id field gets the database to autoincrement it.

2. datetime('now') is a built-in SQLite function that yields the current time stamp.

Show all logs in the last day (86400 seconds) =>

select * from logs where strftime('%s', 'now')-strftime('%s', logtime) < 86400;

Show all logs in the last hour (3600 seconds) =>

select * from logs where strftime('%s', 'now')-strftime(''%s', logtime) < 3600;

Show all critical or major logs in the last 15 minutes (900 seconds) =>

select * from logs where strftime('%s', 'now') - strftime('%s', logtime) < 900 and logtype in ('critical','major');

import sqlite3 from flask import Flask dbpath = '/path/to/my/db' db = sqlite3.connect(dbpath) app = Flask(__name__) @app.route('/<params>') def query(params): cursor = db.cursor() return cursor.execute( '''SELECT * FROM mytable WHERE params=?''', (params,)) if __name__ == '__main__': app.run()

GUI Tools

http://sqlitebrowser.org/

https://stackoverflow.com/questions/835069/which-sqlite-administration-console-do-you-recommend

http://twit88.com/blog/2011/11/28/open-source-or-free-sqlite-administration-tools/

TERMSQL

http://tobimensch.github.io/termsql/

https://github.com/tobimensch/termsql

which sqlite3

/usr/bin/sqlite3

Using sqlite3 in a shell script

https://www.sqlite.org/cli.html

One way to use sqlite3 in a shell script is to use "echo" or "cat" to generate a sequence of commands in a file, then invoke sqlite3 while redirecting input from the generated command file. This works fine and is appropriate in many circumstances. But as an added convenience, sqlite3 allows a single SQL command to be entered on the command line as a second argument after the database name. When the sqlite3 program is launched with two arguments, the second argument is passed to the SQLite library for processing, the query results are printed on standard output in list mode, and the program exits. This mechanism is designed to make sqlite3 easy to use in conjunction with programs like "awk". For example:

$ sqlite3 ex1 'select * from tbl1' | > awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }' <tr><td>hello<td>10 <tr><td>goodbye<td>20 $

CVSKIT

pip install csvkit

To solve your problem

csvsql --db sqlite:///path/to/file.db --insert --table mytable filecsv.txt

BULK IMPORT OF CSV FILES

http://stackoverflow.com/questions/18219779/bulk-insert-huge-data-into-sqlite-using-python

http://stackoverflow.com/questions/14947916/import-csv-to-sqlite

http://stackoverflow.com/questions/5942402/python-csv-to-sqlite/7137270#7137270

http://blog.quibb.org/2010/08/fast-bulk-inserts-into-sqlite/

make sure to do all insert in a single transaction by surrounding the inserts with BEGIN and COMMIT. (executemany() does this automatically.)

Use the ".import" command to import CSV (comma separated value) data into an SQLite table. The ".import" command takes two arguments which are the name of the disk file from which CSV data is to be read and the name of the SQLite table into which the CSV data is to be inserted.

Note that it is important to set the "mode" to "csv" before running the ".import" command. This is necessary to prevent the command-line shell from trying to interpret the input file text as some other format.

sqlite> .mode csv sqlite> .import C:/work/somedata.csv tab1

There are two cases to consider: (1) Table "tab1" does not previously exist and (2) table "tab1" does already exist.

In the first case, when the table does not previously exist, the table is automatically created and the content of the first row of the input CSV file is used to determine the name of all the columns in the table. In other words, if the table does not previously exist, the first row of the CSV file is interpreted to be column names and the actual data starts on the second row of the CSV file.

For the second case, when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content. If the CSV file contains an initial row of column labels, that row will be read as data and inserted into the table. To avoid this, make sure that table does not previously exist.

sqlite3

sqlite> .separator "," sqlite> .import filecsv.txt mytable

https://pysqlite.readthedocs.org/en/latest/sqlite3.html pysqlite

import csv, sqlite3, time def chunks(data, rows=10000): """ Divides the data into 10000 rows each """ for i in xrange(0, len(data), rows): yield data[i:i+rows]if __name__ == "__main__": t = time.time() conn = sqlite3.connect( "path/to/file.db" ) conn.text_factory = str #bugger 8-bit bytestrings cur = conn.cur() cur.execute('CREATE TABLE IF NOT EXISTS mytable (field2 VARCHAR, field4 VARCHAR)') csvData = csv.reader(open(filecsv.txt, "rb")) divData = chunks(csvData) # divide into 10000 rows each for chunk in divData: cur.execute('BEGIN TRANSACTION') for field1, field2, field3, field4, field5 in chunk: cur.execute('INSERT OR IGNORE INTO mytable (field2, field4) VALUES (?,?)', (field2, field4)) cur.execute('COMMIT') print "\n Time Taken: %.3f sec" % (time.time()-t)

Python

http://sebastianraschka.com/Articles/2014_sqlite_in_python_tutorial.html

http://talkera.org/python/python-database-programming-sqlite-tutorial/

http://charlesleifer.com/blog/extending-sqlite-with-python/

http://charlesleifer.com/blog/sqlite-small-fast-reliable-choose-any-three-/

sql = """SELECT action.descr as action FROM public.role_action_def, public.role WHERE role.id = role_action_def.role_id AND role_action_def.account_id = ? AND record_def.account_id= ? AND def_id= ?""" parameters = [account_id, account_id, def_id] cursor.execute(sql, parameters)

http://sqlite.org/index.html

http://code.activestate.com/recipes/526618/

http://yserial.sourceforge.net/

http://www.doughellmann.com/PyMOTW/sqlite3/

import csv

import sqlite3

def init_db(cur):

cur.execute('''CREATE TABLE foo (

Row INTEGER,

Name TEXT,

Year INTEGER,

Priority INTEGER)''')