python sqlite autoincrement in primary column

python sqlite autoincrement in primary column - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: python sqlite autoincrement in primary column (/thread-40519.html)

python sqlite autoincrement in primary column - janeik - Aug-10-2023

hello.
I try to get autoincrement on column markID without success.
I dont want the keyword version of autoincrement.
What am I doing wrong in code?

TABLE:

cur.execute("""CREATE TABLE IF NOT EXISTS "MARKOR"(
    'markID' INT PRIMARY KEY,
    longitude FLOAT NOT NULL,
    latitude FLOAT NOT NULL,
    xcoord INT NOT NULL,
    ycoord INT NOT NULL,
    dato DATE NOT NULL,
    objførstenavn TEXT NOT NULL,
    mellomnavn TEXT,
    objsistenavn TEXT,
    stednavn TEXT NOT NULL,
    "KART_kartID" INT,
    "BILDEKART_bkartID" INT,
    CONSTRAINT "KART_MARKOR"
    FOREIGN KEY ("KART_kartID") REFERENCES "KART" ("kartID"),
    CONSTRAINT "BILDEKART_MARKOR"
    FOREIGN KEY ("BILDEKART_bkartID") REFERENCES "BILDEKART" ("bkartID")
    )""")

DATA/TYPES INSERT AND COMMIT:

item = ('NULL','11.446','50','1200',today,'Per','Roger','Hansen','Østmarksetra','5','7')
    cur.execute("""INSERT OR IGNORE INTO MARKOR VALUES(?,?,?,?,?,?,?,?,?,?,?,?)""",item)
    con.commit()

OUTPUT FROM TERMINAL:
[error]
Output from Python terminal:

     return self.func(*args)
           ^^^^^^^^^^^^^^^^
  File "D:\Python311\Lib\site-packages\customtkinter\windows\widgets\ctk_button.py", line 554, in _clicked
    self._command()
  File "d:\Python311\Scripts\nye.py", line 435, in dbtol
    cur.execute("""INSERT OR IGNORE INTO MARKOR VALUES(?,?,?,?,?,?,?,?,?,?,?,?)""",item)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 12, and there are 11 supplied.   
PS C:\Users\jan-e> & D:/Python311/python.exe d:/Python311/Scripts/nye.py
Exception in Tkinter callback
Traceback (most recent call last):
  File "D:\Python311\Lib\tkinter\__init__.py", line 1948, in __call__
    return self.func(*args)
           ^^^^^^^^^^^^^^^^
  File "D:\Python311\Lib\site-packages\customtkinter\windows\widgets\ctk_button.py", line 554, in _clicked
    self._command()
  File "d:\Python311\Scripts\nye.py", line 435, in dbtol
    cur.execute("""INSERT OR IGNORE INTO MARKOR VALUES(?,?,?,?,?,?,?,?,?,?,?,?)""",item)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 12, and there are 11 supplied. 

SEEN IN DB-browser:
[output]
NULL	60.25	11.446	50	1200	10-08-2023-20-38-12	Per	Roger	Hansen	Østmarksetra	5	7

[/output]
[/error]

RE: python sqlite autoincrement in primary column - menator01 - Aug-10-2023

Not really sure what you mean by keyword version of autoincrement but, to get autoincrement

import sqlite3 as sq

db = sq.connect('mydb.db')

cursor = db.cursor()

cursor.execute('''
               create table if not exists mytable (
               id integer primary key autoincrement,
               name varchar(255)
               )
               ''')
db.commit()

cursor.execute('''
               insert into mytable (name) values ('john')
               ''')
db.commit()

cursor.execute('''
               insert into mytable (name) values ('jane')
               ''')

db.commit()

cursor.execute('''
               select * from mytable
               ''')

print(cursor.fetchall())

output

Output:
[(1, 'john'), (2, 'jane')]

RE: python sqlite autoincrement in primary column - janeik - Aug-10-2023

hi, hanks for input.
Trying to avoid the keyword version in your example.
Think I read something to give no input to the column primary key int, to achieve autoincrement, but dont know how to do that.

(and I will check if commit is proper useed by me)

RE: python sqlite autoincrement in primary column - menator01 - Aug-10-2023

Use integer instead of int in the table creation

import sqlite3 as sq

db = sq.connect('mydb.db')

cursor = db.cursor()

cursor.execute('''
               create table if not exists mytable (
               id integer primary key,
               name varchar(255)
               )
               ''')
db.commit()

alist = ['john', 'jane', 'kid', 'pet']

for name in alist:
    cursor.execute('''
                   insert into mytable (name) values (?)
                   ''', (name,))
db.commit()

cursor.execute('''
               select * from mytable
               ''')

print(*cursor.fetchall())

output

Output:
(1, 'john') (2, 'jane') (3, 'kid') (4, 'pet')

RE: python sqlite autoincrement in primary column - janeik - Aug-13-2023

Thank You, that solved it.
Is there a way of exclude today column regarding primary key topic?
(I want the date time saved in db, but not triggering saving rest of the columns equal content).
Could making today field primary too(toogether with markID), solve that?
I must try that.

def dbtol():
    today = datetime.today().strftime("%d-%m-%Y-%H-%M-%S")
    db = "D:/Python311/Scripts/markers.db"
    con = sqlite3.connect(db)
    cur = con.cursor()
    cur.execute("""DROP TABLE MARKOR""")
    cur.execute("""CREATE TABLE IF NOT EXISTS MARKOR(
    markID INTEGER PRIMARY KEY NOT NULL,
    longitude FLOAT NOT NULL,
    latitude FLOAT NOT NULL,
    xcoord INT NOT NULL,
    ycoord INT NOT NULL,
    today DATE NOT NULL,
    objførstenavn TEXT NOT NULL,
    mellomnavn TEXT,
    objsistenavn TEXT,
    stednavn TEXT NOT NULL,
    "KART_kartID" INTEGER,
    "BILDEKART_bkartID" INT,
    CONSTRAINT "KART_MARKOR"
    FOREIGN KEY ("KART_kartID") REFERENCES "KART" ("kartID"),
    CONSTRAINT "BILDEKART_MARKOR",
    FOREIGN KEY ("BILDEKART_bkartID") REFERENCES "BILDEKART" ("bkartID")
    )""")
    
    items=[62.0,11.3, 200, 100, today, "Ole", "Johan", "Olsen", "BING", 11 , 5]
    cur.execute('INSERT INTO MARKOR (longitude,latitude,xcoord,ycoord,today,objførstenavn,mellomnavn,objsistenavn,stednavn,KART_kartID,BILDEKART_bkartID) VALUES(?,?,?,?,?,?,?,?,?,?,?)',items)
    
    
    con.commit()
    for row in cur.execute('''SELECT * FROM MARKOR'''):
        print(row)
    
    
    con.close()
btndbtool = customtkinter.CTkButton(window, width=20, height=10, font=("Ariel",20,"bold"), border_color="#add19e", fg_color="#aaaaaa",text_color="#000000", text="InitDb",command=dbtol)
btndbtool.place(x=1780,y=1050)


      
window.mainloop()

Output:1	62.0	11.3	200	100	13-08-2023-12-31-24	Ole	Johan	Olsen	BING	11	5
2	62.0	11.3	200	100	13-08-2023-12-40-29	Ole	Johan	Olsen	BING	11	5
3	62.0	11.3	200	100	13-08-2023-12-41-37	Ole	Johan	Olsen	BING	11	5
4	62.0	11.3	200	100	13-08-2023-12-42-41	Ole	Johan	Olsen	BING	11	5
5	62.0	11.3	200	100	13-08-2023-12-43-11	Ole	Johan	Olsen	BING	11	5
6	62.0	11.3	200	100	13-08-2023-12-44-38	Ole	Johan	Olsen	BING	11	5

RE: python sqlite autoincrement in primary column - janeik - Aug-13-2023

i forgot to exclude drop table, did so.
and made today primary key together with markID, not giving expected result,

RE: python sqlite autoincrement in primary column - janeik - Aug-13-2023

Output:1	62.0	11.3	200	100	13-08-2023-12-31-24	Ole	Johan	Olsen	BING	11	5
2	62.0	11.3	200	100	13-08-2023-12-40-29	Ole	Johan	Olsen	BING	11	5
3	62.0	11.3	200	100	13-08-2023-12-41-37	Ole	Johan	Olsen	BING	11	5
4	62.0	11.3	200	100	13-08-2023-12-42-41	Ole	Johan	Olsen	BING	11	5
5	62.0	11.3	200	100	13-08-2023-12-43-11	Ole	Johan	Olsen	BING	11	5
6	62.0	11.3	200	100	13-08-2023-12-44-38	Ole	Johan	Olsen	BING	11	5
7	62.0	11.3	200	100	13-08-2023-13-17-39	Ole	Johan	Olsen	BING	11	5
8	62.0	11.3	200	100	13-08-2023-13-32-37	Ole	Johan	Olsen	BING	11	5
9	62.0	11.3	200	100	13-08-2023-13-33-17	Ole	Johan	Olsen	BING	11	5
10	62.0	11.3	200	100	13-08-2023-13-34-13	Ole	Johan	Olsen	BING	11	5