SQLite creating table name using a variable-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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