def test01(self): #self.assertEqual(sqlite.version,'2.0.3') conn = sqlite.connect(':memory:') csr = conn.cursor() csr.execute("""CREATE TABLE Partners ( id BIGINT, name VARCHAR(50), PRIMARY KEY (id) );""") #conn.commit() csr.execute("""INSERT INTO Partners (id, name) VALUES ( 2, 'Andreas Arens')""") #conn.commit() ## csr.execute("""SELECT id, name FROM Partners;""") ## self.assertEqual(len(csr.fetchall()),1) csr.execute(""" UPDATE Partners SET name = 'Arens, Andreas' WHERE id = 2; """) csr.close() conn.commit() conn.close()
def test01(self): #self.assertEqual(sqlite.version,'2.0.3') conn = sqlite.connect(':memory:') csr = conn.cursor() csr.execute("""CREATE TABLE Partners ( id BIGINT, name VARCHAR(50), PRIMARY KEY (id) );""") #conn.commit() csr.execute("""INSERT INTO Partners (id, name) VALUES ( 2, 'Andreas Arens')""") #conn.commit() ## csr.execute("""SELECT id, name FROM Partners;""") ## self.assertEqual(len(csr.fetchall()),1) csr.execute(""" UPDATE Partners SET name = 'Arens, Andreas' WHERE id = 2; """) csr.close() conn.commit() conn.close()
def test01(self): conn = sqlite.connect(':memory:') csr1 = conn.cursor() csr1.execute("""CREATE TABLE Partners ( id BIGINT, name VARCHAR(50), PRIMARY KEY (id) );""") csr1.execute("""INSERT INTO Partners (id, name) VALUES ( 1, 'Anton Ausdemwald')""") csr1.execute("""INSERT INTO Partners (id, name) VALUES ( 2, 'Andreas Arens')""") csr1.execute("""SELECT id, name FROM Partners;""") # Executing a SELECT causes a shared lock to be placed on the # queried table(s) until the cursor's result has been # completely retrieved. That's why an UPDATE (or INSERT or # DELETE) in this table using *another* cursor will be # refused: csr2 = conn.cursor() try: csr2.execute(""" UPDATE Partners SET name = 'Arens, Andreas' WHERE id = 2; """) self.fail("failed to raise OperationalError") except sqlite.OperationalError,e: self.assertEqual( str(e),\ "database table is locked")
def test01(self): conn = sqlite.connect(':memory:') #csr = sqlite.Cursor(conn,TupleType) csr = conn.cursor() csr.execute("""CREATE TABLE Nations ( id char(50), name varchar(80), curr char(3)) """) csr.execute("""INSERT INTO Nations VALUES ('be', 'Belgium', 'EUR') """) csr.execute("""INSERT INTO Nations VALUES ('ee', 'Estonia', 'EEK') """) csr.execute("""SELECT id, name, curr from Nations WHERE id = 'ee' """) #self.assertEqual(csr.rowcount,1) row = csr.fetchone() self.assertEqual(row[0], 'ee') self.assertEqual(row[1], 'Estonia') self.assertEqual(row[2], 'EEK') csr.execute("""SELECT id, name, curr from Nations WHERE id = 'foo' """) # this fails with pysqlite #self.assertEqual(csr.rowcount,0) row = csr.fetchone() self.assertEqual(row, None)
def test01(self): conn = sqlite.connect(':memory:') #csr = sqlite.Cursor(conn,TupleType) csr = conn.cursor() csr.execute("""CREATE TABLE Nations ( id char(50), name varchar(80), curr char(3)) """) csr.execute("""INSERT INTO Nations VALUES ('be', 'Belgium', 'EUR') """) csr.execute("""INSERT INTO Nations VALUES ('ee', 'Estonia', 'EEK') """) csr.execute("""SELECT id, name, curr from Nations WHERE id = 'ee' """) #self.assertEqual(csr.rowcount,1) row = csr.fetchone() self.assertEqual(row[0],'ee') self.assertEqual(row[1],'Estonia') self.assertEqual(row[2],'EEK') csr.execute("""SELECT id, name, curr from Nations WHERE id = 'foo' """) # this fails with pysqlite #self.assertEqual(csr.rowcount,0) row = csr.fetchone() self.assertEqual(row,None)
def test01(self): conn = sqlite.connect(':memory:') csr1 = conn.cursor() csr1.execute("""CREATE TABLE Partners ( id BIGINT, name VARCHAR(50), PRIMARY KEY (id) );""") csr1.execute("""INSERT INTO Partners (id, name) VALUES ( 1, 'Anton Ausdemwald')""") csr1.execute("""INSERT INTO Partners (id, name) VALUES ( 2, 'Andreas Arens')""") csr1.execute("""SELECT id, name FROM Partners;""") # Executing a SELECT causes a shared lock to be placed on the # queried table(s) until the cursor's result has been # completely retrieved. That's why an UPDATE (or INSERT or # DELETE) in this table using *another* cursor will be # refused: csr2 = conn.cursor() try: csr2.execute(""" UPDATE Partners SET name = 'Arens, Andreas' WHERE id = 2; """) self.fail("failed to raise OperationalError") except sqlite.OperationalError, e: self.assertEqual( str(e),\ "database table is locked")
def test01(self): def month(s): d = datetime.date.fromordinal(s) return d.month def year(s): d = datetime.date.fromordinal(s) return d.year def day(s): d = datetime.date.fromordinal(s) return d.day conn = sqlite.connect(':memory:') #detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES) conn.create_function("month", 1, month) conn.create_function("year", 1, year) conn.create_function("day", 1, day) csr = conn.cursor() # create and fill Tester table csr.execute("CREATE TABLE Days (date date)") sql = "INSERT INTO Days (date) VALUES ( %d )" for i in range(732098, 732103): csr.execute(sql % i) csr.execute("SELECT date from Days") s = " ".join([str(l[0]) for l in csr.fetchall()]) #print s self.assertEqual(s, "732098 732099 732100 732101 732102") csr.execute("SELECT date from Days WHERE month(date) = 6") s = " ".join([str(l[0]) for l in csr.fetchall()]) #print s self.assertEqual(s, "732098 732099 732100 732101 732102") # don't do anything with the cursor try: conn.close() except sqlite.OperationalError: # known bug in pysqlite 2.0.3 self.assertEqual(sqlite.version, '2.0.3')
def test01(self): if sqlite.version != '2.0.3': return conn = sqlite.connect(':memory:') csr = conn.cursor() # create and fill Tester table csr.execute("CREATE TABLE Versuch (id int, name varchar(80))") sql = "INSERT INTO Versuch VALUES (%d, 'This is row %d')" for i in range(100): csr.execute(sql % (i, i)) csr.execute("SELECT id, name from Versuch WHERE id < 10") # don't do anything with the cursor try: conn.close() self.fail("failed to raise OperationalError") except sqlite.OperationalError: pass
def test01(self): if sqlite.version != '2.0.3': return conn = sqlite.connect(':memory:') csr = conn.cursor() # create and fill Tester table csr.execute("CREATE TABLE Versuch (id int, name varchar(80))") sql="INSERT INTO Versuch VALUES (%d, 'This is row %d')" for i in range(100): csr.execute( sql % (i,i)) csr.execute("SELECT id, name from Versuch WHERE id < 10") # don't do anything with the cursor try: conn.close() self.fail("failed to raise OperationalError") except sqlite.OperationalError: pass
def test01(self): def month(s): d=datetime.date.fromordinal(s) return d.month def year(s): d=datetime.date.fromordinal(s) return d.year def day(s): d=datetime.date.fromordinal(s) return d.day conn = sqlite.connect( ':memory:') #detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES) conn.create_function("month",1,month) conn.create_function("year",1,year) conn.create_function("day",1,day) csr = conn.cursor() # create and fill Tester table csr.execute("CREATE TABLE Days (date date)") sql="INSERT INTO Days (date) VALUES ( %d )" for i in range(732098,732103): csr.execute(sql % i) csr.execute("SELECT date from Days") s=" ".join([str(l[0]) for l in csr.fetchall()]) #print s self.assertEqual(s,"732098 732099 732100 732101 732102") csr.execute("SELECT date from Days WHERE month(date) = 6") s=" ".join([str(l[0]) for l in csr.fetchall()]) #print s self.assertEqual(s,"732098 732099 732100 732101 732102") # don't do anything with the cursor try: conn.close() except sqlite.OperationalError: # known bug in pysqlite 2.0.3 self.assertEqual(sqlite.version,'2.0.3')
def test01(self): conn = sqlite.connect(':memory:') csr = conn.cursor() f=codecs.open(filename,encoding="cp1252") sql="" lengths=[] inserts=0 for ln in f: ln=ln.strip() if not ln.startswith('#'): if ln.endswith(";"): sql += ln[:-1] csr.execute(sql) #conn.commit() #print sql #print if sql.startswith("SELECT "): # use the cursor up to avoid work around # pysqlite bug #for t in csr: # print t lengths.append(len(csr.fetchall())) #print "--> %d rows" % len(csr.fetchall()) elif sql.startswith("INSERT "): inserts+=1 csr.close() #else: # conn.commit() # print "(conn.commit())" sql="" else: sql+=ln conn.close() #print lengths #print "%d INSERT statements" % inserts ## self.assertEqual(lengths, ## [0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, ## 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ## 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 7]) self.assertEqual( lengths, [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 1, 1, 1, 15, 1, 5] ) self.assertEqual(inserts,5191)
def test01(self): conn = sqlite.connect(':memory:') csr = conn.cursor() f = codecs.open(filename, encoding="cp1252") sql = "" lengths = [] inserts = 0 for ln in f: ln = ln.strip() if not ln.startswith('#'): if ln.endswith(";"): sql += ln[:-1] csr.execute(sql) #conn.commit() #print sql #print if sql.startswith("SELECT "): # use the cursor up to avoid work around # pysqlite bug #for t in csr: # print t lengths.append(len(csr.fetchall())) #print "--> %d rows" % len(csr.fetchall()) elif sql.startswith("INSERT "): inserts += 1 csr.close() #else: # conn.commit() # print "(conn.commit())" sql = "" else: sql += ln conn.close() #print lengths #print "%d INSERT statements" % inserts ## self.assertEqual(lengths, ## [0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, ## 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ## 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 7]) self.assertEqual(lengths, [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 1, 1, 1, 15, 1, 5 ]) self.assertEqual(inserts, 5191)