2

Here's my code:

val = str(datetime.datetime.now().strftime("%y-%m-%d"))
print(val)

def export():
    cursor = conn.cursor()
    cursor.execute("ALTER TABLE TRACKER ADD %s INTEGER" % val)

    conn.commit()

When I run the code, it prints "17-02-14," but when I hit the export button I get the error "Incorrect syntax near '17'"

The syntax for adding a column was working fine with other variables. It just isn't working with the date. I even cast the date as a string but for some reason that didn't fix it.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
user3364161
  • 365
  • 5
  • 21
  • Are you sure you want to create a column named 17-02-14? Or should the column name be 'timestamp' or something alike? The error is because SQL table names cannot have hyphens with proper escape characters in-place (that's calling for trouble unless you absolutely need it). – Sharad Feb 15 '17 at 03:54
  • Ah, I didn't know that. I want the column title to be the date so I can refer to it in the future. What's a good way of doing that? – user3364161 Feb 15 '17 at 03:59
  • You may use underscores instead of hyphen. Alternatively, use yyyymmdd format for the column name. – Sharad Feb 15 '17 at 04:01
  • What's your use-case for the column title to be a specific date. I haven't seen any DB schemas designed like that because every 24 hours you'll have a new date and your table will run out of columns in a short time . (or at least become un-manageable). I'd recommend adding a 'date' column in the DB table and adding rows against it. A NOSQL database like MongoDB may be a good choice too if your number of columns is varying. – Sharad Feb 15 '17 at 04:04
  • I got the same error with underscores, and strangely enough I got the same error even without dashes or underscores. "Incorrect syntax near '170214'" without dashes. Is there another option? – user3364161 Feb 15 '17 at 04:05
  • Every week it's updated. There won't be any duplicates. Edit: It's to track Elos over time for a club that meets once a week. Once a week I'll update it, and I can see the player's progression over time. – user3364161 Feb 15 '17 at 04:06
  • 1
    The column name itself should be a string (it's a name). Guess there's might be restrictions in using an all integer column name. 1. Can you try adding an alphabet as a prefix - say d_20141217? 2. Can you try enclosing the column name with double quotes "20141217"? – Sharad Feb 15 '17 at 04:10
  • This may offer some help; http://stackoverflow.com/questions/7975417/can-a-number-be-used-to-name-a-mysql-table-column – Sharad Feb 15 '17 at 04:11
  • Well that worked. I have a column called a17_02_14 now. I'll fiddle with the idea some more but I think my question was answered. – user3364161 Feb 15 '17 at 04:11

1 Answers1

2

You were trying to create a column with a name like 17-02-15, but you were not successful because column names containing spaces or "funny characters" need to be quoted. The following example uses SQLite which accepts the ANSI standard double-quote ("column name"), but other DBMSs may use other characters, e.g., MySQL tends to use backquotes (`column name`) and Microsoft SQL Server usually uses square brackets ([column name]).

Test code:

import datetime
import sqlite3

conn = sqlite3.connect(':memory:')
crsr = conn.cursor()

sql = 'CREATE TABLE TRACKER (id INT PRIMARY KEY)'
print(sql)
crsr.execute(sql)
conn.commit()
print("Test table created.")
print("")

val = str(datetime.datetime.now().strftime("%y-%m-%d"))

sql = 'ALTER TABLE TRACKER ADD %s INTEGER' % val
print(sql)
try:
    crsr.execute(sql)
    conn.commit()
    print("Column added.")
except Exception as ex:
    print(repr(ex))
print("")

# now try with quotes around the column name
sql = 'ALTER TABLE TRACKER ADD "%s" INTEGER' % val
print(sql)
try:
    crsr.execute(sql)
    conn.commit()
    print("Column added.")
except Exception as ex:
    print(repr(ex))
print("")

sql = 'SELECT * FROM TRACKER'
crsr.execute(sql)
print("Current column names:")
print([x[0] for x in crsr.description])

crsr.close()
conn.close()

Console output:

CREATE TABLE TRACKER (id INT PRIMARY KEY)
Test table created.

ALTER TABLE TRACKER ADD 17-02-15 INTEGER
OperationalError('near "17": syntax error',)

ALTER TABLE TRACKER ADD "17-02-15" INTEGER
Column added.

Current column names:
['id', '17-02-15']

Note also that different DBMSs may have other restrictions on column names, e.g., some may insist that a column name not begin with a numeric digit.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418