sqlite3 command line tools don't work in Ubuntu

Question

Trying to set up some basic data I/O scripts in python that read and write from a local sqlite db. I'd like to use the command line to verify that my scripts work as expected, but they don't pick up on any of the databases or tables I'm creating.

My first script writes some data from a dict into the table, and the second script reads it and prints it.

Write:

# first part of script creates a dict called 'totals'

import sqlite3 as lite

con = lite.connect('test.db')

with con:
    cur = con.cursor()

    cur.execute("DROP TABLE IF EXISTS testtbl")    

    cur.execute("CREATE TABLE testtbl(Date TEXT PRIMARY KEY, Count INT, AverageServerTime REAL, TotalServerTime REAL, AverageClientTime REAL, TotalClientTime REAL)")
    cur.execute('INSERT INTO testtbl VALUES("2012-09-08", %s, %s, %s, %s, %s)' % (float(totals['count()']), float(totals['serverTime/count()']), float(totals['serverTime']), float(totals['totalLoadTime/count()']), float(totals['totalLoadTime'])))

Read:

import sqlite3 as lite

con = lite.connect('test.db')

with con:    

    cur = con.cursor()    
    cur.execute("SELECT * FROM testtbl")

    rows = cur.fetchall()

    for row in rows:
        print row

These scripts are separate and both work fine. However, if I navigate to the directory in the command line and activate sqlite3, nothing further works. I've tried '.databases', '.tables', '.schema' commands and can't get it to respond to this particular db. I can create dbs within the command line and view them, but not the ones created by my script. How do I link these up?

Running stock Ubuntu 12.04, Python 2.7.3, SQLite 3.7.9. I also installed libsqlite3-dev but that hasn't helped.

Are you putting the DB file name in the command ? sqlite3 test.db — tMC
you need to do con.commit() after changing anything in python — Joran Beasley
@JoranBeasley using the 'with' construction you don't need to, as is my understanding. FWIW, that didn't work when I tried it. — acpigeon

tMC tMC · Accepted Answer · 2012-09-10T22:37:04

2

votes

Are you putting the DB file name in the command ?

$ sqlite3 test.db

sqlite3 command line tools don't work in Ubuntu

1 Answers