CODEWITHSUNDEEP
pythondatabasesqlite
alyferryhalo
alyferryhalo

Reputation: 43

SQLite3: an empty database after running python code

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:

Upvotes: 0

Views: 1097

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169314

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()

Upvotes: 1

Related Questions