Python reads txt file data and deposits it into an implementation instance of the built-in database (SQLite3)

This article mainly introduces Python implementation of read TXT file data into the built-in database SQLite3 method, involving Python read txt file and sqlite3 database creation, insertion, query and other related operations skills, the need for friends can refer to the next

In this paper, we describe how Python implements the SQLite3 of reading TXT file data into the built-in database. Share to everyone for your reference, as follows:

When the TXT file is too large and the computer memory is not enough, we can choose to read the TXT file by line and store it in the Python built-in lightweight Splite database, which can speed up the reading speed of the data, when we need to read the data repeatedly, the time savings caused by this speed is very considerable, For example, when we are training data, we have to iterate 100,000 times, that is, to read from a file 100,000 times, even if we only speed up 0.1 seconds at a time, we can save a few hours.

#创建数据库并把txt文件的数据存进数据库import sqlite3      #导入sqlite3cx = Sqlite3.connect ('./train.db ')  #创建数据库, if the database already exists, link the database If the database does not exist, the database is created first and then linked to the database. CU = Cx.cursor ()           #定义一个游标 to get the query object. Cu.execute (' CREATE table if not exists train4 (ID integer primary key,name text) ')  #创建表fr = open (' Data_sample.txt ') 
   #打开要读取的txt文件i = 0for in Fr.readlines ():    #将数据按行插入数据库的表train4中.  Cu.execute (' INSERT into TRAIN4 values (?,?) ', (i,line))  i +=1cu.close ()   #关闭游标cx. Commit ()   # Transaction Submission Cx.close ()   #关闭数据库

Query data:

Cu.execute (' select * from train4 where id =? ', (I,)) #i代表你要读取表train4中某一行的数据result = Cu.fetchall ()

Note: If the database has been closed earlier, you will reopen the database at query time and create cursors. Pay attention to this point.

The complete query program is this:

Import sqlite3cx = Sqlite3.connect ('./train.db ') cu = Cx.cursor () for I in range (5):  cu.execute (' select * FROM TRAIN4 WH Ere id =? ', (I,))  result = Cu.fetchall ()  cx.commit () cu.close () Cx.close ()

another: here again for everyone with a SQLite3 data operation class for everyone to refer to the use:

Import sqlite3# ***************************************************# *# * Description:python Operation SQLite3 Database helper class (Query builder) # * 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 fieldsdef _concat_keys (keys): Return "[" + "],[". Join (keys) + "]" def _concat_va Lues (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.ap Pend ("[" + Key + "]") if Unit_operator:compiled.append (unit_operator) compiled.append (value) Compiled.app    End (Group_operator) Compiled.pop () # Pop last group_operator return "". Join (compiled) class Datacondition (object): "" This class is used to manipulate the conditional statements section of the SQL constructor helper class, for example:    Datacondition ("=", "and"), id = +) datacondition (("=", "and"), True, id = +) "" "Def __init__ (self, operator = ("=", "and"), Ingroup = True, **kwargs): "" "Constructor method parameter: operator operator, divided into (expression operator, conditional operator) Ingroup is      No grouping, if grouped, will enclose the Kwargs key value tuple in parentheses, including the column name of the database table and the value note that the equals sign here is not equal to the actual generated SQL statement symbol the actual symbol is controlled by operator[0] For example: Datacondition (("=", "and"), id = +) (id=26) datacondition ((">", "OR"), id = +, age = +) (id& Gt;26 or age>35) datacondition (("Like", "or"), False, name = "John", company = "Google") name "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.ingr     Oup:return "(" + result + ")" Return result def __str__ (self): return self.__unicode__ () def toString (self): Return self.__unicodE__ () class Datahelper (object): "" "SQLite3 Data Query Helper Class" "Def __init__ (self, filename):" "" Constructor method parameter: Filen AME is the SQLite3 database file name "" "Self.file_name = filename def open (self):" "opens the database and sets the cursor" "" Self.connection = Sqlite3.connect (self.file_name) self.cursor = Self.connection.cursor () return self def close (self): "" "Off Close the database, note that if you do not explicitly call this method, you will also try to call "" If hasattr (self, "connection") and Self.connection:self.connection.clo when the class is recycled Se () def __del__ (self): "" "destructor method, do some cleanup work" "" Self.close () def commit (self): "" COMMIT TRANSACTION SELECT statement    This action is not required, and the default Execute method, Commit_at_once, is set to true to call this method implicitly, otherwise it needs to be displayed to call this method.        "" "Self.connection.commit () def execute (self, sql = None, commit_at_once = True):" "Executes the SQL statement parameter:        The SQL statement that SQL executes, or none, invokes the SQL statement generated by the constructor.    Commit_at_once whether the transaction is committed immediately, and if it is not committed immediately, a commit explicit commit is required for non-query operations. "" "if not Sql:sql = Self.sql self.cursor.execute (SQL) if CommIt_at_once:self.commit () def fetchone (self, sql = None): "" Takes a Record "" "Self.execute (SQL, False) re Turn Self.cursor.fetchone () def fetchall (self, sql = None): "" "Fetch All Records" "" Self.execute (SQL, False) retur    N 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): "" "sets the table of the query, with multiple table names separated by commas" "" self.tables = args sel F.tables_snippet = Self.__concat_keys (self.tables) return self def __wrap_value (self, value): Return _wrap_value (VA LUE) def __wrap_values (self, Values): Return _wrap_values (values) def __wrap_fields (Self, Fields): Return _wrap_fi ELDs (Fields) def __where (self): # Self.condition_snippet if hasattr (self, "Condition_snippet"): Self.where_sni Ppet = "WHERE" + Self.condition_snippet def __select (self): template = "Select% (keys) s from% (tables) S" Body_sni Ppet_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_fie lds (Kwargs) Self.__build () return self def update (self, **kwargs): Self.current_token = "Update" Self.body_fi  ELDs = 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 in Where (self, *conditions): conditions = List (map (str, conditions)) Self.condition_snippet = "and". Join (conditions) Self.__where () if has attr (self, "Where_snippet"): Self.sql + = Self.where_snippet return self