SQLite creating table name using a variable

Question

I was wondering if it was possible to use the datetime module in python to create day-specific tables so that the table's name is the date itself.

date_object = datetime.date.today()
sqlite_create_transfer_table = '''CREATE TABLE IF NOT EXISTS date_object (
                                sender TEXT NOT NULL,
                                recipient TEXT NOT NULL,
                                ID text NOT NULL,
                                Size NOT NULL,
                                Colour NOT NULL,
                                Quantity INTEGER NOT NULL);'''

However this just makes the table titled 'date_object' rather than using the variable. Any help would be greatly appreciated, thanks! <3

It is generally problematic to want to do this, as it creates multiple tables that you now need to manage instead of one table to manage. — Anon Coward
– Anon Coward, Commented Mar 5, 2022 at 18:46

forpas · Accepted Answer · 2022-03-05 17:57:08Z

1

datetime.date.today() will return a datetime.date object which you must convert to a string, but even then a string like 2022-03-05 is not a valid name for SQLite.
You must enclose it between square brackets or backticks or double quotes.

Try this:

date_object = datetime.date.today()
sqlite_create_transfer_table = f"""CREATE TABLE IF NOT EXISTS [%s](
                                sender TEXT NOT NULL,
                                recipient TEXT NOT NULL,
                                ID text NOT NULL,
                                Size NOT NULL,
                                Colour NOT NULL,
                                Quantity INTEGER NOT NULL);""" % date_object

answered Mar 5, 2022 at 17:57

forpas

165k10 gold badges51 silver badges85 bronze badges

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

Comments

Yanis600 · Accepted Answer · 2022-03-05 17:41:37Z

0

You need to concatenate the query something like this:

sqlite_create_transfer_table = '''CREATE TABLE IF NOT EXISTS '+date_object+' (
                            sender TEXT NOT NULL,
                            recipient TEXT NOT NULL,
                            ID text NOT NULL,
                            Size NOT NULL,
                            Colour NOT NULL,
                            Quantity INTEGER NOT NULL);'''

answered Mar 5, 2022 at 17:41

Yanis600

911 silver badge9 bronze badges

2 Comments

Ben Keez Over a year ago

That didn't work, the table is just titled '+date_object+' now

Yanis600 Over a year ago

use this sqlite_create_transfer_table = "CREATE TABLE IF NOT EXISTS "+date_object+" ( sender TEXT NOT NULL, recipient TEXT NOT NULL, ID text NOT NULL, Size NOT NULL, Colour NOT NULL, Quantity INTEGER NOT NULL);"

Collectives™ on Stack Overflow

SQLite creating table name using a variable

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related