How to Replace with a Variable When Using sqlite3 in Python

I am trying to store the database in sqlite3 using python, but I tried to store additional data in sqlite3 with date, but I can't do it well because of a syntax error.
Please tell me how to improve it.

Columns are id (integer type) and date (text type).
Thank you for your cooperation.

This is what the code looks like now.

import sqlite3
from matplotlib import pyplot as plt
import numpy as np
import time
importos
import datetime
import shutil

x,y=np.loadtxt('exp-data.txt', delimiter=', unpack=True)
plt.plot(x,y)

os.chdir("/home/desktop/uploader/public/uploads/user/avatar/1")#change directory

plt.savefig('sample.png')

time = datetime.datetime.now()
newname="{0:%Y-%m-%d-%H:%M:%S}.png".format(time)
os.rename("sample.png", newname)

newtitle="{0:%Y-%m-%d-%H:%M:%S}".format(time)

os.chdir("/home/ienaga/desktop/uploader/db")#change directory
connector=sqlite3.connect("development.sqlite3")

sql = "insert into graph('1', newtitle) values"
sql+="'+str(newtitle)+"');"

connector.execute(sql)

connector.commit()
connector.close()

Fill in the error details

Traceback (most recent call last):
  File "yomikomi01.py", line 28, in <module>
    connector.execute(sql)
sqlite3.OperationalError: near "'2016-04-13-14:29:45'":syntax error

python sqlite

2022-09-30 21:15

3 Answers

If you say "columns are id(integer type) and date(text type), then maybe the first half of sql is also strange.

sql="INSERT INTO graph (id, date) VALUES('1',?)"
connector.execute(sql, [newtitle])#See the postscript

When embedding values, it is better to embed SQL statements with ? instead of directly creating SQL statements as strings.

(Additional)
I have verified that the above code works in your environment, but as you mentioned in the comment, the document says that the value for ? should be specified in tuple of values and the code example consistently matches it.

connector.execute(sql,(newtitle,))

2022-09-30 21:15

How about the following?

connector.execute("insert into graph(newtitle) values(%s)",(newtitle,))

2022-09-30 21:15

The following are incorrect.

sql+="'+str(newtitle)+"');"

Correctly

sql+="('+str(newtitle)+"');"

SQL syntax errors are easy to see when you log or console SQL statements that you create.

2022-09-30 21:15

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656