How to insert data into SQLite3 database with Python?

Question

I need to insert data into SQLite3 database using Python. I have written the query but it's not working as I expected. My code is below.

conn = sqlite3.connect("db.sqlite3")
cursor = conn.cursor()
location_name = request.POST.get('lname')
rname = request.POST.get('rname')
seat = request.POST.get('seat')
projector = request.POST.get('projector')
video = request.POST.get('video')
location_name = location_name[0:255]
rname = rname[0:255]
seat = seat[0:10]
from_date = request.POST.get('from_date')
to_date = request.POST.get('from_date')
current_datetime = datetime.datetime.now()
now = current_datetime.strftime("%Y-%m-%d %H:%M")
cursor.execute("INSERT INTO booking_meeting (room_name,from_date,to_date,no_seat,projector,video,created_date,location_name) \ VALUES (rname, from_date, to_date, seat, projector, video, now, location_name  )")

conn.commit()

Here I am giving the dynamic value and no data is inserting into table.

try this cursor.execute("INSERT INTO booking_meeting (room_name,from_date,to_date,no_seat,projector,video,created_date,location_name) \ VALUES " %(rname, from_date, to_date, seat, projector, video, now, location_name )) — ksai
– ksai, Commented Jul 31, 2017 at 5:07
@ksai The string for the SQL statement has no format specifiers to place the data from the tuple. — Code-Apprentice
– Code-Apprentice, Commented Jul 31, 2017 at 5:12

Code-Apprentice · Accepted Answer · 2017-07-31 05:09:48Z

2

You need to put the values of your variables into the SQL statement. The safest way to do this is with something like the following

cursor.execute("INSERT INTO booking_meeting (room_name,from_date,to_date,no_seat,projector,video,created_date,location_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", (rname, from_date, to_date, seat, projector, video, now, location_name ))

Note that the variables are passed as a tuple so that their values can be used in the SQL statement.

answered Jul 31, 2017 at 5:09

Code-Apprentice

84k26 gold badges162 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anton vBR Over a year ago

Yep and looks like he is populating all table so: cursor.execute("INSERT INTO booking_meeting VALUES (?, ?, ?, ?, ?, ?, ?, ?)", (rname, from_date, to_date, seat, projector, video, now, location_name )) should be enough, right?

M Dennis · Accepted Answer · 2017-07-31 06:23:23Z

1

In addition to @Code-Apprentice:

You could uses executemany to insert many values:

cursor.executemany(
  "INSERT INTO booking_meeting (room_name,from_date,to_date,no_seat,projector,video,created_date,location_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
  [
    (rname1, from_date1, to_date1, seat1, projector1, video1, now1, location_name1),
    (rname2, from_date2, to_date2, seat2, projector2, video2, now2, location_name2)
  ]
)

Furher reading

answered Jul 31, 2017 at 6:23

M Dennis

1777 bronze badges

Collectives™ on Stack Overflow

How to insert data into SQLite3 database with Python?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related