SQLite can't read table create with Python

Question

I'm trying to create an SQL table from CSV file using Python sqlite3 library. That's the code I wrote:

import csv
import sqlite3


csv_file = 'path/to/file.csv'
db = sqlite3.connect('clients.db')
db.execute('''CREATE TABLE main(
                ID          INT PRIMARY KEY     NOT NULL,
                name        VARCHAR,
                email       VARCHAR,
                address    VARCHAR);''')
# That part is only for getting the number of rows in the table
with open(csv_file, 'r') as csv_table: 
    reader = csv.DictReader(csv_table, delimiter=';')
    rows = sum(1 for r in reader)
# Here I'm reading the table
with open(csv_file, 'r') as csv_table:
    reader = csv.DictReader(csv_table, delimiter=';')
    for i, row in enumerate(reader):
        db.execute('''INSERT INTO main VALUES 
        ('{idn}', '{name}', '{email}', '{add}');'''.format(idn=i,
                                      name=row['name'],
                                      email=row['email'],
                                      add=row['address']))
        print('{}/{}:\t{}'.format(i+1, rows, row))
db.commit()
db.close()

After I've run this code I tried to access the table with sqlite3 but I got the error message: "Error: no such table: main" anytime I've send a query regarding 'main' table.

I've tried to read the table with Python and it works fine:

import sqlite3


db = sqlite3.connect('clients.db')
rows = db.execute('SELECT * FROM main;')
for r in rows:
    print(r)

I'm running this script from the same directory that I'm running SQLite3.

I'm using Python 3.6 and my OS is Ubuntu 16.04.6 LTS.

Wild guess. As you do not use full paths, Python works in one folder and sqlite3 in another one, so they use 2 different databases. — Serge Ballesta
– Serge Ballesta, Commented Jun 19, 2019 at 13:08
@SergeBallesta I just change it to full paths and it's working, thanks. — ygeyzel
– ygeyzel, Commented Jun 19, 2019 at 13:17
Feel free to use my comment to build an answer and later accept it to avoid leaving an unanswered question. — Serge Ballesta
– Serge Ballesta, Commented Jun 19, 2019 at 13:19

ygeyzel · Accepted Answer · 2019-06-19 13:29:35Z

1

The problem was that Python and sqlite3 were actually running on different locations so they used different databases.

I've changed

db = sqlite3.connect('clients.db')

to

db = sqlite3.connect('full/path/to/clients.db')

and now it's working fine.

answered Jun 19, 2019 at 13:29

ygeyzel

116 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLite can't read table create with Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related