SQLite Problem

[Stefano Mtangoo]

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

[Gribouillis]

There should be something here CHAR, ? CHAR)" (t_name, t_date between the string and the opening parenthesis :)

[Stefano Mtangoo]

I Haven't catched you, can you elaborate?

[woooee]

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()

[Gribouillis]

I Haven't catched you, can you elaborate?

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

[Stefano Mtangoo]

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

[Stefano Mtangoo]

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) )

[jlm699]

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...

[Stefano Mtangoo]

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?

[jlm699]

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)  )

[Stefano Mtangoo]

but I read somehwere that, the character % is dangerous and is to be avoided as it can make SQL injection easier and they suggested ? as placeholder. I don't know what is wrong!
Here is example:
http://www.devshed.com/c/a/Python/Using-SQLite-in-Python/2/

[woooee]

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

[Gribouillis]

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.

[Stefano Mtangoo]

I will try those, thanks guys

[Stefano Mtangoo]

#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))

[Stefano Mtangoo]

decided to use %s temporarily, until I get permanent solution.
Thanks all!