python3.8 save stock list to db

# coding=utf-8
import os
import sys
sys.path.append('..')
#sys.path.append('../code_pub')
import csv
import code_pub.api_sqlite

server_root_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

global stocks
create_teble_sql='''
             create table  if not exists stock(
                [id] integer primary key autoincrement,
                [code] varchar(8),
                 [name] varchar(20),                
                 [date] datetime
                )
             '''
def init_db_sqlite():
    global stocks
    stocks = code_pub.api_sqlite.c_create_db(db_name="stock_code")
    stocks.conn_db()
    stocks.create_table(create_teble_sql)





def deal_data(code1, name1, date_start1):
    code = code1.strip()
    print(type(code))
    name = name1.strip()
    date = date_start1.strip()
    #share_total = share_total1.strip()
    #share_outstanding = share_outstanding1.strip()
    sql = 'select * from stock where code = "%s" ' % (code)
    print(sql)
    info = stocks.search_info(search_sql=sql)
    cnt = 0
    for i in info:
        print(i,type(i[0]),i[0])
        name_find = i[2]
        cnt+=1
    #l = info
    #cnt = len(list(info))
    print(type(info), code)
    print(len(list(info)), type(len(list(info))))
    #for i in info:
    #    print(i,type(i[0]),i[0])
    #cnt = len(list(info))
    print (type(cnt),cnt,"cnt", len(list(info)))
    if (1 == cnt):
        print("------uptime name-------")
        #for i in l:
        #    print(i)
        #l = stocks.find_one({"code": code})
        #for i in l:
         #   print i.get("name")
        #    print i.get("code")
        #name_find = l.get("name")
        #for i in l:
        #    name_find = i[1]
        #    print(name_find, type(name_find), type(i[1]))
            # print(i,type(i[0]),i[0])
        #name = "adc3"
        #str.decode("g18030") == str.decode("utf8")
        #if (0 != cmp(name, name_find)):
        print(type(name),name, type(name_find), name_find)
        if (name != name_find):
        #if (name.decode("utf8") != name_find.decode("utf8")):
            sql = 'update stock set name = "%s" where code = "%s" ' % (name, code)
            print(sql)
            stocks.update_info(update_sql=sql)
            #stocks.update({"code":code}, {"$set":{"name":name, "date_start":date_start, "share_total":share_total ,"share_outstanding":share_outstanding}})
            print ("------update name-------", name, name_find)
            #stocks.update({"code": "123456"}, {$set:{"name": "babc123"}})
    elif (0 == cnt):
        print ("------insert name-------")
        #stocks.insert({"code": code, "name": name, "date_start":date_start, "share_total":share_total ,"share_outstanding":share_outstanding})
        sql = 'insert into stock (code, name, date) values ("%s", "%s", "%s" )' % (code, name, date)
        print(sql)
        stocks.update_info(update_sql=sql)
    else:
        print ("------remove name-------", cnt)
        #stocks.remove({"code": code})
        #stocks.insert({"code": code, "name":name, "date_start":date_start, "share_total":share_total ,"share_outstanding":share_outstanding})
        sql = 'delete from stock where code = "%s"' % (code)
        print(sql)
        stocks.update_info(update_sql= sql)
        sql = 'insert into stock (code, name, date) values ("%s", "%s", "%s" )' % (code, name, date)
        print(sql)
        stocks.update_info(update_sql= sql)

def get_stock_list_sh():
    list_name = ['sha.csv', 'shb.csv', 'shk.csv']
    for name in list_name:
        try:
            name2 = '%s/file_stock/%s' % (server_root_path, name)
            print(name2)
            csvfile = open(name2, 'rt')
            reader = csv.reader(csvfile)

            for line in reader:
                try:
                    # 忽略第一行
                    if reader.line_num == 1:
                        continue
                    #list_code.append(line[0])
                    # print(' said: ', '')
                    i = 2
                    print (line[i], line[i+1], line[i+2])
                    deal_data(line[i], line[i+1], line[i+2])

                except ValueError:
                    pass
            print ("name --",name, locals())
            # csvfile.close()
        except IOError as err:  # 使用as将异常对象，并将其赋值给一个标识符
            print('File Error:' + str(err))  # ‘+’用于字符串直接的连接

        finally:
            if 'csvfile' in locals():
                csvfile.close()
                print ("close")

def get_stock_list_sz():
    list_name = ['sza.csv']
    for name in list_name:
        try:
            name2 = '%s/file_stock/%s' % (server_root_path, name)
            print(name2)
            csvfile = open(name2, 'rt')
            reader = csv.reader(csvfile)

            for line in reader:
                try:
                    # 忽略第一行
                    if reader.line_num == 1:
                        continue
                    #list_code.append(line[0])
                    # print(' said: ', '')
                    i = 5
                    j = 5
                    #print line
                    print ("a---",line[i], line[i + 1], line[i + 2], line[i + 3], line[i + 4])
                    print ("b---",line[i + j], line[i + 1+ j], line[i + 2+ j], line[i + 3+ j], line[i + 4+ j])
                    #'''
                    if ('' != line[i]):
                        deal_data(line[i], line[i + 1], line[i + 2])
                    #print line[i + j]
                    '''
                    if ('' != line[i + j]):
                        print ("-------------------sz---------b-----------------",line[i + j])
                        deal_data(line[i + j], line[i + 1+ j], line[i + 2+ j])
                        '''
#'''
                except ValueError:
                    pass
            print ("anme2 ---", name, locals())
            # csvfile.close()
        except IOError as err:  # 使用as将异常对象，并将其赋值给一个标识符
            print('File Error:' + str(err))  # ‘+’用于字符串直接的连接

        finally:
            if 'csvfile' in locals():
                csvfile.close()
                print ("close")
def get_stock_list_szb():
    list_name = ['szb.csv']
    for name in list_name:
        try:
            name2 = '%s/file_stock/%s' % (server_root_path, name)
            print(name2)
            csvfile = open(name2, 'rt')
            reader = csv.reader(csvfile)

            for line in reader:
                try:
                    # 忽略第一行
                    if reader.line_num == 1:
                        continue
                    #list_code.append(line[0])
                    # print(' said: ', '')
                    i = 10
                    j = 5
                    #print line
                    print ("a---",line[i], line[i + 1], line[i + 2], line[i + 3], line[i + 4])
                    print ("b---",line[i + j], line[i + 1+ j], line[i + 2+ j], line[i + 3+ j], line[i + 4+ j])
                    #'''
                    if ('' != line[i]):
                        deal_data(line[i], line[i + 1], line[i + 2])
                    #print line[i + j]
                    '''
                    if ('' != line[i + j]):
                        print ("-------------------sz---------b-----------------",line[i + j])
                        deal_data(line[i + j], line[i + 1+ j], line[i + 2+ j])
                        '''
#'''
                except ValueError:
                    pass
            print ("anme2 ---", name, locals())
            # csvfile.close()
        except IOError as err:  # 使用as将异常对象，并将其赋值给一个标识符
            print('File Error:' + str(err))  # ‘+’用于字符串直接的连接

        finally:
            if 'csvfile' in locals():
                csvfile.close()
                print ("close")
if __name__ == '__main__':
    init_db_sqlite()
    get_stock_list_sh()
    get_stock_list_sz()
    get_stock_list_szb()
    stocks.close_db()```