一个.txt到可修改的android数据库

I have a question for you guys. I have been working on a project application that in one part uses an SQLite database loaded from a txt file (it has about 100k-200k rows of 5 strings separated by the ^ sign).

我有个问你们。我一直在研究一个项目应用程序,它在一个部分中使用从txt文件加载的SQLite数据库(它有大约100k-200k行的5个字符串,由^符号分隔)。

Now my question is, since this is my first time working with databases, how does .txt import for modifiable databases work? If I understand right, it pulls all data from the txt file once and creates a database that it keeps to work on, so when I modify the database I modify the newly created one and not the txt? Does the code try to pull info again from the txt whenever the app loads, and would loading 200k 10char words every time be too much? :)

现在我的问题是,因为这是我第一次使用数据库,可修改数据库的.txt导入如何工作?如果我理解正确,它会从txt文件中提取一次所有数据并创建一个它继续工作的数据库,所以当我修改数据库时,我修改了新创建的数据而不是txt?每当应用加载时,代码是否会尝试从txt再次提取信息,并且每次加载200k 10char的单词会是多少? :)

The database consists of music bands in this format: name/genre/popular[yes/no]/selected
The selected column is the only one being modified by the user (and the app for that matter). If I use the regular approach to databases with added implementation from a txt file will the selected column reset every time (do not want that)?

该数据库由以下格式的音乐乐队组成:名称/流派/流行[是/否] /已选择所选列是用户(以及应用程序)修改的唯一列。如果我使用常规方法对数据库添加txt文件的实现,那么每次都会重置所选列(不想这样)?

1 个解决方案

#1

Don't distribute your app with a huge txt-file and import it on the users device. This takes time and is annoying.

不要使用巨大的txt文件分发您的应用程序并将其导入用户设备。这需要时间并且令人讨厌。

Rather distribute your app with a pre-populated database and copy it over from the res-folder. You can use android-sqlite-asset-helper to automate this.

而是使用预先填充的数据库分发您的应用程序并将其从res文件夹复制。您可以使用android-sqlite-asset-helper自动执行此操作。

Also, yes. The Database is always stored on the internal memory and you can't access it on a non-rooted device (unless you're using the AVD).

还有,是的。数据库始终存储在内部存储器中,您无法在非root设备*问它(除非您使用的是AVD)。

To import your txt-contents into a database, create a script or something that parses the contents and executes the corresponding SQL-queries. Again, your App should ship with the database, not the raw-file!

要将txt内容导入数据库,请创建一个脚本或解析内容并执行相应SQL查询的内容。同样,您的应用程序应该附带数据库,而不是原始文件!

I was a little bored and hacked together a short Python-Script to read all entries from your txt-file and insert them into a SQLite Database:

我有点无聊并且将一个简短的Python脚本拼凑在一起以读取txt文件中的所有条目并将它们插入到SQLite数据库中:

import sqlite3
import re

counter = 0;
pattern = re.compile('^([^\^]+)\^([\w\s]+)\^(yes|no)\^\w+$');
conn = sqlite3.connect("imported.db");
cursor = conn.cursor();
# Create the Table:
conn.execute('''
    CREATE TABLE Bands (
        name TEXT,
        genre TEXT,
        popular INTEGER,
        selected INTEGER
    );''');

# Now, insert:
with open('bands.txt', 'r') as f:
    for line in f:
    match = pattern.search(line);
    if match:
            cursor.execute('''
            INSERT INTO Bands (name, genre, popular, selected)
            VALUES (?,?,?,0)''',
            (
                match.group(1), match.group(2),
                (1 if match.group(3) == 'yes' else 0)
            )
        );
        counter+=1;



conn.commit();
conn.close();
print "Imported ", counter, " bands!";

This will assume that the txt-file is named bands.txt, each value is separated by a / and each entry will be on it's own line. The resulting database-file is imported.db.

这将假设txt文件名为bands.txt,每个值用/分隔,每个条目都在它自己的行上。生成的数据库文件是imported.db。

Also, I use INTEGER for all True|False-fields (popular, selected). These will then hold a 0 for false and a 1 for true.

另外,我对所有True | False字段使用INTEGER(流行,选择)。然后,这些将保持0表示假,1表示真。

Last but not least, the RegEx only allows "yes" and "no" for the popular-value.

最后但并非最不重要的是,RegEx仅允许流行值为“是”和“否”。

#1