python/SQL Syntax issues

Question

I'm having trouble calling this SQL command, I am getting: 'OperationalError: near "'0-0'": syntax error'

My call is as follows:

users_db_curs.execute("INSERT INTO `"+table_name+"` (uID, username, password, creator_exp, location) 
VALUES ('"+user_ID+"', '"+username+"', '"+password+"', '"+creator_exp+"', '0-0')")

• Tim M.: Syntax depends on specific SQL implementation--you should specify the database you are using.
• abkai: @TimMedora I'm a bit confused! :P I'm doing this as a part of a study on databases now that I've completed the w3schools tutorials. That's using sqlite3.cursor() on a .db file.
• Tim M.: Sounds like you are using SQL Lite then. That should be enough info for someone to help you (unfortunately I'm not familiar with the syntax).

Answer 1

I tested your statement and it should work fine if your different variables are correctly formatted to be directly concatenated in the statement string litteral:

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> cu = conn.cursor()
>>> cu.execute('create table foo (uID, username, password, creator_exp, location)')
<sqlite3.Cursor object at 0x7fccc2c115e0>
>>> user_ID='a'
>>> username='b'
>>> password='c'
>>> creator_exp='d'
>>> table_name='foo'
>>> cu.execute("INSERT INTO `"+table_name+"` (uID, username, password, creator_exp, location) VALUES ('"+user_ID+"', '"+username+"', '"+password+"', '"+creator_exp+"', '0-0')")
<sqlite3.Cursor object at 0x7fccc2c115e0>

It's probably not the case when you get that syntax error:

>>> username="'barf"
>>> cu.execute("INSERT INTO `"+table_name+"` (uID, username, password, creator_exp, location) VALUES ('"+user_ID+"', '"+username+"', '"+password+"', '"+creator_exp+"', '0-0')")
Traceback (most recent call last):
 File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near "barf": syntax error

So just use formatted parameters instead.

>>> cu.execute("INSERT INTO `"+table_name+"` (uID, username, password, creator_exp, location) VALUES (?, ?, ?, ?, '0-0')", 
(user_ID, username, password, creator_exp))
<sqlite3.Cursor object at 0x7fccc2c115e0

Answer 2

con = sqlite.connect(db_name)
cur = con.cursor()
with con:
 cur.execute("INSERT INTO ? VALUES(?, ?, ?, ?, ?)",
 (table_name, user_ID, username, password, creator_exp))

should be working
You should read Zetcode's tutorial