I think this is a rookie mistake. I tried several hours but couldn't find a solution.
I heard that SQLite supports multithreaded access to a file. So I created my first database with multiple tables in Python:
def create_table(page):
con = connect('emails.db')
cur = con.cursor()
try:
cur.executescript(f'''
CREATE TABLE {page} (
id INTEGER,
email VARCHAR,
password VARCHAR,
time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
''')
con.commit()
except:
pass
con.close()
Now to my problem what I want to do:
- Move first row of table e. g.
fremdgehen
tofremdgehen_used
- do something
- Delete first row of
fremdgehen
- repeat
What my program does:
- Move first row of
fremdgehen
tofremdgehen_used
- Do something
It's not deleting the first row of "fremdgehen" and only moves one row to "fremdgehen_used", instead it gives following error:
Exception in thread
Thread-1reifer6:
Traceback (most recent call last):
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\threading.py", line 932, in _bootstrap_inner
self.run()
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\threading.py", line 870, in run
self._target(*self._args, **self._kwargs)
File "c:\Users\Administrator\Desktop\de_1\v2\sub_bot\main.py", line 92, in main
delete_table_row(page)
File "c:\Users\Administrator\Desktop\de_1\main_bot\data.py", line 62, in delete_table_row
cur.execute(f'DELETE FROM {page} ORDER BY id LIMIT 1')
sqlite3.OperationalError: near "ORDER": syntax error
*This message three times because three threads are running
My Code:
Move first row of table e. g. fremdgehen
to fremdgehen_used
def move_table(page):
# page is "fremdgehen"
con = connect('emails.db')
cur = con.cursor()
cur.execute(f'INSERT INTO {page}_used SELECT * FROM {page} LIMIT 1')
con.commit()
con.close()
Do something
Delete first row of fremdgehen
def delete_table_row(page):
# page is "fremdgehen"
con = connect('emails.db')
cur = con.cursor()
cur.execute(f'DELETE FROM {page} ORDER BY id LIMIT 1')
con.commit()
con.close()
CodePudding user response:
I believe that the query to delete the first line is wrong. Maybe this would work out better:
Delete from fremdgehen where id IN (Select id from fremdgehen limit 1);
Stolen from: https://stackoverflow.com/a/10381812/10110813