def update_data():
print('TEST: Update data...')
update_sql = 'UPDATE student SET name = ? WHERE ID = ? '
data = [('HongtenAA', 1), ('HongtenBB', 2), ('HongtenCC', 3),
('HongtenDD', 4)]
conn = dbhelper.get_conn(DB_FILE_PATH)
dbhelper.update(conn, update_sql, data)
def save_data():
print('TEST: Save data...')
save_sql = '''INSERT INTO student values (?, ?, ?, ?, ?, ?)'''
data = [(1, 'Hongten', u'男', 20, u'广东省广州市', '13423****62'),
(2, 'Tom', u'男', 22, u'美国旧金山', '15423****63'),
(3, 'Jake', u'女', 18, u'广东省广州市', '18823****87'),
(4, 'Cate', u'女', 21, u'广东省广州市', '14323****32')]
conn = dbhelper.get_conn(DB_FILE_PATH)
dbhelper.save(conn, save_sql, data)
def save_data():
print(LOG + 'Save data...')
save_sql = '''INSERT INTO 'roe_revenue' values (?, ?, ?, ?, ?, ?, ?)'''
data = [(000001, '000001', u'汤臣倍健', -190.08, 0, 263.03, 0),
(000002, '000001', u'中国石油', 17.00, 0, 1.03, 0),
(600001, '600001', u'中国石化', 25.42, 0, -100.11, 0),
(600002, '600002', u'建设银行', 1.06, 0, 32.2, 0)]
conn = dbhelper.get_conn(DB_FILE_PATH)
dbhelper.save(conn, save_sql, data)
def create_table():
print('TEST: Create database table...')
create_table_sql = '''CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`gender` varchar(4) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(200) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
)'''
conn = dbhelper.get_conn(DB_FILE_PATH)
dbhelper.create_table(conn, create_table_sql)
def create_table():
print(LOG + 'Create database table...')
create_table_sql = '''CREATE TABLE 'roe_revenue' (
`id` int(11) NOT NULL,
`code` varchar(6) NOT NULL,
`name` varchar(24) NOT NULL,
`roe` REAL(32) DEFAULT NULL,
'roe_sort' int(16) DEFAULT NULL,
`revenue` REAL(32) DEFAULT NULL,
`revenue_sort` int(16) DEFAULT NULL,
PRIMARY KEY (`id`)
)'''
conn = dbhelper.get_conn(DB_FILE_PATH)
dbhelper.create_table(conn, create_table_sql)
def delete_data():
print('TEST: Delete data...')
delete_sql = 'DELETE FROM student WHERE NAME = ? AND ID = ? '
data = [('HongtenAA', 1), ('HongtenCC', 3)]
conn = dbhelper.get_conn(DB_FILE_PATH)
dbhelper.delete(conn, delete_sql, data)
def fetchone():
print('TEST: Query single data...')
fetchone_sql = 'SELECT * FROM student WHERE ID = ? '
data = 1
conn = dbhelper.get_conn(DB_FILE_PATH)
dbhelper.fetchone(conn, fetchone_sql, data)
def fetchall():
print('TEST: Query all data...')
fetchall_sql = '''SELECT * FROM student'''
conn = dbhelper.get_conn(DB_FILE_PATH)
dbhelper.fetchall(conn, fetchall_sql)
def drop_table():
print('TEST: Delete database table...')
conn = dbhelper.get_conn(DB_FILE_PATH)
dbhelper.drop_table(conn, TABLE_NAME)