CODEWITHSUNDEEP
pythonsqlite
kmg1
kmg1

Reputation: 33

Storing a text file into SQLite3 database using python

I have done some operations with a file using python. Now all i have to is to create one table with two columns...one is for msgid and another is for msgstr...all msgids should be stored in msgid column and all msgstrs should be stored in msgstr column..

I am very new to the programming world. Kindly help me. I have pasted what i had done below:

fn='ru.po'
f=open(fn)
output=[]
for line in f:
    if not '#' in line:
        output.append(line)
f.close()
f=open(fn,'w')
f.writelines(output)
f.close

Upvotes: 0

Views: 1828

Answers (1)

mhawke
mhawke

Reputation: 87124

There are 2 parts to this:

  1. Extracting the msgid and corresponding msgstr values from the .po file.
  2. Inserting the msgid and msgstr into a table in the SQLite database.

For part 1, I suggest using the babel module. You can install it with

pip install babel

Use the babel.messages.pofile.read_po() function to read the .po file. This will return a catalog on which you can iterate over all of the messages parsed from the file:

from babel.messages.pofile import read_po

with open('ru.po') as po_file:
    cat = read_po(po_file)

for message in cat:
    if message.id:
        print '{!r} -> {!r}'.format(message.id, message.string)

For part 2:

import sqlite3

conn = sqlite3.connect('catalog.db')
cursor = conn.cursor()
result = cursor.execute('CREATE TABLE ru (msgid TEXT, msgstr TEXT)')

# bulk insert the messages
messages = [(msg.id, msg.string) for msg in cat if msg.id]
result = cursor.executemany('INSERT INTO ru (msgid, msgstr) VALUES (?, ?)',  messages)
assert(result.rowcount == len(messages))
conn.commit()

result = cursor.execute("SELECT * from ru where msgid = '11 inches/sec.'")
msgid, msgstr = result.fetchone()
# .encode('utf8') can be removed for Python 3
print '"{}" translates to "{}"'.format(msgid, msgstr.encode('utf8'))

msgid = 'A Samba password is required to export printer drivers'
result = cursor.execute("SELECT * from ru where msgid = ?", (msgid,))
msgid, msgstr = result.fetchone()
print '"{}" translates to "{}"'.format(msgid, msgstr.encode('utf8'))

Output

"11 inches/sec." translates to "11 дюймов/с"
"A Samba password is required to export printer drivers" translates to "Для экспорта драйверов принтера требуется пароль Samba"

You might notice that there are lot of msgids with empty msgstrs. If you don't want them, then modify

messages = [(msg.id, msg.string) for msg in cat if msg.id]

to

messages = [(msg.id, msg.string) for msg in cat if msg.id and msg.string]

Upvotes: 1

Related Questions