[sqlite] Re; update problem
inq1ltd
# -*- coding: utf-8 -*-
import sqlite3
This email copy may be easier to work with rather than the previous one.
If someone has the time I would appreciate it if you
could run this module.
Everything works except the class Kupdate.
For some reason I don't get any complaints from
python or sqlite but when selecting from the table I
cannot retrieve the last update on line 113, ( '1)
Add New Name' ). any suggestions would be
helpful.
jd
class KmkSql:
def __init__(self) :
self.sqldb = 'ZZSQL_TEST.sql'
KmkSql.sqldb = self.sqldb
self.tablename = tablename = 'abc800_111_1111_FIRST'
print tablename, '## tablename sql_test 24 \n'
self.vRecordKey = ['abc', '800 111 1111', 'FIRST' ]
self.FmkSql_01()
def FmkSql_01(self) :
print '## locate sql_test 34\n'
self.con = sqlite3.connect(self.sqldb)
self.cursor = self.con.cursor()
self.cursor.execute ("""CREATE TABLE IF NOT EXISTS """ +
self.tablename + """
(pink varchar,
green varchar,
rec_Rowname varchar,
row_1 varchar,
ent_1 varchar,
notes varchar,
rowcount
)"""
);
self.con.commit()
self.FaddDefault()
def FaddDefault(self) :
vrowcount = 'rowcount'
vpink = str(self.vRecordKey[0] )
vgreen = str(self.vRecordKey[1] )
rec_Rowname = str(self.vRecordKey[2] )
self.cursor.execute("""SELECT """ + vrowcount + """ FROM """ +
self.tablename + """ WHERE pink = (?) AND green = (?) AND rec_Rowname = (?)
""", [self.vRecordKey[0], self.vRecordKey[1], self.vRecordKey[2] ]);
self.con.commit()
Rcount = self.cursor.fetchall() ## unicode
self.con.commit()
print Rcount, '## Rcount sql_test 69 \n'
if Rcount == [] :
Kinsert( self.sqldb, self.tablename, self.vRecordKey )
Kupdate (self.sqldb, self.tablename, self.vRecordKey)
else :
self.con.close()
Kupdate(self.sqldb, self.tablename, self.vRecordKey )
class Kinsert:
def __init__(self, sqldb, tablename, vRecordKey ) :
self.tablename = tablename
self.vRecordKey = vRecordKey
print vRecordKey, '## vRecordKey sql_test 97 \n'
self.sqldb = sqldb
self.Finsert()
def Finsert(self) :
vpink = str(self.vRecordKey[0] )
vgreen = str(self.vRecordKey[1] )
rec_Rowname = str(self.vRecordKey[2] )
norow = '1) No Row Name'
self.con = sqlite3.connect(self.sqldb) # open DB
self.cursor = self.con.cursor()
self.cursor.execute ("""INSERT INTO """ + self.tablename + """ VALUES
(?,?,?,?,?,?,?) """ , [ vpink, vgreen, rec_Rowname, norow, '', '', '1' ] )
;
self.con.commit()
self.con.close()
class Kupdate :
def __init__(self, sqldb, tablename, vRecordKey ) :
self.tablename = tablename
self.colname = 'row_1'
self.divAddName = '1) Add New Name'
self.sqldb = sqldb
self.vRecordKey = vRecordKey
self.Fupdate()
def Fupdate(self) :
vpink = str(self.vRecordKey[0] )
vgreen = str(self.vRecordKey[1] )
rec_Rowname = str(self.vRecordKey[2] )
self.con = sqlite3.connect(self.sqldb) # open DB
self.cursor = self.con.cursor()
self.cursor.execute (""" UPDATE """ + self.tablename + """ SET """ +
self.colname + """ = (?) WHERE pink = (?) AND green = (?) AND rec_Rowname =
(?) """, [ self.divAddName, vpink, vgreen, rec_Rowname ] );
self.con.commit
self.con.close()
Kselect( self.sqldb, self.tablename, self.vRecordKey, self.colname)
class Kselect:
def __init__(self, sqldb, tablename, vRecordKey, colname ) :
self.vRecordKey = vRecordKey
self.Fselect(sqldb, tablename, vRecordKey, colname)
def Fselect(self, sqldb, tablename, vRecordKey, colname) :
vpink = str(self.vRecordKey[0] )
vgreen = str(self.vRecordKey[1] )
rec_Rowname = str(self.vRecordKey[2] )
con = sqlite3.connect(sqldb) # open DB
cursor = con.cursor()
cursor.execute("""SELECT """ + colname + """ FROM """ + tablename +
""" WHERE pink = (?) AND green = (?) AND rec_Rowname = (?) """, [vpink,
vgreen, rec_Rowname ] );
con.commit()
divcolinfo = cursor.fetchall() ## unicode
con.commit()
con.close()
print divcolinfo, '## divcolinfo sql_test 170 \n'
if __name__ == "__main__" :
KmkSql()
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Black, Michael (IS)
./py
abc800_111_1111_FIRST ## tablename sql_test 24
## locate sql_test 34
[] ## Rcount sql_test 69
['abc', '800 111 1111', 'FIRST'] ## vRecordKey sql_test 97
[(u'1) No Row Name',)] ## divcolinfo sql_test 170
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE abc800_111_1111_FIRST
(pink varchar,
green varchar,
rec_Rowname varchar,
row_1 varchar,
ent_1 varchar,
notes varchar,
rowcount
);
INSERT INTO "abc800_111_1111_FIRST" VALUES('abc','800 111 1111','FIRST','1) No Row Name','','','1');
COMMIT;
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: sqlite-use...@sqlite.org [sqlite-use...@sqlite.org] on behalf of inq1ltd [inq...@inqvista.com]
Sent: Friday, February 03, 2012 4:32 PM
To: sqlite...@sqlite.org
Subject: EXT :[sqlite] Re; update problem
Black, Michael (IS)
Change
self.con.commit
To
self.con.commit()
In your Fupdate function
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE abc800_111_1111_FIRST
(pink varchar,
green varchar,
rec_Rowname varchar,
row_1 varchar,
ent_1 varchar,
notes varchar,
rowcount
);
INSERT INTO "abc800_111_1111_FIRST" VALUES('abc','800 111 1111','FIRST','1) Add New Name','','','1');
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: sqlite-use...@sqlite.org [sqlite-use...@sqlite.org] on behalf of inq1ltd [inq...@inqvista.com]
Sent: Friday, February 03, 2012 4:32 PM
To: sqlite...@sqlite.org
Subject: EXT :[sqlite] Re; update problem
#!/usr/bin/env python
inq1ltd
Thanks for responding,
Every time this module runs, it calls
the Kupdate class.
In the Kupdate class
the data in the collumn named "row_1"
should be updated to contain the value,
'1) Add New Name'
see >> self.divAddName = '1) Add New Name' << in Kupdate
When finished updating, Kupdate calls Kselect,
Kselect always returns >> '1) No Row Name' <<
I expect Kselect to return >> '1) Add New Name' <<
Why doesn't Kupdate update the data in the table ?
I appreciate the help,
jd
inq1ltd
self.con.commit()
wins the prize.
Thanks,
now if I can just find () on the keyboard
jd
inq1ltd
Can someone tell me how to find the names of the tables that exist in a
database?
I don't see it in the list of Pragma's
jd
Luuk
> sqlite3 help,
>
>
> Can someone tell me how to find the names of the tables that exist in a
> database?
>
> I don't see it in the list of Pragma's
>
.help;
you'll get a list of things, pick the right one.... ;)
Stephan Beal
> Can someone tell me how to find the names of the tables that exist in a
> database?
sqlite> create table t1(a);
sqlite> create table t2(a);
sqlite> .h on
sqlite> select * from sqlite_master where type='table';
type|name|tbl_name|rootpage|sql
table|t1|t1|2|CREATE TABLE t1(a)
table|t2|t2|3|CREATE TABLE t2(a)
--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
inq1ltd
> On Sat, Feb 25, 2012 at 6:02 PM, inq1ltd <inq...@inqvista.com> wrote:
> > Can someone tell me how to find the names of the tables that exist in a
> > database?
>
> sqlite> create table t1(a);
> sqlite> create table t2(a);
> sqlite> .h on
> sqlite> select * from sqlite_master where type='table';
> type|name|tbl_name|rootpage|sql
> table|t1|t1|2|CREATE TABLE t1(a)
> table|t2|t2|3|CREATE TABLE t2(a)
I appreciate the help, thanks to all.
