Brian Brown Brian Brown - 6 months ago 53
Python Question

sqlite3, python, CREATE TABLE - near "/": syntax error

I want to simply create a table in a database using python and sqlite. However, I need my db name to be given by the user. I managed to do this:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3 as sqlite
import sys, os
sys.path.append(os.path.join(os.path.dirname(__file__), '..'))


def create_table(dbname):
sql = '''\
CREATE TABLE IF NOT EXISTS ''' + dbname + ''' (
ID INTEGER PRIMARY KEY NOT NULL,
FIRSTNAME TEXT,
LASTNAME TEXT,
ACADEMIC_DEGREE TEXT,
DISCIPLINE TEXT
'''

connection = sqlite.connect(dbname, check_same_thread=False)
try:
with connection:
cursor = connection.cursor()
try:
cursor.execute(sql)
except sqlite.DatabaseError, dbe:
print dbe
finally:
connection.close()

tmp = os.path.split(os.path.dirname(os.path.abspath(__file__)))
path = os.path.join(tmp[0], 'researchers.sql')
create_table(path)


However, my code does not create a table, but only gives me an error message:
near "/": syntax error
. What's wrong with this code? Where's the syntax error python mentions about, how to solve this?

This code worked:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3 as sqlite
import sys, os
sys.path.append(os.path.join(os.path.dirname(__file__), '..'))


def create_table(dbname):
sql = '''
CREATE TABLE IF NOT EXISTS ''' + str(dbname.split('.')[0]) + ''' (
ID INTEGER PRIMARY KEY NOT NULL,
FIRSTNAME TEXT,
LASTNAME TEXT,
ACADEMIC_DEGREE TEXT,
DISCIPLINE TEXT
)
'''

connection = sqlite.connect(dbname, check_same_thread=False)
try:
with connection:
cursor = connection.cursor()
try:
cursor.execute(sql)
except sqlite.DatabaseError, dbe:
print dbe
finally:
connection.close()

create_table('researchers.sql')

Answer

Replace your sql variable with this;

 sql = '''
                    CREATE TABLE IF NOT EXISTS ''' + dbname + ''' (
                        ID INTEGER PRIMARY KEY NOT NULL,
                        FIRSTNAME TEXT,
                        LASTNAME TEXT,
                        ACADEMIC_DEGREE TEXT,
                        DISCIPLINE TEXT
                    )
                '''