is there any ways to backup sqlite db in python?

Question

i'm using sqlite3 for storing my telegram bot's users and every time when i deploy or run my bot my db restarts and after inserting some data i try to copy db file but i still has old data here is my code

i've tried to use backup method but it is didn't work or too slow

connection = sqlite3.connect('users.db')

def backup(conn):
    new_db = sqlite3.connect('users.db')
    conn.backup(new_db)
    new_db.close()


backup(connection)

bot code


connection = sqlite3.connect('users.db')
cursor = connection.cursor()
cursor.execute(
    'CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, chat_id INTEGER, name VARCHAR(255))')


@dp.message_handler(commands=['start'])
async def send_welcome(message: Message):
    chat_id = message.chat.id
    user_name = message.from_user.first_name
    users = cursor.execute('SELECT count(chat_id) FROM users WHERE chat_id = :chat_id', {'chat_id': chat_id})
    is_user = list(users.fetchall()[0])[0]
    if not is_user:
        cursor.execute("INSERT INTO  users (id, chat_id, name)  VALUES (NULL, ?, ?)", (chat_id, user_name))
        connection.commit()
    connection.close()
    await bot.send_message(chat_id, f'Hello {user_name}, this bot helps you to download media files from '
                                    f'social medias such as *tiktok, instagram, youtube, pinterest*',
                           'markdownv2')




admins = [679679313]

@dp.message_handler(commands=['stat'])
async def send_message(message: Message):
    chat_id = message.chat.id
    x = datetime.now()
    date = x.strftime("%B %d, %Y %H:%M:%S")
    users = cursor.execute('SELECT count(*) FROM users')
    if chat_id in admins:
        await bot.send_message(chat_id, f"""Bot Statistics
 Users : {users.fetchall()[0][0]}
️ {date}""")


@dp.message_handler(commands=['backup'])
async def send_message(message: Message):
    chat_id = message.chat.id
    if chat_id in admins:
        await bot.send_document(chat_id, open('users.db', "rb"))

Sqlite databases are just files. Copy the file if you want a backup. Unclear why you need a connection instance — OneCricketeer, Jul 30 '22 at 18:14
i tried but afer i copied my db it still has old data when i inserted before deployimg my bot. i need db for collect my users and sending messages to them — Jakhongir Ganiev, Jul 30 '22 at 18:16
@JakhongirGaniev You probably have to flush all the writes to the db before backing it up. — Ibolit, Jul 30 '22 at 18:18
From the code shown `conn.backup(new_db)` is trying to backup into the exact same file. You've given the same filename to two connections — OneCricketeer, Jul 30 '22 at 18:20

score 0 · Accepted Answer · answered Jul 31 '22 at 22:25

0

Since the .db is just a file, you can use python with import os to just make a copy of said file.

answered Jul 31 '22 at 22:25

Andrew Salinas

54
4

score 0 · Answer 2 · answered May 08 '23 at 01:37

0

As mentioned in How to backup sqlite database?, you can find the Sqlite backup API at https://www.sqlite.org/backup.html

answered May 08 '23 at 01:37

sjb-sjb

1,112
6
14

is there any ways to backup sqlite db in python?

2 Answers2