SQLite3: an empty database after running python code

Question

I would like to make a simple data base with two tables. So I use this code to make it and fill with my .csv data:

import sqlite3
import pandas as pd
import os

conn = sqlite3.connect("base.db")
c = conn.cursor()

c.execute('''
CREATE TABLE IF NOT EXISTS table_1 (
Name TEXT,
Index INTEGER,
Sample_ID TEXT PRIMARY KEY);
''')

c.execute('''
CREATE TABLE IF NOT EXISTS table_2 (
Other_id TEXT PRIMARY KEY,
info TEXT);
''')

cmd1 = """sqlite3 base.db <<< ".import table_1.csv table_1" """
cmd2 = """sqlite3 base.db <<< ".import table_2.csv table_2" """

rc1 = os.system(cmd1)
rc2 = os.system(cmd2)

conn.commit()

conn.close()

I get a file with database, but when I use this command SELECT COUNT(*) FROM table_2; in DBeaver I get zero counts. Moreover, the size of .db file is 20,5 kB (20 480 bytes).

How can I fix it? Why is my database empty?

I use this:

Ubuntu 20.04
Python 3.8.5
sqlite3 3.31.1
DBeaver 21.0.4

code looks okay, can you check if DBeaver is pointing to base.db also look if you saw any error when os. system ran cmd1 and cmd2. — simpleApp
– simpleApp, Commented May 15, 2021 at 22:30

mechanical_meat · Accepted Answer · 2021-05-15 22:43:02Z

1

Below is a tested and working example utilising .import solution from this SO answer: https://stackoverflow.com/a/41788666/42346
The commands you were sending with os.system() had multiple problems.
Another issue is that you cannot re-use the reserved word Index as a column name, so I changed that to Idx.

import sqlite3
import pandas as pd
import os

conn = sqlite3.connect("base.db")
c = conn.cursor()

# note change from `Index` to `Idx` because the former is a reserved word in SQLite
c.execute('''
CREATE TABLE IF NOT EXISTS table_1 (
Name TEXT,
Idx INTEGER,
Sample_ID TEXT PRIMARY KEY);
''')

c.execute('''
CREATE TABLE IF NOT EXISTS table_2 (
Other_id TEXT PRIMARY KEY,
info TEXT);
''')

cmd1 = """(echo .separator ,; echo .import table_1.csv table_1) | sqlite3 base.db"""
cmd2 = """(echo .separator ,; echo .import table_2.csv table_2) | sqlite3 base.db"""

rc1 = os.system(cmd1)
rc2 = os.system(cmd2)

conn.commit()

conn.close()

answered May 15, 2021 at 22:43

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

1 Comment

alyferryhalo Over a year ago

Thank you for help. I tried this but after SELECT COUNT(*) FROM table_1; there is zero again ):

Collectives™ on Stack Overflow

SQLite3: an empty database after running python code

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related