How to write data to the database and later get it in the bot?

P

popoff032019-01-13 09:17:51

Python

popoff03, 2019-01-13 09:17:51

Hello. I am learning to work with DB. Chose sqlite3. You need to get data about the user (last name, first name, id, username), write them to the database, and after pressing the button, give him a message. Please tell me how to implement it. Thank you.

Reply

Answer the question

In order to leave comments, you need to log in

2 answer(s)

N

Nick V, 2019-01-13
@half-life

Let's try. Let's omit the business logic of getting user data, it still depends on the implementation of your bot. Let's go from the moment that you already have this data and you need to save it.
See the first thing you need to do is to check whether the table you need exists in the database.
If not, then create it.
Create a module, let's say db
In it, create a file, let 's say utils.py with the following content:

utils.py

from sqlite3 import Connection as SqliteConnection
from sqlite3 import Cursor as SqliteCursor

sql = """
pragma foreign_keys = on;
create table if not exists users
(
  id          integer primary key autoincrement,
  internal_id text not null,
  first_name  text not null,
  last_name   text not null,
  username    text not null unique,
  created     date not null default current_date
);
create unique index if not exists users_id_uindex
  on users (id);
create unique index if not exists users_internal_id_uindex
  on users (internal_id);
create index if not exists users_first_name_index
  on users (created);
create index if not exists users_last_name_index
  on users (created);
create unique index if not exists users_username_uindex
  on users (username);
create index if not exists users_created_index
  on users (created);
"""


def check_db(db_connect: SqliteConnection,
             db_cursor: SqliteCursor) -> None:
    db_cursor.executescript(sql)
    db_connect.commit()

You create another file in the db module with the name queries.py
In it, for convenience, you describe queries to the database.
You need 2 requests:
1) To save data
2 To receive data
We do:

queries.py

from sqlite3 import Connection as SqliteConnection
from sqlite3 import Cursor as SqliteCursor
from typing import List

def insert_user(db_cursor: SqliteCursor,
                db_connect: SqliteConnection,
                internal_id: str, username: str,
                first_name: str, last_name: str) -> None:
    db_cursor.execute("""
            insert into users(internal_id, username, first_name, last_name)
            select :internal_id, :username, :first_name, :last_name
            where not exists(select 1 from users where internal_id = :internal_id and username = :username)
        """, {
        'internal_id': internal_id,
        'username': username,
        'first_name': first_name,
        'last_name': last_name,
    })
    db_connect.commit()

def select_user(db_connect: SqliteConnection,
                username: str) -> List[tuple] or None:
    result = db_connect.execute(
        f"""select *
            from users
            where username = :username""",
        {'username': username},
    ).fetchone()

    return result

Next, in the place where you need to save the data, do something like this:

somewhere _in_your_code

from contextlib import closing
import sqlite3

from db.utils import check_db
from db.queries import insert_user

with closing(sqlite3.connect('db_name')) as db_connect:
        with closing(db_connect.cursor()) as db_cursor:
            check_db(db_connect, db_cursor)  # проверяем есть ли таблица в базе, если нет то создаём её
            insert_user(db_cursor, db_connect, 'internal_id', 'username', 'first_name', 'last_name')  # если в базе ещё нет такого юзера с internal_id и username то он создастся

And in the place where you need to get the data is something like this:

somewhere _in_your_code

from contextlib import closing
import sqlite3

from db.utils import check_db
from db.queries import select_user


with closing(sqlite3.connect('db_name')) as db_connect:
        with closing(db_connect.cursor()) as db_cursor:
            check_db(db_connect, db_cursor)  # проверяем есть ли таблица в базе, если нет то создаём её
            user = select_user(db_connect, 'username')

That's like everything.
Py.Sy. The code was written in a text editor, maybe I missed something.
Yes, and use the dock , do not be shy

H

hackyoupeople, 2019-01-13
@hackyoupeople

I hope I understood your question correctly?
https://ruseller.com/lessons.php?id=2277&rub=34