0

i have a table which holds some columns, ID(IntPK), Name(Text), Quantity(Int), Total(Int), Hour(Int),Date(Int)

I want to only have to insert a Name, a Quantity, and a Total, and then whenever a field is inserted into this table, i want it to automatically fill the hour and date with say, the hour and date from the computer.. Is there a way? I don't want to have to be typing the date and hour in everytime i insert a new field..

import sqlite3
conn = sqlite3.connect('Inventario.db')
c = conn.cursor()
conn.commit()

c.execute('''CREATE table if not exists sales(id INTEGER PRIMARY KEY, client TEXT, qty INTEGER, total INTEGER, hour INTEGER, date INTEGER)''')

1 Answers1

0

You could create a method which detects a date using (for example) datetime.

import datetime
def insert_into_table(cursor,client,qty,total):
    date = datetime.datetime.now().date() # you can change it to your desired format as well as hour
    hour = datetime.datetime.now().hour()
    cursor.execute("INSERT INTO sales(client,qty,total,hour,date) VALUES (?,?,?,?,?)", (client,qty,total,hour,date))

Cursor could be an attribute of a class in my opinion so you don't need to add the attribute into the method.

EDIT: Thanks Jon Clements who helped me to improve my code to be more safe.

Milano
  • 18,048
  • 37
  • 153
  • 353
  • Don't use string interplation for construction of SQL value queries, and the normally used place holder for sqlite is `?` not `%s` – Jon Clements May 18 '15 at 23:16
  • Actually, I use %s and it works for me. – Milano May 18 '15 at 23:17
  • Yeah... it accepts multiple ones, but `?` is generally the one used... I've downvoted because you're using string interpolation which is *dangerous* and subject to SQL injection attacks – Jon Clements May 18 '15 at 23:18
  • Ok, thank you, I will use ? in a future. But I think that this is a possible solution whether I used %s or ?. – Milano May 18 '15 at 23:19
  • That's not the *key* point - `cursor.execute("INSERT INTO sales(client,qty,total,hour,date) VALUES ('%s','%s','%s','%s','%s')"%(client,qty,total,hour,date))` is **dangerous**.... it should be at least something like: `cursor.execute("INSERT INTO sales(client,qty,total,hour,date) VALUES (?,?,?,?,?)", (client,qty,total,hour,date))` that then allows the DB API to correctly escape the values passed... – Jon Clements May 18 '15 at 23:21
  • Compuslory: https://xkcd.com/327/ and http://www.explainxkcd.com/wiki/index.php/Little_Bobby_Tables – Jon Clements May 18 '15 at 23:24
  • Ok, thanks, I didn't know about this notation and risk of my %s notation in this case. – Milano May 18 '15 at 23:24