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

Question

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')

It doesn't look like your closing your CREATE TABLE statement, the error comes from your first char inside the ''', which im also not sure is valid — Scott Stainton
– Scott Stainton, Commented May 21, 2016 at 12:38

Scott Stainton · Accepted Answer · 2016-05-21 12:41:46Z

1

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
                    )
                '''

answered May 21, 2016 at 12:41

Scott Stainton

3942 silver badges14 bronze badges

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

Comments

Alex Hall · Accepted Answer · 2016-05-21 12:40:33Z

1

The table name can't have arbitrary characters in it, certainly not a /, so you can't create_table(path). Choose a simple table name, such as "researchers".

answered May 21, 2016 at 12:40

Alex Hall

36.2k5 gold badges63 silver badges98 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related