-2

Recently, I'm trying to make some database. (I'm new to programming) When I'm trying to run following code, I'm receiving this error:

sqlite3.OperationalError: near ".27": syntax error

def file_write(self, name, value):                  
    con = sqlite3.connect("c:/Users/DB.db")
    cursor = con.cursor()
    cursor.execute("CREATE TABLE {} (Date text, Value1 int, Value2 int, Value3 int, Value4 int, Value5 int, Value6 float, Value7 float, Value8 float)".format(name))
    for i in range(3):
        cursor.execute("INSERT INTO {0} VALUES({1},{2},{3},{4},{5},{6},{7},{8},{9})".format(name, value[i][0], value[i][1], value[i][2], value[i][3], value[i][4], value[i][5], value[i][6], value[i][7], value[i][8]))
        #cursor.execute("INSERT INTO {0} VALUES({1},{2},{3},{4},{5},{6},{7},{8},{9})".format(name, '2020.03.27', '12500', '13100', '15100', '10950', '31323050', '10440.0', '10524.5', '45.0'))
    con.commit()
    con.close()

Values are DataFrame and remark's values are matched with each value[i][j]. I spent my all day with this problem please help me

dspencer
  • 4,297
  • 4
  • 22
  • 43
ketiz
  • 3
  • 1

1 Answers1

0

The problem is with your date formatting, this will work:

cursor.execute("INSERT INTO {0} VALUES('{1}',{2},{3},{4},{5},{6},{7},{8},{9})".format(name, '2020-03-27','12500', '13100', '15100', '10950', '31323050', '10440.0', '10524.5', '45.0'))

Read more from the docs here: https://www.sqlite.org/datatype3.html#date_and_time_datatype

Also as was mentioned in the comments, you should never format query strings yourself. Untrustworthy inputs formatted into sql queries will make your code vulnerable for sql injection attacks (read more here https://en.wikipedia.org/wiki/SQL_injection)

Bemis
  • 3,092
  • 2
  • 17
  • 24
  • Thank you for reply! I read link that you mentioned. But, I can't use date function (my programming skill is not good). I executed code you fixed. But It doesn't work. It is recorded '1990' (It's result of 2020 - 3 - 27 = 1990). So I tried other method. When I'm not using query for example """cursor.execute("INSERT INTO name VALUES('2020.03.27', '12500', '13100', '15100', '10950', '31323050', '10440.0', '10524.5', '45.0')")""" It worked! Also, This one worked cursor.execute("INSERT INTO name VALUES('2020-03-27', '12500', '13100', '15100', '10950', '31323050', '10440.0', '10524.5', '45.0')") – ketiz Mar 30 '20 at 13:58
  • I think If I want to keep using query I should change my dataframe columns's value (2020.03.27 to 20200327) or change my existing code. Is there any way to solve the problem? – ketiz Mar 30 '20 at 13:59
  • my bad, you have to quote your date when inserting: ```cursor.execute("INSERT INTO {0} VALUES('{1}',{2},{3},{4},{5},{6},{7},{8},{9})".format(name, '2020-03-27','12500', '13100', '15100', '10950', '31323050', '10440.0', '10524.5', '45.0'))``` – Bemis Mar 30 '20 at 16:01