inserting into sqlite db-python

Question

I'm trying to use a SQLite database in my newest python program. I'm trying to insert a new row into my database by using insert. The database's columns are set up as:

0|pw|TEXT|0||0
1|Name|TEXT|0||0
2|dataBaseID|INT|0||0
3|email|TEXT|0||0

My python code is:

this = "INSERT INTO users (pw, name, dataBaseID, email)"
this += " VALUES ("+passWord+", "+UserName+", "+"1"+", "+email+")"
print(this)
cur.execute(this)
dbCon.commit()

And the error is:

Traceback (most recent call last):
  File "/usr/lib/python3.2/multiprocessing/process.py", line 267, in _bootstrap
    self.run()
  File "/usr/lib/python3.2/multiprocessing/process.py", line 116, in run
    self._target(*self._args, **self._kwargs)
  File "serverBackend.py", line 85, in main
    add(msg,cur,databaseConnection,connect)
  File "serverBackend.py", line 20, in add
    cur.execute(this)
sqlite3.OperationalError: no such column: Chips1994

Why Is it throwing an error and indicating that the Im trying to access a column that doesn't exist? Thanks in advance.

INSERT INTO users (pw, name, dataBaseID, email) VALUES (Chips1994, raddingy, 1, raddingy_msu.edu) — rady
– rady, Commented Jul 9, 2014 at 20:43

tjati · Accepted Answer · 2014-07-09 20:46:41Z

4

You have to surround your values with " or something similar. What you're currently doing is in addition, really bad code and may result in some serious sql injection problems.

As stated in the official python-sqlite 3 documentation, you should use placeholders.

Replace your current code with

query = "INSERT INTO users (pw, name, dataBaseID, email) VALUES (?, ?, ?, ?)"
cur.execute(query, (passWord, UserName, 1, email))
cur.commit()

Using the placeholder ?, the sql wrapper will take care to surround the values with necessary characters like ".

Bonus tip: It seems, that you want to store a password in clear text. You should avoid this and use some hash method like PBKDF2.

answered Jul 9, 2014 at 20:46

tjati

6,1166 gold badges47 silver badges62 bronze badges

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

1 Comment

rady Over a year ago

Thanks! I completely forgot about that! I'm still in the process of learning sqlite.

Collectives™ on Stack Overflow

inserting into sqlite db-python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related