python + sqlite

tim_spac 2008-05-20 05:27:24

#!python

#-*- coding:GBK -*-

# filename: txt2sqlite.py

# author: Tim Wang @ 2008-5-15



import sqlite3

import types

import string



con = sqlite3.connect("../DAT/gnet_center.sqlite")

cur = con.cursor()



# Clear table

cur.execute("delete from gnet_ec_users")



for line in open("../DAT/gnet_ec_users.txt","r").readlines()[:-1]:

    # 字段拆分、汉字解码

    items = line[:-1].decode('utf-8').split("\t")

    # \N ==> None

    for i in range(len(items)):

        if (items[i]=='\\N'): items[i] = None

    # 数据入库

    cur.execute("""insert into gnet_ec_users 

        (user_id,site_id,service_account,user_name,display_name,user_company,user_department,create_date,invalid_time)

        VALUES (?,?,?,?,?,?,?,?,?) """, 

        (items[0], items[1], items[2], items[3], items[4], items[5], items[6], items[7], items[8]))



# 目视入库结果

for row in cur.execute('select * from gnet_ec_users'):

    for i in range(len(row)):

        if (i == 4 and row[i] != None):

            print row[i].encode('gbk'),

        else:

            print row[i],

    print 



cur.close()

2000070 1 None est4@126.com est4@126.com 11 2008-03-06 16:15:31 None None
2000071 1 None est5@126.com est4@126.com 11 2008-03-06 16:18:09 None None
2000235 1 1 admin eee sdsd sdsd 2008-03-02 12:31:35 2008-03-31 12:31:38 None
2000236 3 1 jiawu.liu@gnetis.com jiawu 111 1 2008-03-03 13:24:15 None None
2000237 1 None bin.cheng@gnetis.com chengbin 12 2008-03-03 13:48:39 None None
2000238 2 1 admin 管理员 jj jj 2008-03-02 14:22:24 2008-03-31 14:22:29 None
2000239 2 None nan.zhang@gnetis.com nan 1 2008-03-03 14:33:35 None None
2000240 1 1 1 ddd 99 aaa 2008-03-03 15:45:57 None None

但是库中没存下数据。

#!python

#-*- coding:GBK -*-

# filename: txt2sqlite.py

# author: Tim Wang @ 2008-5-15



import sqlite3

import types

import string



con = sqlite3.connect("../DAT/gnet_center.sqlite")

cur = con.cursor()



# Clear table

cur.execute("delete from gnet_ec_users")



for line in open("../DAT/gnet_ec_users.txt","r").readlines()[:-1]:

    # 字段拆分、汉字解码

    items = line[:-1].decode('utf-8').split("\t")

    # \N ==> None

    for i in range(len(items)):

        if (items[i]=='\\N'): items[i] = None

    # 数据入库

    cur.execute("""insert into gnet_ec_users 

        (user_id,site_id,service_account,user_name,display_name,user_company,user_department,create_date,invalid_time)

        VALUES (?,?,?,?,?,?,?,?,?) """, 

        (items[0], items[1], items[2], items[3], items[4], items[5], items[6], items[7], items[8]))



cur.close()

# 先关闭后，再打开

con = sqlite3.connect("../DAT/gnet_center.sqlite")

cur = con.cursor()



# 目视入库结果

for row in cur.execute('select * from gnet_ec_users'):

    for i in range(len(row)):

        if (i == 4 and row[i] != None):

            print row[i].encode('gbk'),

        else:

            print row[i],

    print 



cur.close()