I'm trying to grasp SQLite to do some stuffs I want it to do, but it is sometimes too hash to me :)

So what i'm I wrong with this code??

#!/usr/bin/
# SQLite command to be used with the applications
import sqlite3 as sqlite
import os

class SqliteCommands:
    def ___init__(self, parent):
        self.parent = parent
        
    # Create database
    def onCreateDb(self):
        conn = sqlite.connect("./databases/test.db")
        cur = conn.cursor()
        return (conn, cur)
    
    #Create New Table
    def onCreateTable(self, cur, t_name):        
        cur.execute("""CREATE TABLE IF NOT EXISTS %s(Date TEXT, Explanations TEXT, Deposit REAL, Withdraw REAL, Balance REAL)""" %(t_name,))
        
    #Create Rows for the New table    
    def onInsertRows(self, cur, t_name, tuple_rows):
        #add name to a tuple (unpack and pack the tuple)
        names = list(tuple_rows)
        names.insert(0, t_name)
        rows = tuple(names)
        print rows
        cur.execute("""INSERT INTO %s VALUES(%s, %s, %f, %f, %f )""" %(rows))         
        
        
        
x = SqliteCommands()
con, cur = x.onCreateDb()
x.onCreateTable(cur, "Steve")
t = ("03/02/2009", "Billing for electricity", 200.0, 230.0, 270.0)
x.onInsertRows(cur, "Steve", t)

Recommended Answers

All 8 Replies

The way you have written it, onCreateTable() doesn't seem to return anything. Maybe you should write a simpler test program first.

You also want a conn.comit() after inserting. And you should take advantage of using a class, plus change the first statement to use python.

#!/usr/bin/python
# SQLite command to be used with the applications
import sqlite3 as sqlite

class SqliteCommands:
    def ___init__(self, parent):
        self.parent = parent

    def list_all_recs( self, t_name ) :
        self.cur.execute("select * from %s" % (t_name))
        recs_list = self.cur.fetchall()
        for rec in recs_list:
           print rec

    # Create database
    def onCreateDb(self, t_name):

        self.conn = sqlite.connect("%s" % (t_name))
        self.cur = self.conn.cursor()
##        return (conn, cur)     ## not necessary

    #Create New Table
    def onCreateTable(self, t_name):        
       SQL_str="CREATE TABLE IF NOT EXISTS %s" %(t_name)
       SQL_str += "(Date TEXT, Explanations TEXT, Deposit REAL, "
       SQL_str += "Withdraw REAL, Balance REAL)"
       self.cur.execute(SQL_str)

    #Create Rows for the New table    
    def onInsertRows(self, t_name, tuple_rows):
        SQL_str ="INSERT INTO %s VALUES(?, ?, ?, ?, ? )" % (t_name)
        self.cur.execute(SQL_str, tuple_rows)         
        self.conn.commit()        


x = SqliteCommands()
x.onCreateDb("Steve")
x.onCreateTable("Steve")
t = ("03/02/2009", "Billing for electricity", 200.0, 230.0, 270.0)
x.onInsertRows("Steve", t)
t = ("03/02/2009", "Billing for gas", 100.0, 210.0, 200.0)
x.onInsertRows("Steve", t)
x.list_all_recs("Steve")

Maybe you should write a simpler test program first

This is good advice. First, test onCreateDb to see that it is working, then test onCreateTable, etc. When the program doesn't run at all there is something wrong with the shebang (#!/usr/bin/python).

Sorry, my bad, onCreateDb() is okay, didn't read it right.

Member Avatar for leegeorg07

you want to add a conn (connection) and cursor to use the conn

The problem is, it doesnt work when It reaches insert rows in the table. The Functions return nothing for now. Also FYI, this is the test program that I plan to incorporate in my small project.

Also Python.org doesn't recommend % place holder and recommend ? in its place. But when I use ? it crashes. Any Idea? (:

The program that I submitted works fine, with "?", on Python 2.5. Take a look at it. If you still can't get your program to run, then submit the code you are now using as it has changed from the first post.

sorry, I overlooked the code!
Thanks a lot. I'm going to test it. One question, why in my code, when I replaced % with ? didn't work?

I understood now. My mistake was, I was too extreme and applied ? even to the place holder for names. Thanks all for your insights
:)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.