CODEWITHSUNDEEP
pythonsqlite
Runner Bean
Runner Bean

Reputation: 5195

sqlite python - read records into table from txt file

Ive created a python script that sets up a SQLite database and creates a table, now im trying to read values into the table from a .txt file, as follows 

import  sqlite3
conn = sqlite3.connect('mydatabase.db')
c = conn.cursor()

c.execute('''CREATE TABLE mytable
         (var1 TEXT,
         var2 REAL)''')

c.execute('separator "," ')

c.execute('import records.txt myTable ')

conn.commit()

for row in c.execute('SELECT * FROM myTable'):
    print(row)

conn.close()

the records.txt looks like

item1, 8.8
item2, 9.1

when i run the python code form the command line i get

    c.execute('separator "," ')
sqlite3.OperationalError: near "separator": syntax error

how do I use the seperator sql statement here and and maybe the same problem will be for the import statement?

How to get this code working?

Upvotes: 1

Views: 2522

Answers (3)

Ewald
Ewald

Reputation: 155

The solution above will work fine. Just to make it a little more readable and easier to debug you could also go like this:

import  sqlite3
conn = sqlite3.connect('mydatabase.db')
c = conn.cursor()

c.execute('''CREATE TABLE mytable (var1 TEXT, var2 REAL)''')

with open("records.txt", "r") as f:
    rows = f.readlines()
    for row in rows:
        fields = row.split(',')
        c.execute(f'INSERT INTO mytable (var1, var2)'\
                  f"VALUES ('{fields[0]}','{fields[1]}')")

conn.commit()

for row in c.execute('SELECT * FROM myTable'):
    print(row)

conn.close()

This way you open the text file with Python and read the lines. for each line you than seperate the records and put them into the INSERT-statement right away. Quick and easy!

goodluck!

Upvotes: -1

BramAppel
BramAppel

Reputation: 1366

c.execute('separator "," ')

should be replaced with:

sql_statement = ''' INSERT INTO mytable(var1, var2) VALUES (?, ?) '''
values = ('text', 343.4)
c.execute(sql_statement, values)

The question marks are the placeholders for the values you want to insert. Note that you should pass these values in a tuple, if you want to insert a single value into your database the values argument should look like this:

values = (text,)

A working solution:

import  sqlite3

# create database and initialize cursor
conn = sqlite3.connect('mydatabase.db')
c = conn.cursor()

# create table if not exists
c.execute('''CREATE TABLE IF NOT EXISTS mytable(var1 TEXT, var2 REAL)''')


sql_insert = ''' INSERT INTO mytable(var1, var2) VALUES (?, ?) '''
with open('results.txt', 'r') as fr:
    for line in fr.readlines():
        # parse the results.txt, create a list of comma separated values
        line = line.replace('\n', '').split(',')
        t, f = line
        c.execute(sql_insert, (t, float(f)))

conn.commit()

sql_select = ''' SELECT * FROM mytable '''
for row in c.execute(sql_select):
    print(row)

conn.close()

Upvotes: 0

deets
deets

Reputation: 6395

I don't think this is possible with the Python module sqlite3. The .separator (for me only with leading dot) command, as well as the .import, are features of the CLI fronted sqlite3.

If you want to use these, you could use subprocess to invoke the commands:

import subprocess

p = subprocess.Popen(["sqlite3", "mydatabase.db"], stdout=subprocess.PIPE, stdin=subprocess.PIPE)

p.communicate(b"""
CREATE TABLE mytable
         (var1 TEXT,
         var2 REAL);
.separator ","
.import records.txt myTable
""")

Upvotes: 2

Related Questions