CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
datetime_of_added DATETIME,
telegram_id VARCHAR(255),
datetime_of_logging DATETIME,
date_of_birth DATE,
img TEXT,
admin BOOLEAN DEFAULT(FALSE)
);SELECT
p.name,
i1.price AS price1,
i2.price AS price2
FROM
relations r
LEFT JOIN product p ON r.id_product = p.id
LEFT JOIN item_1 i1 ON i1.id = r.item_1_id
LEFT JOIN item_2 i2 ON i2.id = r.item_2_id
WHERE
i1.price >= 125
AND i2.price >= 200;SELECT
@ids := (
SELECT
GROUP_CONCAT(DISTINCT id SEPARATOR ',')
FROM
(
SELECT
id
FROM
test
WHERE
date >= '2022-12-10 08:00:00'
AND date <= '2022-12-10 20:00:00'
ORDER BY
date DESC
LIMIT
2
) AS sub
);
SELECT @ids;
SELECT
SUM(bunker_sum)
FROM
test
WHERE
find_in_set(id, @ids);WHERE id_sender = $sender AND id_recipient = $to.
import sqlite3
blob = bytearray(b'{"param1":"1","param2":"2"}')
with sqlite3.connect('np.db') as connection:
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS t_info_t (blob_Value BLOB )")
cursor.execute("INSERT OR IGNORE INTO t_info_t (blob_Value) values (?)", (blob,))
cursor.execute("SELECT * FROM t_info_t")
data = cursor.fetchall()
print(data)
print(type(data[0][0]))
out = data[0][0].decode()
print(out)
print(type(out))SELECT id as refer, count(ref_id) as refers_count
FROM users
WHERE
ref_id is not null
GROUP BY ref_id
ORDER BY 2 DESC LIMIT 5;SELECT * FROM new_table
WHERE
link_id NOT IN (SELECT DISTINCT(link_id) FROM new_table WHERE flag=0);Select * from table where priceFrom < 180000;
Select * from table where priceForm < 180000 and priceForm != 150000;import sqlite3
from sqlite3 import Error
def post_sql_query(sql_query):
with sqlite3.connect(database) as connection:
cursor = connection.cursor()
cursor.execute(sql_query)
result = cursor.fetchall()
return result
def create_tables():
users_query = '''CREATE TABLE IF NOT EXISTS USERS
(user_id INTEGER PRIMARY KEY NOT NULL, fullname TEXT, birthday TEXT);'''
order_query = '''CREATE TABLE IF NOT EXISTS ORDERS
(order_id INTEGER PRIMARY KEY NOT NULL, user_id INT, order_date TEXT);'''
post_sql_query(users_query)
post_sql_query(order_query)
database = 'MyDataBase.db'
create_tables()
# пример для первой строки
user_insert_query = "INSERT INTO users(fullname,birthday) VALUES('Сидоров Иван Петрович', '01.02.1970');"
order_insert_query = "INSERT INTO orders(user_id,order_date) VALUES(2,'10.03.2020');"
post_sql_query(user_insert_query)
post_sql_query(order_insert_query)user_without_orders_query = 'SELECT fullname FROM users WHERE user_id NOT IN (SELECT user_id FROM orders);'
U = post_sql_query(user_without_orders_query)
print(U)root@a:~# sqlite3
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE table1(id INT PRIMARY KEY AUTOINCREMENT, fld1 INTEGER , fld2 INTEGER, fld3 INTEGER);
Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
sqlite> CREATE TABLE table1(id INTEGER PRIMARY KEY AUTOINCREMENT, fld1 INTEGER , fld2 INTEGER, fld3 INTEGER);
sqlite> CREATE TABLE table2(id INTEGER PRIMARY KEY AUTOINCREMENT, fld1 INTEGER , fld2 INTEGER, fld3 INTEGER);
sqlite> INSERT INTO table1(fld1, fld2, fld3) VALUES(15, 10, 5);
sqlite> INSERT INTO table1(fld1, fld2, fld3) VALUES(3, 6, 9);
sqlite> INSERT INTO table2(fld1, fld2, fld3) VALUES(5, 10, 15);
sqlite> INSERT INTO table2(fld1, fld2, fld3) VALUES(4, 8, 12);
sqlite> SELECT * FROM table1;
1|15|10|5
2|3|6|9
sqlite> SELECT * FROM table2;
1|5|10|15
2|4|8|12
sqlite> SELECT (table1.fld1 * table2.fld1)
...> FROM table1
...> INNER JOIN TABLE2 ON table1.id=table2.id;
75
12
sqlite> SELECT (table1.fld2 * table2.fld2)
...> FROM table1
...> INNER JOIN TABLE2 ON table1.id=table2.id;
100
48
sqlite> SELECT (table1.fld1 * table2.fld1), (table1.fld2 * table2.fld2), (table1.fld3 * table2.fld3)
...> FROM table1
...> INNER JOIN TABLE2 ON table1.id=table2.id;
75|100|75
12|48|108
sqlite>with db.connect(db) as connection:
cursor = connection.cursor()
cursor.execute('''CREATE TABLE USERS (
user_id TEXT PRIMARY key,
username TEXT,
first_name TEXT,
last_name TEXT);'''def add_user_info(user):
with db.connect(db) as connection:
cursor = connection.cursor()
try:
cursor.execute('''INSERT INTO USERS (user_id, username, first_name, last_name)
VALUES(?,?,?,?);''', user)
except Error:
pass