SQL for creating tables in a database using Python sqlite3

Question

I am trying to create a number of tables with different names (of course) but sharing the same schema. For this purpose, I am using executemany on a Cursor object as follows:

tables = ['Meanings', 'Synonyms', 'Antonyms', 'Examples', 'Phrases',
          'Pronunciations', 'Hyphenations']
create_table_query = '''CREATE TABLE (?) (
                                        id      INTEGER NOT NULL,
                                        text    TEXT,
                                        word_id INTEGER NOT NULL,
                                        PRIMARY KEY id,
                                        FOREIGN KEY(word_id) REFERENCES Word(id)
                                    )'''
cursor.executemany(create_table_query, tables)

When I execute this snippet, I get the following error message:

OperationalError: near "(": syntax error

I am having trouble fixing the bug here with my SQL since I find the error message to be not descriptive enough. I have tried the following queries but I am unable to develop the understanding of their success and my query's failure:

create_table_query_1 = '''CREATE TABLE {} (
                                         id INTEGER NOT NULL,
                                         text TEXT,
                                         word_id INTEGER NOT NULL,
                                         PRIMARY KEY id,
                                         FOREIGN KEY(word_id) REFERENCES Word(id)
                        )''' # Syntax error near "id"
create_table_query_2 = '''CREATE TABLE (?) (
                                         id INTEGER PRIMARY KEY,
                                         text TEXT,
                                         word_id INTEGER NOT NULL,
                                         FOREIGN KEY(word_id) REFERENCES Word(id)
                        )''' # Syntax error near "("

create_table_query_1 = '''CREATE TABLE {} (
                                         id INTEGER PRIMARY KEY,
                                         text TEXT,
                                         word_id INTEGER NOT NULL,
                                         FOREIGN KEY(word_id) REFERENCES Word(id)
                        )''' # works with string formatting

Also, what are other efficient(in terms of time) ways to achieve the same?

@bernie Thanks. I didn't know that. Can you add a link to the documentation where one can read more about the limitations of parameterization in Python's sqlite3 library? — Kshitij Saraogi
– Kshitij Saraogi, Commented Nov 8, 2017 at 16:59
I was looking for documentation about that and don't see it, sorry. It's weird, that should probably be added to the documentation. — mechanical_meat
– mechanical_meat, Commented Nov 8, 2017 at 17:04

mechanical_meat · Accepted Answer · 2017-11-08 17:21:31Z

2

To put my comment into an answer and expand on it: you cannot parametrize table nor column names. I was unable to find any documentation on this...

In a couple of the other examples you have extra parens/brackets that SQLite doesn't need.

So the solution, as you've found, is to use string substitution for the table names as in your final example.

Here's an example with a loop over all of your tables:

for table in tables:
    cursor.execute('''CREATE TABLE {} (
                          id INTEGER PRIMARY KEY,
                          text TEXT,
                          word_id INTEGER NOT NULL,
                          FOREIGN KEY(word_id) REFERENCES Word(id)
                        )'''.format(table))

I am not completely clear on why you want different tables for the different types of words, though, as this would seem to go against the principles of database design.

answered Nov 8, 2017 at 17:21

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

2 Comments

Kshitij Saraogi Over a year ago

Thanks once again for your help. To answer your question regarding database design: I am creating a CLI vocabulary application. I am creating a main table Word which is referenced by other tables such as synonyms, antonyms etc. This is my first time working with SQL and I would love to know your thoughts on the best practices for database design.

mechanical_meat Over a year ago

I think I would have one table, Word, and have a word_type column in it. How does that sound?

Collectives™ on Stack Overflow

SQL for creating tables in a database using Python sqlite3

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related