Pysqlite2.dbapi.operationalerror Syntax Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the sqlite3 operationalerror near syntax error python company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions
Python Sqlite3 Operationalerror
Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million
Sqlite3.operationalerror Syntax Error
programmers, just like you, helping each other. Join them; it only takes a minute: Sign up sqlite3 “OperationalError: near ”(“: syntax error” python up vote 2 down vote favorite simply put i am trying to make
Near Syntax Error Sqlite3
a sql database table and input data into it. I have it working in a simpler way, but when I put it into my script it results in this error. I'm hoping its something simple I missed. Any help/advice would be greatly appreciated. conn = sqlite3.connect('Data1.db') c = conn.cursor() # Create table c.execute('''CREATE TABLE Data_Output6 (date text, output6MV real)''') Averages_norm = [] for i, x in enumerate(Averages): Averages_norm.append(x*output_factor) c.execute("INSERT INTO Data_Output6 VALUES (%r,%r)" %(xdates[i],Averages_norm[-1])) sqlite3.operationalerror unable to open database file conn.commit() results in the error: 57 for i, x in enumerate(Averages): 58 Averages_norm.append(x*output_factor) ---> 59 c.execute("INSERT INTO Data_Output6 VALUES (%r,%r)"%(xdates[i],Averages_norm[-1])) 60 conn.commit() 61 OperationalError: near "(": syntax error python sqlite3 share|improve this question edited May 15 '15 at 11:01 Martijn Pieters♦ 502k7513181473 asked May 15 '15 at 10:59 Ciaran 86111 So what data do you have in xdates? –Martijn Pieters♦ May 15 '15 at 11:02 3 Also, why are you using string interpolation instead of SQL parameters? –Martijn Pieters♦ May 15 '15 at 11:02 1 Try printing the statement before executing it. I'm sure you'll immediately spot the error. –Andrea Corbellini May 15 '15 at 11:57 2 You're inviting Booby Tables to tea... bobby-tables.com –LexyStardust May 15 '15 at 12:58 add a comment| 1 Answer 1 active oldest votes up vote 3 down vote accepted Simply put, let the DB API do that formatting: c.execute("INSERT INTO Data_Output6 VALUES (?, ?)", (xdates[i], Averages_norm[-1])) And refer to the documentation https://docs.python.org/2/library/sqlite3.html where is mentioned: Instead, use the DB-API’s parameter substitution. share|improve this answer answered May 15 '15 at 11:13 user3921265 Thanks, that has really helped. I guess im sticking too much to what I know instead of getting to know the packages im using! –Ciaran May 15 '15 at 12:55 add a comment|
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of python sqlite3 insert example this site About Us Learn more about Stack Overflow the company Business sqlite3.operationalerror: database is locked Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask sqlite3.operationalerror no such table Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up http://stackoverflow.com/questions/30257826/sqlite3-operationalerror-near-syntax-error-python sqlite3.OperationalError: near “?”: syntax error in python — using 'IN' operator up vote 2 down vote favorite 2 Code: print 'SELECT %s FROM %s WHERE %s %s %s' % (q_select, q_table, q_where, q_where_operator, q_value) rows = cursor.execute('SELECT %s FROM %s WHERE %s %s ?' % (q_select, q_table, q_where, q_where_operator), (q_value,)).fetchall() Result: SELECT ticket FROM my_table WHERE issue_key IN ('APSEC-2261') Traceback (most recent call http://stackoverflow.com/questions/7447938/sqlite3-operationalerror-near-syntax-error-in-python-using-in-operato last): ... File "code.py", line 1319, in validate to_validate = db_query(q_select = 'ticket', q_table = 'my_table', q_where = 'issue_key', q_where_operator = 'IN', q_value = incident_query_list) File "code.py", line 1834, in db_query rows = cursor.execute('SELECT %s FROM %s WHERE %s %s ?' % (q_select, q_table, q_where, q_where_operator), (q_value,)).fetchall() sqlite3.OperationalError: near "?": syntax error When I perform the exact query directly on the SQLite file in Firefox's SQLite Manager, I receive a proper response without an error: SELECT ticket FROM my_table WHERE issue_key IN ('APSEC-2261') 179908 Update: Trying without the %s substitutions, and still receiving the same error. >>> test = cursor.execute('SELECT ticket FROM my_table WHERE issue_key IN ?', ('APSEC-2261',)).fetchall() Traceback (most recent call last): File "
question and get tips & solutions from a community of 418,626 IT Pros & Developers. It's quick & easy. Lie Hetland https://bytes.com/topic/python/answers/170920-lie-hetland-book-beginning-python book: Beginning Python.. P: n/a Vittorio I am reading "Beginning Python from Novice to Professional" and the book is really awesome. Nonetheless on ch 13 "Database Support" I found this code to import data (in a txt file) into a SQLite Database: #this was corrected because original "import sqlite" does not work from pysqlite2 import dbapi2 as sqlite #this function syntax error strips the txt file from special chars def convert(value): if value.startswith('~'): return value.strip('~') if not value: value = '0' return float(value) conn = sqlite.connect('food.db') curs = conn.cursor() curs.execute(''' CREATE TABLE food ( id TEXT PRIMARY KEY, desc TEXT, water FLOAT, kcal FLOAT, protein FLOAT, fat FLOAT, ash FLOAT, carbs FLOAT, fiber FLOAT, sugar FLOAT ) ''') field_count = 10 #following is near syntax error the line I suspect mistyped markers = ', '.join(['%s']*field_count) query = 'INSERT INTO food VALUES (%s)' % markers for line in open('ABBREV.txt'): fields = line.split('^') vals = [convert(f) for f in fields[:field_count]] #the following line raises error curs.execute(query,vals) conn.commit() conn.close The error was "Traceback (most recent call last): File "C:\Python24\food.py", line 39, in ? curs.execute(query,vals) pysqlite2.dbapi2.OperationalError: near "%": syntax error" After two hours of trying (did I say I am a beginner?) and after some documentation about PySqlite I suspect the error is in: markers = ', '.join(['%s']*field_count) I think Magnus intended: markers = ', '.join(['?']*field_count) Did I found an errata or my Python is still too green? Nov 7 '05 #1 Post Reply Share this Question 8 Replies P: n/a Steve Holden Vittorio wrote: I am reading "Beginning Python from Novice to Professional" and the book is really awesome. Nonetheless on ch 13 "Database Support" I found this code to import data (in a txt file) into a SQLite Database: #this was corrected because original "import sqlite" does not work from pysqlite2 import dbapi2 as sqlite #this function strips t