


Implementation example of Python reading TXT file data and storing it in the built-in database (SQLite3)
This article mainly introduces the method of Python to read TXT file data and store it into the built-in database SQLite3. It involves Python's reading of txt files and the creation, insertion, query and other related operation skills of sqlite3 database. Friends who need it can Refer to the following
The example of this article describes the method of reading TXT file data in Python and storing it in the built-in database SQLite3. Share it with everyone for your reference, the details are as follows:
When the TXT file is too large and the computer memory is not enough, we can choose to read the TXT file line by line and store it into Python's built-in lightweight splite database. This can speed up the reading of data. When we need to read data repeatedly, the time savings brought by this speed increase are very considerable. For example, when we are training data, we need to iterate 100,000 times, that is, Reading from a file 100,000 times, even if it only speeds up by 0.1 seconds each time, can save several hours of time.
#创建数据库并把txt文件的数据存进数据库 import sqlite3 #导入sqlite3 cx = sqlite3.connect('./train.db') #创建数据库,如果数据库已经存在,则链接数据库;如果数据库不存在,则先创建数据库,再链接该数据库。 cu = cx.cursor() #定义一个游标,以便获得查询对象。 cu.execute('create table if not exists train4 (id integer primary key,name text)') #创建表 fr = open('data_sample.txt') #打开要读取的txt文件 i = 0 for line in fr.readlines(): #将数据按行插入数据库的表train4中。 cu.execute('insert into train4 values(?,?)',(i,line)) i +=1 cu.close() #关闭游标 cx.commit() #事务提交 cx.close() #关闭数据库
Query data:
cu.execute('select * from train4 where id = ?',(i,)) #i代表你要读取表train4中某一行的数据 result = cu.fetchall()
Note: If it has been closed previously If the database is closed, you must reopen the database and create a cursor when querying. Pay attention to this.
The complete query program is like this:
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()
Another: Here is another SQLite3 data operation for everyone Class for your reference:
import sqlite3 # *************************************************** # * # * Description: Python操作SQLite3数据库辅助类(查询构造器) # * 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构造器辅助类的条件语句部分 例如: DataCondition(("=", "AND"), id = 26) DataCondition(("=", "AND"), True, id = 26) """ def __init__(self, operator = ("=", "AND"), ingroup = True, **kwargs): """ 构造方法 参数: operator 操作符,分为(表达式操作符, 条件运算符) ingroup 是否分组,如果分组,将以括号包含 kwargs 键值元组,包含数据库表的列名以及值 注意这里的等于号不等于实际生成SQL语句符号 实际符号是由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 数据查询辅助类 """ def __init__(self, filename): """ 构造方法 参数: filename 为SQLite3 数据库文件名 """ self.file_name = filename def open(self): """ 打开数据库并设置游标 """ self.connection = sqlite3.connect(self.file_name) self.cursor = self.connection.cursor() return self def close(self): """ 关闭数据库,注意若不显式调用此方法, 在类被回收时也会尝试调用 """ if hasattr(self, "connection") and self.connection: self.connection.close() def __del__(self): """ 析构方法,做一些清理工作 """ self.close() def commit(self): """ 提交事务 SELECT语句不需要此操作,默认的execute方法的 commit_at_once设为True会隐式调用此方法, 否则就需要显示调用本方法。 """ self.connection.commit() def execute(self, sql = None, commit_at_once = True): """ 执行SQL语句 参数: sql 要执行的SQL语句,若为None,则调用构造器生成的SQL语句。 commit_at_once 是否立即提交事务,如果不立即提交, 对于非查询操作,则需要调用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): """ 设置查询的表,多个表名用逗号分隔 """ 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
The above is the detailed content of Implementation example of Python reading TXT file data and storing it in the built-in database (SQLite3). For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

PHP is mainly procedural programming, but also supports object-oriented programming (OOP); Python supports a variety of paradigms, including OOP, functional and procedural programming. PHP is suitable for web development, and Python is suitable for a variety of applications such as data analysis and machine learning.

PHP is suitable for web development and rapid prototyping, and Python is suitable for data science and machine learning. 1.PHP is used for dynamic web development, with simple syntax and suitable for rapid development. 2. Python has concise syntax, is suitable for multiple fields, and has a strong library ecosystem.

Python is more suitable for beginners, with a smooth learning curve and concise syntax; JavaScript is suitable for front-end development, with a steep learning curve and flexible syntax. 1. Python syntax is intuitive and suitable for data science and back-end development. 2. JavaScript is flexible and widely used in front-end and server-side programming.

VS Code can be used to write Python and provides many features that make it an ideal tool for developing Python applications. It allows users to: install Python extensions to get functions such as code completion, syntax highlighting, and debugging. Use the debugger to track code step by step, find and fix errors. Integrate Git for version control. Use code formatting tools to maintain code consistency. Use the Linting tool to spot potential problems ahead of time.

PHP originated in 1994 and was developed by RasmusLerdorf. It was originally used to track website visitors and gradually evolved into a server-side scripting language and was widely used in web development. Python was developed by Guidovan Rossum in the late 1980s and was first released in 1991. It emphasizes code readability and simplicity, and is suitable for scientific computing, data analysis and other fields.

VS Code can run on Windows 8, but the experience may not be great. First make sure the system has been updated to the latest patch, then download the VS Code installation package that matches the system architecture and install it as prompted. After installation, be aware that some extensions may be incompatible with Windows 8 and need to look for alternative extensions or use newer Windows systems in a virtual machine. Install the necessary extensions to check whether they work properly. Although VS Code is feasible on Windows 8, it is recommended to upgrade to a newer Windows system for a better development experience and security.

In VS Code, you can run the program in the terminal through the following steps: Prepare the code and open the integrated terminal to ensure that the code directory is consistent with the terminal working directory. Select the run command according to the programming language (such as Python's python your_file_name.py) to check whether it runs successfully and resolve errors. Use the debugger to improve debugging efficiency.

VS Code extensions pose malicious risks, such as hiding malicious code, exploiting vulnerabilities, and masturbating as legitimate extensions. Methods to identify malicious extensions include: checking publishers, reading comments, checking code, and installing with caution. Security measures also include: security awareness, good habits, regular updates and antivirus software.
