Hi all,
I have been trying to write and retrieve data from SQLite Database. So far I can make connection, cursor; but cannot get table populated(fails to execute SQL statement). Since I read somewhere that it is recommended to use ? instead of % then I tries it but I run errors. Below is the code and traceback error

Thanks alot,

def onNewTable(self, cursor, t_name):    
        t_date = "Date of Transaction"
        t_explanations = "Transaction Explanations"
        t_deposit = "Deposit Equivalent"
        t_withdraw = "Withdraw Equivalent"
        t_balance = "Balance After Transaction"        
        cursor.execute("CREATE TABLE  ? (? CHAR, ? CHAR, ? CHAR, ? CHAR, ? CHAR)" (t_name, t_date, t_explanations, t_deposit, t_withdraw, t_balance)  ) 
        cursor.commit()

File "I:\Documents\Projects\Coding\Financial Management\MainApp_tabbed.py", line 201, in onNewFile
self.onNewTable(cur, "steve_test")
File "I:\Documents\Projects\Coding\Financial Management\MainApp_tabbed.py", line 215, in onNewTable
cursor.execute("CREATE TABLE ? (? CHAR, ? CHAR, ? CHAR, ? CHAR, ? CHAR)" (t_name, t_date, t_explanations, t_deposit, t_withdraw, t_balance) )
TypeError: 'str' object is not callable

You have to give each field in the SQLite db a name so you can access each one individually, and define it as a variable length character field, integer, etc.. Select where sql_date == date_today kind of thing. Assuming that everything is a string...

## Create a connection to the database file
con = sqlite.connect('test_db')
## Get a Cursor object that operates in the context of Connection con
cursor = con.cursor()
 
cursor.execute('''create table test_db(sql_date varchar,sql_exp varchar, sql_deposit varchar, sql_withdraw varchar, sql_balance varchar)''')

val_tuple=(t_date, t_explanations, t_deposit, t_withdraw, t_balance)
cursor.execute('INSERT INTO test_db values (?,?,?,?,?)', val_tuple)
con.commit()

I Haven't catched you, can you elaborate?

You have a TypeError: str object is not callable because the syntax "mystring"(myargs) is invalid.

ok I understod now, I forgot a comma. Also SQLite uses TEXT instead of CHAR (which I found in mysql) (open to correction)
Now inserting that comma and changing to TEXT i still have error:

def onNewTable(self, cursor, t_name):
    
        t_date = "Date of Transaction"
        t_explanations = "Transaction Explanations"
        t_deposit = "Deposit Equivalent"
        t_withdraw = "Withdraw Equivalent"
        t_balance = "Balance After Transaction"
        
        cursor.execute("CREATE TABLE  ? (? TEXT, ? TEXT, ? TEXT, ? TEXT, ? TEXT)", (t_name, t_date, t_explanations, t_deposit, t_withdraw, t_balance)  )

sqlite3.OperationalError: near "?": syntax error

tried do little modification and came up with this but still error! :(
What I'm I missing?

def onNewTable(self, cursor, t_name):
    
        t_date = "Date of Transaction"
        t_explanations = "Transaction Explanations"
        t_deposit = "Deposit Equivalent"
        t_withdraw = "Withdraw Equivalent"
        t_balance = "Balance After Transaction"
        
        cursor.execute("CREATE TABLE  table = ? (col1 = ? TEXT, col2 = ? TEXT, col3 = ? TEXT, col4 = ? TEXT, col5 = ? TEXT)", (t_name, t_date, t_explanations, t_deposit, t_withdraw, t_balance)  )

full error decription

sqlite3.OperationalError: near "table": syntax error
File "i:\Documents\Projects\Coding\Financial Management\MainApp_tabbed.py", line 257, in <module>
Application().MainLoop()
File "C:\Python25\Lib\site-packages\wx-2.8-msw-unicode\wx\_core.py", line 7942, in MainLoop
wx.PyApp.MainLoop(self)
File "C:\Python25\Lib\site-packages\wx-2.8-msw-unicode\wx\_core.py", line 7267, in MainLoop
return _core_.PyApp_MainLoop(*args, **kwargs)
File "i:\Documents\Projects\Coding\Financial Management\MainApp_tabbed.py", line 201, in onNewFile
self.onNewTable(cur, "steve_test")
File "i:\Documents\Projects\Coding\Financial Management\MainApp_tabbed.py", line 215, in onNewTable
cursor.execute("CREATE TABLE table = ? VALUES(col1 = ? TEXT, col2 = ? TEXT, col3 = ? TEXT, col4 = ? TEXT, col5 = ? TEXT)", (t_name, t_date, t_explanations, t_deposit, t_withdraw, t_balance) )

cursor.execute("CREATE TABLE  ? (? TEXT, ? TEXT, ? TEXT, ? TEXT, ? TEXT)", (t_name, t_date, t_explanations, t_deposit, t_withdraw, t_balance)  )

Are you allowed to create things with a '?' for the name? That doesn't look right to me...

mhh, I'm new to SQLite and want to Create table without passing strings literal, because names can be subject to change. I want something like:

name = "Jimmy"
print "My name is %s " %(name, )

How do I do that in SQLite?

Well you can still use your Python skills on the query that you send to SQL, it's only the syntax within the quotes that needs to be SQL:

cursor.execute("CREATE TABLE %s (%s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT)" % (t_name, t_date, t_explanations, t_deposit, t_withdraw, t_balance)  )

I don't use this so am not sure, but you can do something along the lines of this so you have a pre-defined string instead of substitution in the SQL statement.

field_type = "TEXT"
SQL_statement = "CREATE TABLE %s (%s %s, %s %s, %s %s, %s %s, %s %s)" % \
     (t_name, field_type, \
      t_date, field_type, \
      t_explanations, field_type, \
      t_deposit, field_type, \
      t_withdraw, field_type, \
      t_balance, field_type)
print SQL_statement
cursor.execute(SQL_statement)

Note that in the example you linked in the previous post, they are using VARCHAR, which I always use, so if you get an error try changing TEXT to VARCHAR. SQLite does not support a full SQL set of statements but has most of the stuff that a normal user will use.

Edit: Doh! I have a work-in-progress program that I use to create a simple and quick database with access. I fed in your fields, and the following came out which hopefully should help some. It sould be trivial to pass the database's name to the class as a variable or change the number of fields, etc.

##======================================================================
class DummyClass:
   def __init__( self ) :
      self.SQL_filename = './test_dbf'
      self.open_files()

   ##   END  __init__()

   ##----------------------------------------------------------------------
   def add_rec( self ) :
      val_tuple=(self.t_date, self.t_explanations, self.t_deposit, self.t_withdraw, self.t_balance)
      self.cur.execute('INSERT INTO test_dbf values (?,?,?,?,?)', val_tuple)
      self.con.commit()

   ##   END  AddRec()

   ##----------------------------------------------------------------------
   def copy_to_struct( self, rec ) :
      self.t_date = rec[0]
      self.t_explanations = rec[1]
      self.t_deposit = rec[2]
      self.t_withdraw = rec[3]
      self.t_balance = rec[4]

   ##   END  copy_to_struct()

   ##----------------------------------------------------------------------
   def list_all_recs( self ) :
      self.cur.execute("select * from test_dbf")
      recs_list = self.cur.fetchall()
      for rec in recs_list:
         print rec

   ##   END  list_all_recs

   ##----------------------------------------------------------------------
   def lookup_first_field( self ) :
      self.cur.execute("select * from test_dbf where t_date==:dic_lookup", {"dic_lookup":"test_A_0"})
      recs_list = self.cur.fetchall()
      print
      print "lookup_first_field (test_A_0)" 
      for rec in recs_list:
         self.copy_to_struct(rec)
         self.print_rec()

   ##   END  lookup_first_field()

   ##----------------------------------------------------------------------
   def lookup_first_2_fields( self, lookup_dic ) :
      self.cur.execute("select * from test_dbf where t_date==:dic_field_1 and t_explanations==:dic_field_2", lookup_dic)

      recs_list = self.cur.fetchall()
      print
      print "lookup_first_2_fields (test_A_0 + test_A_1)" 
      for rec in recs_list:
         print rec

   ##   END  lookup_first_2_field()

   ##----------------------------------------------------------------------
   def open_files( self ) :
         ##  a connection to the database file
         self.con = sqlite.connect('./test_dbf')
         # Get a Cursor object that operates in the context of Connection con
         self.cur = self.con.cursor()

         ##--- CREATE FILE ONLY IF IT DOESN'T EXIST
         if os.path.getsize('./test_dbf') < 10:
            self.cur.execute('''create table test_dbf(t_date varchar, t_explanations varchar, t_deposit varchar, t_withdraw varchar, t_balance varchar)''')

   ##   END  open_files()

   ##----------------------------------------------------------------------
   def print_rec( self ) :
      spaces = ""
      print spaces, "t_date =", self.t_date
      spaces = "     "
      print spaces, "t_explanations =", self.t_explanations
      print spaces, "t_deposit =", self.t_deposit
      print spaces, "t_withdraw =", self.t_withdraw
      print spaces, "t_balance =", self.t_balance

   ##   END  rec_struct()

   ##----------------------------------------------------------------------
   def rec_struct( self ) :
      self.t_date = ""
      self.t_explanations = ""
      self.t_deposit = ""
      self.t_withdraw = ""
      self.t_balance = ""

   ##   END  rec_struct()

##----------------------------------------------------------------------
def test_data( class_ptr ) :
   print 'test_data --> add records'
   class_ptr.rec_struct()
   class_ptr.t_date = "test_A_0"
   class_ptr.t_explanations = "test_A_1"
   class_ptr.t_deposit = "test_A_2"
   class_ptr.t_withdraw = "test_A_3"
   class_ptr.t_balance = "test_A_4"
   class_ptr.add_rec()

   class_ptr.t_date = "test_B_0"
   class_ptr.t_explanations = "test_B_1"
   class_ptr.t_deposit = "test_B_2"
   class_ptr.t_withdraw = "test_B_3"
   class_ptr.t_balance = "test_B_4"
   class_ptr.add_rec()

   class_ptr.t_date = "test_C_0"
   class_ptr.t_explanations = "test_C_1"
   class_ptr.t_deposit = "test_C_2"
   class_ptr.t_withdraw = "test_C_3"
   class_ptr.t_balance = "test_C_4"
   class_ptr.add_rec()

   class_ptr.t_date = "test_A_0"
   class_ptr.t_explanations = "test_A_2"
   class_ptr.t_deposit = "test_A_3"
   class_ptr.t_withdraw = "test_A_4"
   class_ptr.t_balance = "test_A_5"
   class_ptr.add_rec()

   class_ptr.t_date = "test_B_0"
   class_ptr.t_explanations = "test_B_2"
   class_ptr.t_deposit = "test_B_3"
   class_ptr.t_withdraw = "test_B_4"
   class_ptr.t_balance = "test_B_5"
   class_ptr.add_rec()

   class_ptr.t_date = "test_C_0"
   class_ptr.t_explanations = "test_C_2"
   class_ptr.t_deposit = "test_C_3"
   class_ptr.t_withdraw = "test_C_4"
   class_ptr.t_balance = "test_C_5"
   class_ptr.add_rec()

   class_ptr.list_all_recs()

##   END  test_data()

##======================================================
if __name__ == '__main__':
   try :
      DM=DummyClass()
      test_data( DM )
      DM.lookup_first_field()

      lookup_dic = {"dic_field_1":"test_A_0", 
                    "dic_field_2":"test_A_1"}
      DM.lookup_first_2_fields(lookup_dic)
   except :
      import traceback
      traceback.print_exc()
      raise

I think it's suspicious to have column names with spaces. I doubt it's compatible with the SQL syntax. You should try to replace the spaces by underscores.

#create table - tuple of column and table name
    def onNewTable(self, cursor, t_name):        
        cursor.execute("CREATE TABLE  %s(date DATE, transaction_expl CHAR, deposit_equi INTEGER, withdral_equi INTEGER, balance INTEGER)" %(t_name,))
        cursor.execute('INSERT INTO  %s(date, transaction_expl, deposit_equi, withdral_equi, balance) VALUES("12/11/2008", "fee", 500, 300, 200)' %(t_name))
This question has already been answered. Start a new discussion instead.