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)