0

Here's what I am trying to do:

import sqlite3

conn = sqlite3.connect('example.db')

c = conn.cursor()

c.execute(CREATE table Movies(index integer, mvnm text, year integer, genere text))

SyntaxError: invalid syntax

It highlights table in red color.

Tonechas
  • 13,398
  • 16
  • 46
  • 80

1 Answers1

2

There are few problems:

  • The SQL should be provided in quotes because the execute() method accepts a string argument.
  • index is a reserved keyword in SQL, so you cannot use that as the name of your column.
  • If you want to run this script repeatedly, you should add IF NOT EXISTS clause. Otherwise consequent runs will fail due to "table already exists"

So, after these changes the statement looks like below:

c.execute("CREATE table IF NOT EXISTS Movies(myindex integer, mvnm text, year integer, genere text)")

You can then verify the table creation by logging in to sqllite:

$> ~ $ sqlite3 example.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .tables
Movies
Bajal
  • 5,487
  • 3
  • 20
  • 25
  • Thanks, for a newbie to sqlite3 and python it was really getting tedious. I ran c.execute("CREATE table IF NOT EXISTS Movies(myindex integer, mvnm text, year integer, genere text)") and instantly got this I guess that is confirmation itself. In addition to this, I have one more question sqlite3 comes built in python 3, meaning I do not have to separately install sqlite3 module using pip. so how do I verify it, where should I run that command? In python idle? –  Nov 29 '16 at 00:57
  • Yes, it does come builtin. I usually run `python -c "import sqlite3"` in a shell to verify if a module is available. A little faster than doing the same in the idle shell – Bajal Nov 29 '16 at 01:00
  • No No! I'm talking about $> ~ $ sqlite3 example.db SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> .tables Movies Where should I run the first command so as to know which sqlite3 version do i have and access Movies table? In command prompt or Python idle shell? Sorry if its annoying you! –  Nov 29 '16 at 01:07
  • That would be at the command prompt. To know the version, run `sqlite3 -version`. This assumes you have it in the path. Also, see this post for querying etc: http://stackoverflow.com/questions/4654762/how-can-one-see-the-structure-of-a-table-in-sqlite?rq=1 – Bajal Nov 29 '16 at 01:10