Posts: 49
Threads: 16
Joined: Jun 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]
Posts: 972
Threads: 104
Joined: Sep 2019
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')]
Posts: 49
Threads: 16
Joined: Jun 2023
Aug-10-2023, 07:30 PM
(This post was last modified: Aug-10-2023, 07:30 PM by janeik.)
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)
Posts: 972
Threads: 104
Joined: Sep 2019
Aug-10-2023, 07:37 PM
(This post was last modified: Aug-10-2023, 07:37 PM by menator01.)
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')
Posts: 49
Threads: 16
Joined: Jun 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
Posts: 49
Threads: 16
Joined: Jun 2023
i forgot to exclude drop table, did so.
and made today primary key together with markID, not giving expected result,
Posts: 49
Threads: 16
Joined: Jun 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
|
