Help With Python SQLite Error

[Extra]

Hello,

I created this table:

import sqlite3

#Create a database (inventory.db)
connection = sqlite3.connect("inventory.db")
cursor = connection.cursor()

cursor.execute('''CREATE TABLE Items
        (ID INT PRIMARY KEY    NOT NULL,
        NAME           TEXT    NOT NULL,
        Image          BLOB,
        Quantity       TEXT    NOT NULL,
        Price          DOUBLE  NOT NULL,
        Sell Price     DOUBLE,
        Location       TEXT    NOT NULL,
        Description    TEXT,
        Category       TEXT,
        Length(Ft)     INT,
        Brightness     TEXT,
        Rating         TEXT,
        Torque         TEXT,  
        Date Updated   timestamp);''')

#Commit the changes
connection.commit()
#Close the connection
connection.close() 

And when I run the script I get:

Error:
Traceback (most recent call last):
  File "SQLite_Database.py", line 21, in <module>
    Date Updated   timestamp);''')
sqlite3.OperationalError: near "(": syntax error

This is starting to drive me mad because I can't seem to figure it out

Any help would be greatly appreciated.

Thanks.

P.S
Also, I do happen to have another question. How would I go about displaying the records in the database (once I add some)?
I know I'll need a for loop and a select statement but I don't know how to mix them together to get it to work.
(Also any good tutorials for SQLite with python that you can point me to would be appreciated as well. I'm familiar with MySQl but it's been a while. I don't know if SQLite is much different.)

[Extra]

Even tried this:

import sqlite3

#Create a database (inventory.db)
connection = sqlite3.connect("inventory.db")
cursor = connection.cursor()

table = """CREATE TABLE Items
        (ID INTEGER PRIMARY KEY    AUTOINCREMENT,
        NAME           TEXT    NOT NULL,
        Image          BLOB,
        Quantity       TEXT    NOT NULL,
        Price          DOUBLE  NOT NULL,
        Sell Price     DOUBLE,
        Location       TEXT    NOT NULL,
        Description    TEXT,
        Category       TEXT,
        Length(Ft)     INT,
        Brightness     TEXT,
        Rating         TEXT,
        Torque         TEXT,  
        Date Updated   DATETIME);"""

#Execute the creation of the table
cursor.execute(table)
print("The table has been created")
#Commit the changes
connection.commit()
#Close the connection
connection.close() 

and got the same error:

Error:
Traceback (most recent call last):
  File "SQLite_Database.py", line 25, in <module>
    cursor.execute(table)
sqlite3.OperationalError: near "(": syntax error

Why is this?
I still can't find my syntax error.

[deanhystad]

There are hundreds of sqlite3 tutorials. Go work through a few of those.

[ibreeden]

This is the syntax error:

Length(Ft)     INT,

You cannot have parentheses in column names. Change it to:

Length_Ft     INT,

I also have my doubts about column names with spaces in them:

Sell Price     DOUBLE,
...
Date Updated   DATETIME);"""

I would change the space to an underscore or else you would have to quote these column names.

[Extra]

This is the syntax error:

Length(Ft)     INT,

You cannot have parentheses in column names. Change it to:

Length_Ft     INT,

I also have my doubts about column names with spaces in them:

Sell Price     DOUBLE,
...
Date Updated   DATETIME);"""

I would change the space to an underscore or else you would have to quote these column names.

Thanks, that helped!

I do have one more question though:
Now that I have my table working/created how do I get it so I can input the info for the columns through the terminal?
What I mean is when I run my python script it will say the column title so the user can input the values like so:

Output:
NAME: ___user input here:_____
(User hit's enter)
Image ___that can be left empty for now__
(User hit's enter)
Quantity __User enters a number____
...

And how would I get it to display like that too (or display like a table) when I call my SELECT statement to view the info in the table?

[menator01]

I would put it all in a list then insert the list all at once. For display look into tabulate or pandas.

[Extra]

I would put it all in a list then insert the list all at once. For display look into tabulate or pandas.

Ok so I made a script to put all the info in a list.
But how do I get it so it repeats and creates a new list every time?
I know for the repeating part I'll use a while loop (while input does not equal "Q" or "quit" (or something like that)), but I don't know how to put the new inputted info into a new list and not overwrite the old one. That way I can have several lists with the same format (name, price, quantity,...) but with different inputted info.

Here's the code right now:

#Creatie an empty list
Item = []
 
#Elements for the list
name = (input("Item Name: "))
price = (input("Item Price: "))
quantity = (input("Item Quantity: "))
description = (input("Item Description: "))
category = (input("Item Category: "))

 
ItemInfo = (name,price,quantity,description,category)
 
Item.append(ItemInfo) #Appened the ItemInfo to the Item list
     
print(Item) #Print the list

Thanks in advance.
I really appreciate the help.

[ibreeden]

But how do I get it so it repeats and creates a new list every time?
I know for the repeating part I'll use a while loop (while input does not equal "Q" or "quit" (or something like that)), but I don't know how to put the new inputted info into a new list and not overwrite the old one.

Your code looks quite nice so I don't know exactly what your problem is. Is it the part "creates a new list every time"? You should not create the list again. Do you mean to have something like this?

#Creatie an empty list
Item = []

while True:  
    #Elements for the list
    name = (input("Item Name (Q to Quit): "))
    if name == "Q":
        break
    price = (input("Item Price: "))
    quantity = (input("Item Quantity: "))
    description = (input("Item Description: "))
    category = (input("Item Category: "))

    ItemInfo = (name,price,quantity,description,category)
    Item.append(ItemInfo) #Append the ItemInfo to the Item list.
      
print(Item) #Print the list

[menator01]

Here is a rough example of one way to do it. (There are better ways)
Just call the function for each insert.

import sqlite3 as sq

conn = sq.connect('inventory.db')

table = '''
    create table items (
    id integer primary key autoincrement not null,
    name varchar(255) not null,
    image blob,
    quantity integer not null default 0,
    price double not null,
    sell_price double,
    location text,
    description text,
    category varchar(255),
    length integer default 0,
    brightness varchar(255),
    rating varchar(255),
    torque varchar(255),
    updated datetime default current_timestamp
    );
'''

conn.execute(table)

def inserts():
    name = input('item name: ')
    price = input('item price: ')
    quantity = input('item quantity: ')
    description = input('item description: ')
    category = input('item category: ')
    mylist = [name, price, quantity, description, category]
    return mylist


cursor = conn.cursor()
cursor.execute('''
    insert into items (name, price, quantity, description, category)
    values (?,?,?,?,?)
    ''', inserts())
conn.commit()

cursor.execute('select * from items')

result = cursor.fetchall()

conn.close()

print(result)

Output:
[(1, 'goofy', None, 8, 10.0, None, None, 'some kind of something', 'doggy', 0, None, None, None, '2022-05-04 07:13:50')]

[Extra]

Here is a rough example of one way to do it. (There are better ways)
Just call the function for each insert.

import sqlite3 as sq

conn = sq.connect('inventory.db')

table = '''
    create table items (
    id integer primary key autoincrement not null,
    name varchar(255) not null,
    image blob,
    quantity integer not null default 0,
    price double not null,
    sell_price double,
    location text,
    description text,
    category varchar(255),
    length integer default 0,
    brightness varchar(255),
    rating varchar(255),
    torque varchar(255),
    updated datetime default current_timestamp
    );
'''

conn.execute(table)

def inserts():
    name = input('item name: ')
    price = input('item price: ')
    quantity = input('item quantity: ')
    description = input('item description: ')
    category = input('item category: ')
    mylist = [name, price, quantity, description, category]
    return mylist


cursor = conn.cursor()
cursor.execute('''
    insert into items (name, price, quantity, description, category)
    values (?,?,?,?,?)
    ''', inserts())
conn.commit()

cursor.execute('select * from items')

result = cursor.fetchall()

conn.close()

print(result)

Output:
[(1, 'goofy', None, 8, 10.0, None, None, 'some kind of something', 'doggy', 0, None, None, None, '2022-05-04 07:13:50')]

THANKS! This is exactly what I needed!