#創(chuàng)建數(shù)據(jù)庫(kù)并把txt文件的數(shù)據(jù)存進(jìn)數(shù)據(jù)庫(kù)
import sqlite3      #導(dǎo)入sqlite3
cx = sqlite3.connect('./train.db')  #創(chuàng)建數(shù)據(jù)庫(kù)，如果數(shù)據(jù)庫(kù)已經(jīng)存在，則鏈接數(shù)據(jù)庫(kù)；如果數(shù)據(jù)庫(kù)不存在，則先創(chuàng)建數(shù)據(jù)庫(kù)，再鏈接該數(shù)據(jù)庫(kù)。
cu = cx.cursor()           #定義一個(gè)游標(biāo)，以便獲得查詢對(duì)象。
cu.execute('create table if not exists train4 (id integer primary key,name text)')  #創(chuàng)建表
fr = open('data_sample.txt')    #打開(kāi)要讀取的txt文件
i = 0
for line in fr.readlines():    #將數(shù)據(jù)按行插入數(shù)據(jù)庫(kù)的表train4中。
  cu.execute('insert into train4 values(?,?)',(i,line))
  i +=1
cu.close()   #關(guān)閉游標(biāo)
cx.commit()   #事務(wù)提交
cx.close()   #關(guān)閉數(shù)據(jù)庫(kù)

cu.execute('select * from train4 where id = ?',(i,)) #i代表你要讀取表train4中某一行的數(shù)據(jù)
result = cu.fetchall()

import sqlite3
cx = sqlite3.connect('./train.db')
cu = cx.cursor()
for i in range(5):
  cu.execute('select * from train4 where id = ?',(i,))
  result = cu.fetchall()
  cx.commit()
cu.close()
cx.close()

import sqlite3
# ***************************************************
# *
# * Description: Python操作SQLite3數(shù)據(jù)庫(kù)輔助類(查詢構(gòu)造器)
# * Author: wangye
# *
# ***************************************************
def _wrap_value(value):
  return repr(value)
def _wrap_values(values):
  return list(map(_wrap_value, values))
def _wrap_fields(fields):
  for key,value in fields.items():
    fields[key] = _wrap_value(value)
  return fields
def _concat_keys(keys):
  return "[" + "],[".join(keys) + "]"
def _concat_values(values):
  return ",".join(values)
def _concat_fields(fields, operator = (None, ",")):
  if operator:
    unit_operator, group_operator = operator
  # fields = _wrap_fields(fields)
  compiled = []
  for key,value in fields.items():
    compiled.append("[" + key + "]")
    if unit_operator:
      compiled.append(unit_operator)
      compiled.append(value)
    compiled.append(group_operator)
  compiled.pop() # pop last group_operator
  return " ".join(compiled)
class DataCondition(object):
  """
    本類用于操作SQL構(gòu)造器輔助類的條件語(yǔ)句部分
    例如:
    DataCondition(("=", "AND"), id = 26)
    DataCondition(("=", "AND"), True, id = 26)
  """
  def __init__(self, operator = ("=", "AND"), ingroup = True, **kwargs):
    """
      構(gòu)造方法
      參數(shù):
        operator 操作符，分為(表達(dá)式操作符, 條件運(yùn)算符)
        ingroup 是否分組，如果分組，將以括號(hào)包含
        kwargs  鍵值元組，包含數(shù)據(jù)庫(kù)表的列名以及值
             注意這里的等于號(hào)不等于實(shí)際生成SQL語(yǔ)句符號(hào)
             實(shí)際符號(hào)是由operator[0]控制的
      例如:
      DataCondition(("=", "AND"), id = 26)
      (id=26)
      DataCondition((">", "OR"), id = 26, age = 35)
      (id>26 OR age>35)
      DataCondition(("LIKE", "OR"), False, name = "John", company = "Google")
      name LIKE 'John' OR company LIKE "Google"
    """
    self.ingroup = ingroup
    self.fields = kwargs
    self.operator = operator
  def __unicode__(self):
    self.fields = _wrap_fields(self.fields)
    result = _concat_fields(self.fields, self.operator)
    if self.ingroup:
      return "(" + result + ")"
    return result
  def __str__(self):
    return self.__unicode__()
  def toString(self):
    return self.__unicode__()
class DataHelper(object):
  """
    SQLite3 數(shù)據(jù)查詢輔助類
  """
  def __init__(self, filename):
    """
      構(gòu)造方法
      參數(shù): filename 為SQLite3 數(shù)據(jù)庫(kù)文件名
    """
    self.file_name = filename
  def open(self):
    """
      打開(kāi)數(shù)據(jù)庫(kù)并設(shè)置游標(biāo)
    """
    self.connection = sqlite3.connect(self.file_name)
    self.cursor = self.connection.cursor()
    return self
  def close(self):
    """
      關(guān)閉數(shù)據(jù)庫(kù)，注意若不顯式調(diào)用此方法，
      在類被回收時(shí)也會(huì)嘗試調(diào)用
    """
    if hasattr(self, "connection") and self.connection:
      self.connection.close()
  def __del__(self):
    """
      析構(gòu)方法，做一些清理工作
    """
    self.close()
  def commit(self):
    """
      提交事務(wù)
      SELECT語(yǔ)句不需要此操作，默認(rèn)的execute方法的
      commit_at_once設(shè)為True會(huì)隱式調(diào)用此方法，
      否則就需要顯示調(diào)用本方法。
    """
    self.connection.commit()
  def execute(self, sql = None, commit_at_once = True):
    """
      執(zhí)行SQL語(yǔ)句
      參數(shù):
        sql 要執(zhí)行的SQL語(yǔ)句，若為None，則調(diào)用構(gòu)造器生成的SQL語(yǔ)句。
        commit_at_once 是否立即提交事務(wù)，如果不立即提交，
        對(duì)于非查詢操作，則需要調(diào)用commit顯式提交。
    """
    if not sql:
      sql = self.sql
    self.cursor.execute(sql)
    if commit_at_once:
      self.commit()
  def fetchone(self, sql = None):
    """
      取一條記錄
    """
    self.execute(sql, False)
    return self.cursor.fetchone()
  def fetchall(self, sql = None):
    """
      取所有記錄
    """
    self.execute(sql, False)
    return self.cursor.fetchall()
  def __concat_keys(self, keys):
    return _concat_keys(keys)
  def __concat_values(self, values):
    return _concat_values(values)
  def table(self, *args):
    """
      設(shè)置查詢的表，多個(gè)表名用逗號(hào)分隔
    """
    self.tables = args
    self.tables_snippet = self.__concat_keys(self.tables)
    return self
  def __wrap_value(self, value):
    return _wrap_value(value)
  def __wrap_values(self, values):
    return _wrap_values(values)
  def __wrap_fields(self, fields):
    return _wrap_fields(fields)
  def __where(self):
    # self.condition_snippet
    if hasattr(self, "condition_snippet"):
      self.where_snippet = " WHERE " + self.condition_snippet
  def __select(self):
    template = "SELECT %(keys)s FROM %(tables)s"
    body_snippet_fields = {
      "tables" : self.tables_snippet,
      "keys" : self.__concat_keys(self.body_keys), 
    }
    self.sql = template % body_snippet_fields
  def __insert(self):
    template = "INSERT INTO %(tables)s (%(keys)s) VALUES (%(values)s)"
    body_snippet_fields = {
      "tables" : self.tables_snippet,
      "keys" : self.__concat_keys(list(self.body_fields.keys())),
      "values" : self.__concat_values(list(self.body_fields.values()))
    }
    self.sql = template % body_snippet_fields
  def __update(self):
    template = "UPDATE %(tables)s SET %(fields)s"
    body_snippet_fields = {
      "tables" : self.tables_snippet,
      "fields" : _concat_fields(self.body_fields, ("=",","))
    }
    self.sql = template % body_snippet_fields
  def __delete(self):
    template = "DELETE FROM %(tables)s"
    body_snippet_fields = {
      "tables" : self.tables_snippet
    }
    self.sql = template % body_snippet_fields
  def __build(self):
    {
      "SELECT": self.__select,
      "INSERT": self.__insert,
      "UPDATE": self.__update,
      "DELETE": self.__delete
    }[self.current_token]()
  def __unicode__(self):
    return self.sql
  def __str__(self):
    return self.__unicode__()
  def select(self, *args):
    self.current_token = "SELECT"
    self.body_keys = args
    self.__build()
    return self
  def insert(self, **kwargs):
    self.current_token = "INSERT"
    self.body_fields = self.__wrap_fields(kwargs)
    self.__build()
    return self
  def update(self, **kwargs):
    self.current_token = "UPDATE"
    self.body_fields = self.__wrap_fields(kwargs)
    self.__build()
    return self
  def delete(self, *conditions):
    self.current_token = "DELETE"
    self.__build()
    #if *conditions:
    self.where(*conditions)
    return self
  def where(self, *conditions):
    conditions = list(map(str, conditions))
    self.condition_snippet = " AND ".join(conditions)
    self.__where()
    if hasattr(self, "where_snippet"):
      self.sql += self.where_snippet
    return self