-4

There is a DrugName column in the database, but I am getting this error that says the column does not exist.

def create_connection(db_file):
    
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)

    return conn

def select_by_slot(conn, slot_name, slot_value):
    """
    Query all rows in the tasks table
    :param conn: the Connection object
    :return:
    """
    cur = conn.cursor()
    cur.execute("SELECT * FROM finale WHERE {}='{}'".format(slot_name, slot_value))

    rows = cur.fetchall()

    if len(list(rows)) < 1:
        print("There are no resources matching your query.")
    else:
        print(rows)
        # for row in random.sample(rows, 1):
        #     print(f"Try the {(row[0])}")

select_by_slot(create_connection("ubats.db"),
    slot_name = 'DrugName',slot_value= 'Beclomethasone dipropionate 100mcg and formoterol fumarate dihydrate 6mcg pressurized inhalation solution')

I want to be able to search if a particular drug is in the column. If so, print the row. I have tried searching and using f-string formatting too, but it did not work.

Ideas please?

error message:

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
/Users/karan/udev/ubat_db/talk_db.ipynb Cell 2 in <cell line: 33>()
     29         print(rows)
     30         # for row in random.sample(rows, 1):
     31         #     print(f"Try the {(row[0])}")
---> 33 select_by_slot(create_connection("ubats.db"),
     34     slot_name = 'DrugName',slot_value= 'Beclomethasone dipropionate 100mcg and formoterol fumarate dihydrate 6mcg pressurized inhalation solution')

/Users/karan/udev/ubat_db/talk_db.ipynb Cell 2 in select_by_slot(conn, slot_name, slot_value)
     16 """
     17 Query all rows in the tasks table
     18 :param conn: the Connection object
     19 :return:
     20 """
     21 cur = conn.cursor()
---> 22 cur.execute("SELECT * FROM finale WHERE {}='{}'".format(slot_name, slot_value))
     24 rows = cur.fetchall()
     26 if len(list(rows)) < 1:

OperationalError: no such column: DrugName
karan
  • 85
  • 6
  • is your table name right? I seen your comment is tasks table but you searched finale table – JumHorn Sep 20 '22 at 03:35
  • if code can't find column then you don't have this column, or it has different name, or you use wrong database. Maybe you have to use `"/full/path/to/ubats.db"` You could also run `"SELECT * FROM finale` and check `cur.description` to see what columns you get. [Is there a way to get a list of column names in sqlite?](https://stackoverflow.com/questions/7831371/is-there-a-way-to-get-a-list-of-column-names-in-sqlite) – furas Sep 20 '22 at 06:33
  • @JumHorn, thanks for pointing that out. But the error was in the comment only. search finale table was correct. – karan Sep 20 '22 at 12:54
  • @furas: cur.description gives me this (('DrugName,MDC,Category,Indication,Restrictions,Dosage', None, None, None, None, None, None),) – karan Sep 20 '22 at 12:55
  • you can have a try to use full path to this "ubats.db" file. May be it is path error and "ubats.db" is new empty one – JumHorn Sep 20 '22 at 13:01
  • it strange. Maybe name has some non-printable char or space at the end and maybe it will give False when you try to use `==` to compare string `'DrugName'` with value from `cur.description` – furas Sep 20 '22 at 13:55

1 Answers1

1

the problem was apparently due to improper database creation.

df = pd.read_csv('/Users/karan/Downloads/ubat.csv') 
df.to_sql('finale', conn, if_exists='append', index=False)

This way of creating the database solved the issue. Thanks all.

karan
  • 85
  • 6