SQLite and Placeholder

Question

Stefano Mtangoo 455 Senior Poster

15 Years Ago

I cannot get the code work. I want to use placeholder than a fixed value. When use fixed value it works but not placeholder. Please correct me!

import sqlite3 as sql

class Tables(object):
    def __init__(self):
        print "Class created!"
        
    def CreateDb(self, dbname):
        self.conn = sql.connect("?", (dbname, ))
        self.cur = self.conn.cursor()
        
    def CreateNewTable(self, tablename):#, tablename
        self.cur.execute("CREATE TABLE IF NOT EXISTS ?(id INTEGER, name TEXT)", (tablename, ))
        listed = [(1, "Mary"), (2, "Martha"), (3, "Jesca"), (4, "Don")]
        for i in listed:
            self.cur.execute("INSERT INTO test(id, name) VALUES(?, ?)", i)
        print self.cur.execute("SELECT * FROM test").fetchall()
        
    def DropTable(self, tablename):
        self.cur.execute("DROP TABLE ?", (tablename, ))
        
        


test = Tables()
test.CreateDb("test.cb")
test.CreateNewTable("testing")

python

3 Contributors
10 Replies
3K Views
5 Days Discussion Span
Latest Post 15 Years Ago Latest Post by Stefano Mtangoo

woooee 814 Nearly a Posting Maven

15 Years Ago

You have to commit
self.conn.commit()
after the adds

woooee 814 Nearly a Posting Maven

15 Years Ago

It's just
self.conn = sql.connect(dbname)

Here's some code (just a bunch of crap really) from when I was first testing SQLite that shows how to add, change and delete recs. Note that when you retrieve records you have to test for None, i.e. no records found in db. That threw me for a curve when first starting out.

import sqlite3 as sqlite

##----------------------------------------------------------------------
def add_rec(cur, con):
   ##cur.execute("INSERT INTO people (name_last, age) values ('Yeltsin',   72)")
   cur.execute("INSERT INTO people (name_last, age) values ('Putin',   50)")
   cur.execute("INSERT INTO people (name_last, age) values ('Putin',   50)")
   cur.execute("INSERT INTO people (name_last, age) values ('Putin',   50)")
   cur.execute("INSERT INTO people (name_last, age) values ('Yeltsin',   70)")

   ##---  add using a tuple instead
   person_tuple = ('Barisinov', '30')
   cur.execute("insert into people (name_last, age) values (?, ?)", person_tuple)
   con.commit()

##----------------------------------------------------------------------
def change_recs_test(cur, con):
   cur.execute("UPDATE people SET name_last==:who_dic WHERE age==:age_dic",
       {"who_dic": "Chg_Yeltsin", "age_dic": 70})
   con.commit()

##----------------------------------------------------------------------
def del_recs_test(cur, con):
   who = "Putin"
   age=50
   cur.execute("DELETE from people where name_last==:who_dic and age==:age_dic",
       {"who_dic": who, "age_dic": age})
   con.commit()
   print "-" * 30, "AFTER Delete"
   print_all_recs(cur)
   print
   
##----------------------------------------------------------------------
def print_all_recs(cur):
   # Execute the SELECT statement:
   print "Printing all recs"
   cur.execute("select * from people order by age")

   # Retrieve all rows as a sequence and print that sequence:
   print cur.fetchall()

#==========================================================================
if __name__ == "__main__":
   # Create a connection to the database file
   table_name = "people_db"
   con = sqlite.connect(table_name )

   # Get a Cursor object that operates in the context of Connection con
   cur = con.cursor()

   ##--- ONLY CREATE FIRST TIME
   cur.execute("CREATE TABLE people (name_last VARCHAR(20), age INT)")

   print "--------- starting select test --------------"
   cur.execute('SELECT * FROM people')
   result = cur.fetchone()
   print result
   test_tuple = ()
   if result is None:
      print "result is None"
   else:
      y = list(result)
      print y
      for row in result:
         stop = 10
         stop -= len(row[0])
         print "name =", row[0],    
         spaces = ""
         for j in range(0, stop):
            spaces += " "
         print spaces, " age =", row[1]
   
   ##----------------------------------------------------------------------
   print '\n---------- SELECT * FROM people where name_last="Putin"-------'
   x_test=cur.execute('SELECT * FROM people where name_last="Putin"')
   print '\nSELECT * FROM people where name_last="Putin"'
   ctr=0
   x = list(x_test)
   print "len(x)", len(x), type(x)
   for row in x:
      print row
      ctr += 1
   print ctr, "Putin recs found - used for delete test"
   
   ##----------------------------------------------------------------------
   who = "Putin"
   age = 51
   print "\nSELECT specific 'who' and 'age' fields:", who, age
   
   cur.execute("select name_last, age from people where name_last=:who_key and age=:age_key",
       {"who_key": who, "age_key": age})
   x=cur.fetchall()
   for row in x:
      print "Putin, 51 =", row
   
   add_rec(cur, con)           ## add some recs to delete
   print
   print "-" * 30, "BEFORE Delete"
   print "DELETE recs for:", who, age
   print_all_recs(cur)

jlm699 320 Veteran Poster

15 Years Ago

sqlite3.OperationalError: near "?": syntax error
File "f:\Hosanna\CodeBase\PyCodeBase\sqlite_on_test.py", line 26, in <module>
test.CreateNewTable("testing")
File "f:\Hosanna\CodeBase\PyCodeBase\sqlite_on_test.py", line 12, in CreateNewTable
self.cur.execute("CREATE TABLE IF NOT EXISTS ?(id INTEGER, name TEXT)", (tablename, ))

Yup, that's a syntax error in the SQL command. Read up on SQL syntax here.

Reply to this topic

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.

Stefano Mtangoo 455 Senior Poster · Answer 1 · 2009-08-07T08:48:10+00:00

It throws errors
CreateDb
TypeError: a float is required
File "f:\Hosanna\CodeBase\PyCodeBase\sqlite_on_test.py", line 25, in <module>
test.CreateDb("test.cb")
File "f:\Hosanna\CodeBase\PyCodeBase\sqlite_on_test.py", line 8, in CreateDb
self.conn = sql.connect("?", (dbname, ))

When i pass fixed value "test.db" explicitly Then CreateNewTable throws the following error

sqlite3.OperationalError: near "?": syntax error

File "f:\Hosanna\CodeBase\PyCodeBase\sqlite_on_test.py", line 26, in <module>
test.CreateNewTable("testing")
File "f:\Hosanna\CodeBase\PyCodeBase\sqlite_on_test.py", line 12, in CreateNewTable
self.cur.execute("CREATE TABLE IF NOT EXISTS ?(id INTEGER, name TEXT)", (tablename, ))

Stefano Mtangoo 455 Senior Poster · Answer 2 · 2009-08-11T04:31:38+00:00

Thanks woeeee for code.
Please jlm699, can you correct me where I go wrong. Sometimes you do mistakes and you just don't see them until they are pointed out :)

Stefano Mtangoo 455 Senior Poster · Answer 3 · 2009-08-11T04:37:35+00:00

It's just
self.conn = sql.connect(dbname)

Here's some code (just a bunch of crap really) from when I was first testing SQLite that shows how to add, change and delete recs. Note that when you retrieve records you have to test for None, i.e. no records found in db. That threw me for a curve when first starting out.

import sqlite3 as sqlite

##----------------------------------------------------------------------
def add_rec(cur, con):
   ##cur.execute("INSERT INTO people (name_last, age) values ('Yeltsin',   72)")
   cur.execute("INSERT INTO people (name_last, age) values ('Putin',   50)")
   cur.execute("INSERT INTO people (name_last, age) values ('Putin',   50)")
   cur.execute("INSERT INTO people (name_last, age) values ('Putin',   50)")
   cur.execute("INSERT INTO people (name_last, age) values ('Yeltsin',   70)")

   ##---  add using a tuple instead
   person_tuple = ('Barisinov', '30')
   cur.execute("insert into people (name_last, age) values (?, ?)", person_tuple)
   con.commit()

##----------------------------------------------------------------------
def change_recs_test(cur, con):
   cur.execute("UPDATE people SET name_last==:who_dic WHERE age==:age_dic",
       {"who_dic": "Chg_Yeltsin", "age_dic": 70})
   con.commit()

##----------------------------------------------------------------------
def del_recs_test(cur, con):
   who = "Putin"
   age=50
   cur.execute("DELETE from people where name_last==:who_dic and age==:age_dic",
       {"who_dic": who, "age_dic": age})
   con.commit()
   print "-" * 30, "AFTER Delete"
   print_all_recs(cur)
   print
   
##----------------------------------------------------------------------
def print_all_recs(cur):
   # Execute the SELECT statement:
   print "Printing all recs"
   cur.execute("select * from people order by age")

   # Retrieve all rows as a sequence and print that sequence:
   print cur.fetchall()

#==========================================================================
if __name__ == "__main__":
   # Create a connection to the database file
   table_name = "people_db"
   con = sqlite.connect(table_name )

   # Get a Cursor object that operates in the context of Connection con
   cur = con.cursor()

   ##--- ONLY CREATE FIRST TIME
   cur.execute("CREATE TABLE people (name_last VARCHAR(20), age INT)")

   print "--------- starting select test --------------"
   cur.execute('SELECT * FROM people')
   result = cur.fetchone()
   print result
   test_tuple = ()
   if result is None:
      print "result is None"
   else:
      y = list(result)
      print y
      for row in result:
         stop = 10
         stop -= len(row[0])
         print "name =", row[0],    
         spaces = ""
         for j in range(0, stop):
            spaces += " "
         print spaces, " age =", row[1]
   
   ##----------------------------------------------------------------------
   print '\n---------- SELECT * FROM people where name_last="Putin"-------'
   x_test=cur.execute('SELECT * FROM people where name_last="Putin"')
   print '\nSELECT * FROM people where name_last="Putin"'
   ctr=0
   x = list(x_test)
   print "len(x)", len(x), type(x)
   for row in x:
      print row
      ctr += 1
   print ctr, "Putin recs found - used for delete test"
   
   ##----------------------------------------------------------------------
   who = "Putin"
   age = 51
   print "\nSELECT specific 'who' and 'age' fields:", who, age
   
   cur.execute("select name_last, age from people where name_last=:who_key and age=:age_key",
       {"who_key": who, "age_key": age})
   x=cur.fetchall()
   for row in x:
      print "Putin, 51 =", row
   
   add_rec(cur, con)           ## add some recs to delete
   print
   print "-" * 30, "BEFORE Delete"
   print "DELETE recs for:", who, age
   print_all_recs(cur)

I want to create table without specifying literal table name. I want user to specify it and using placeholders, I want to programatically create it. I cannot find such in your code (correct me if i'm wrong)

woooee 814 Nearly a Posting Maven · Answer 4 · 2009-08-11T05:57:12+00:00

You would use a variable like "table_name" in the first line here, and pass that to the connect function. If you are creating a new table, then you would also have to input the field names and type, or use a standard layout for all databases.

table_name = "people_db"
   con = sqlite.connect(table_name )

   # Get a Cursor object that operates in the context of Connection con
   cur = con.cursor()

   ##--- ONLY CREATE FIRST TIME
   cur.execute("CREATE TABLE people (name_last VARCHAR(20), age INT)")

Stefano Mtangoo 455 Senior Poster · Answer 5 · 2009-08-11T06:24:41+00:00

You would use a variable like "table_name" in the first line here, and pass that to the connect function. If you are creating a new table, then you would also have to input the field names and type, or use a standard layout for all databases.
table_name = "people_db"
   con = sqlite.connect(table_name )

   # Get a Cursor object that operates in the context of Connection con
   cur = con.cursor()

   ##--- ONLY CREATE FIRST TIME
   cur.execute("CREATE TABLE [B]people[/B] (name_last VARCHAR(20), age INT)")

No problem now on using database placeholder. I want now table name to be a variable and not fixed value. As an example, can you change the statement to use place holder instead of fixed tablename people? (Red coloured one)

woooee 814 Nearly a Posting Maven · Answer 6 · 2009-08-11T07:17:05+00:00

Note the quotation marks, so it is just a string so you can substitute anything you like.

SQL_str="CREATE TABLE %s (name_last VARCHAR(20), age INT)" % (name_to_use)
cur.execute(SQL_str)

Stefano Mtangoo 455 Senior Poster · Answer 7 · 2009-08-12T03:16:05+00:00

Note the quotation marks, so it is just a string so you can substitute anything you like.
SQL_str="CREATE TABLE %s (name_last VARCHAR(20), age INT)" % (name_to_use)
cur.execute(SQL_str)

That settled this problem once and for all
Thanks alot