Python Script for sqlite3 can't create table

Question

import sqlite3
conn = sqlite3.connect('serpin.db')

c = conn.cursor()

c.execute("""CREATE TABLE Gene(Gene_name TEXT, Organism TEXT, link_2_gene_with_ID TEXT, Number_SpliceForm INTEGER,ID_mRNA INTEGER, ID_Prt INTEGER);""")

c.execute(".import practice.csv Gene --csv")
c.execute(".mode column")
c.execute("select * from Gene;")
print(c.fetchall())
conn.commit()
conn.close

I can run all these commands individually on my own on the windows terminal in sqlite3. However I get multiple errors running this code, which is roughly what i used in a bash script where i got no errors. The first error I receive is an error that ssays "table Gene already exists." Now even if i comment out that line, i also get an error in the import command, where it says there is a syntax error with the period right before import. These are all sqlite3.OperationalError. I have tried running these commands on their own directly in sqlite3 and have no issues, so i'm not sure what the problem is.

I have no database in this folder, so I'm not sure how the table is already made.

edit(solution): the output of this is not formatted correctly, but this runs without errors.

import csv,sqlite3
conn = sqlite3.connect('serpin.db')

c = conn.cursor()

try:
   c.execute("""CREATE TABLE Gene (Gene_name TEXT, Organism TEXT, link_2_gene_with_ID TEXT, Number_SpliceForm INTEGER,ID_mRNA INTEGER, ID_Prt INTEGER);""")
except:
   pass
path = r'C:\Users\User\Desktop\sqlite\practice.csv'
with open(path,'r') as fin: # `with` statement available in 2.5+
    # csv.DictReader uses first line in file for column headings by default
    dr = csv.DictReader(fin) # comma is default delimiter
    to_db = [(i['Gene_name'], i['Organism'],i['link_2_gene_with_ID'],i['Number_SpliceForm'],i['ID_mRNA'],i['ID_Prt'] ) for i in dr]

c.executemany("INSERT INTO Gene (Gene_name,Organism,link_2_gene_with_ID,Number_SpliceForm,ID_mRNA,ID_Prt) VALUES (?,?,?,?,?,?);", to_db)
c.execute("select * from Gene;")
print(c.fetchall())
conn.commit()
conn.close

These dot-preceded commands are for an Sqlite shell. They are not supported by the actual database engine. — Michael Butscher, Nov 21 '20 at 16:47

score 1 · Answer 1 · answered Nov 21 '20 at 16:44

About the fact that you may already have created the table and that gives you an error:

try:
    c.execute("""CREATE TABLE Gene(Gene_name TEXT, Organism TEXT, link_2_gene_with_ID TEXT, Number_SpliceForm INTEGER,ID_mRNA INTEGER, ID_Prt INTEGER);""")
except:
    pass

To import the file, I report here from another answer from the user mechanical_meat Importing a CSV file into a sqlite3 database table using Python:

import csv, sqlite3

con = sqlite3.connect(":memory:") # change to 'sqlite:///your_filename.db'
cur = con.cursor()
cur.execute("CREATE TABLE t (col1, col2);") # use your column names here

with open('data.csv','r') as fin: # `with` statement available in 2.5+
    # csv.DictReader uses first line in file for column headings by default
    dr = csv.DictReader(fin) # comma is default delimiter
    to_db = [(i['col1'], i['col2']) for i in dr]

cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db)
con.commit()
con.close()

Don't know about the .mode command, but as far as I know, operation in SQLite3 in python are all in capital letters, thus also select should be SELECT

Thanks, with some mods, this worked. now i just need to get it formatted. — Erick, Nov 21 '20 at 18:59

Python Script for sqlite3 can't create table

1 Answers1