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 to fremdgehen_used
• do something
• Delete first row of fremdgehen
• repeat

What my program does:

• Move first row of fremdgehen to fremdgehen_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