551 - 600 Flashcards
SQL.CASE
expression to add the conditional logic to a query. The SQLite CASE expression evaluates a list of conditions and returns an expression based on the result of the evaluation.
The CASE expression is similar to the IF-THEN-ELSE statement in other programming languages.
CASE case_expression
WHEN when_expression_1 THEN result_1
WHEN when_expression_2 THEN result_2
...
[ ELSE result_else ]
END
SELECT customerid, firstname, lastname,
CASE country
WHEN 'USA'
THEN 'Domestic'
ELSE 'Foreign'
END CustomerGroup
FROM
customers
ORDER BY
LastName,
FirstName;
CASE
WHEN bool_expression_1 THEN result_1
WHEN bool_expression_2 THEN result_2
[ ELSE result_else ]
END
MySQL.RLIKE
is used to performs a pattern match of a string expression against a pattern.
SELECT * FROM Employee WHERE Last_name RLIKE '^S' ;
SELECT * FROM Employee WHERE First_name RLIKE 'i$' ;
MySQL.LENGTH()
is used to find the string length which is of type bytes.
SELECT LENGTH(item) FROM package099;
SELECT LENGTH(float_val) FROM float061;
SELECT LENGTH(mrp+sp) FROM package72;
SQL.datetime
function to manipulate datetime values. The function accepts a time string and one or more modifiers.
SELECT datetime('now','-1 day','localtime');
SELECT datetime('now','localtime');
CREATE TABLE referrals(
id INTEGER PRIMARY KEY,
source TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
SQL.TIME()
function extracts the time part from a given time/datetime. Note: This function returns “00:00:00” if expression is not a datetime/time, or NULL if
expression is NULL.
SELECT *, (julianday(ShippedDate) - julianday(OrderDate)) as delivery_time FROM Orders ORDER BY delivery_time
SELECT TIME("19:30:10");
SELECT TIME("2017-08-15 19:30:10");
SELECT TIME("2017-08-15 19:30:10.000001");
SELECT TIME(NULL);
sqlite3.cursor()
It is an object that is used to make the connection for executing SQL queries. It acts as middleware between SQLite database connection and SQL query. It is created after giving connection to SQLite database.
import sqlite3
connect = sqlite3.connect(":memory:")
connect = con.cursor()
connect.connection == connect
# True
def char_generator():
for c in string.ascii_lowercase:
yield (c,)
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")
cur.executemany("insert into characters(c) values (?)", char_generator())
cur.execute("select c from characters")
print(cur.fetchall())
con.close()
con = sqlite3.connect(":memory:")
cur = con.cursor()
sql = "create table people (name_last, age)"
cur.execute(sql)
who = "Yeltsin"
age = 72
стиль qmark:
sql = "insert into people values (?, ?)"
cur.execute(sql, (who, age))
именованный стиль:
sql = "select * from people where name_last=:who and age=:age"
cur.execute(sql, {"who": who, "age": age})
print(cur.fetchone())
con.close()
sqlite3.fetchall() and sqlite3.fetchmany() and sqlite3.fetchone()
возвращаются одна или несколько строк.
sqlite3.fetchmany([size=cursor.arraysize]) — можно указывать, какое количество строк возвращается. По умолчанию параметр size равен значению cursor.arraysiz
conn = sqlite3.connect('movies.sqlite').cursor()
conn.execute("""SELECT COUNT(id) FROM directors""").fetchall()
connection = sqlite3.connect('sw_inventory.db')
cursor = connection.cursor()
cursor.execute('select * from switch')
sqlite3.Cursor at 0x104eda810>
cursor.fetchone()
Out[20]: ('0000.AAAA.CCCC', 'sw1', 'Cisco 3750', 'London, Green Str')
con = sqlite3.connect(":memory:")
con.execute("""
select * from pragma_compile_options
where compile_options like 'THREADSAFE=%'
""").fetchall()
cursor.execute('select * from switch')
from pprint import pprint
while True:
three_rows = cursor.fetchmany(3)
if three_rows:
pprint(three_rows)
else:
break
sqlite3.Row
пытается имитировать кортеж в большинстве своих функций. Поддерживает доступ к результату запроса как к словарю, где ключ это имя столбца. Так же поддерживает обращение к столбцу по индексу, итерацию по строкам запроса, проверку на равенство и встроенную функцию len() для подсчета количества строк запроса. Если два объекта подряд имеют одинаковые столбцы, а их элементы равны, то эти объекты считаются равными.
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute('select * from stocks')
r = cursor.fetchone()
type(r)
👉 <class 'sqlite3.Row'>
tuple(r)
👉 ('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
len(r)
👉 5
r[2]
👉 'RHAT'
r.keys()
👉 ['date', 'trans', 'symbol', 'qty', 'price']
r['qty']
👉 100.0
>>> for member in r:
... print(member)
👉 2006-01-05
👉 BUY
👉 RHAT
👉 100.0
👉 35.14
sqlite3.cursor.keys
возвращает список имен столбцов. Сразу после запроса, это первый элемент каждого кортежа в Cursor.description.
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute('select * from stocks')
r = cursor.fetchone()
type(r)
👉 <class 'sqlite3.Row'>
tuple(r)
👉 ('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
len(r)
👉 5
r[2]
👉 'RHAT'
r.keys()
👉 ['date', 'trans', 'symbol', 'qty', 'price']
r['qty']
👉 100.0
>>> for member in r:
... print(member)
👉 2006-01-05
👉 BUY
👉 RHAT
👉 100.0
👉 35.14
sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])
открывает соединение с файлом базы данных SQLite database. По умолчанию возвращает объект Connection, если не указана пользовательская фабрика. Если базы по пути нет, то будет созданна база в указоном пути с указаным именем.
- database - путь к файлу с базой данных,
- timeout - тайм-аут блокировки базы при изменении,
- detect_types - обнаружения типов,
- isolation_level - уровень изоляции,
- check_same_thread - использование нескольких потоков,
- factory - пользовательский класс Connection,
- cached_statements - кэширование инструкций,
- uri - интерпретировать подключение как URI.
- sqlite3.connect(“:memory:”) - что бы использовать в памяти(будет уничтожена сразу после закрытия программы
import sqlite3
con = sqlite3.connect('example.db')
cur = con.cursor()
con = sqlite3.connect(":memory:")
con.isolation_level = None
cur = con.cursor()
sqlite3.row_factory()
change this attribute to a callable that accepts the cursor and the original row as a tuple and will return the real result row. This way, you can implement more advanced ways of returning results, such as returning an object that can also access columns by name.
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print(cur.fetchone()["a"])
con.close()
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("select 'John' as name, 42 as age")
for row in cur:
assert row[0] == row["name"]
assert row["name"] == row["nAmE"]
assert row[1] == row["age"]
assert row[1] == row["AgE"]
con.close()
SQL.WHERE
clause is used to filter records. It is used to extract only those records that fulfill a specified condition.
SELECT column1, column2, ... FROM table_name WHERE condition;
SELECT * FROM Customers WHERE Country='Mexico';
SELECT * FROM Customers WHERE CustomerID=1;
SELECT directors.name, COUNT(*) movie_count FROM movies JOIN directors ON movies.director_id = directors.id WHERE movies.genres = ? GROUP BY directors.name ORDER BY movie_count DESC, directors.name LIMIT 5
SQL.LIKE
command is used in a WHERE clause to search for a specified pattern in a column.
You can use two wildcards with LIKE:
% - Represents zero, one, or multiple characters
_ - Represents a single character (MS Access uses a question mark (?) instead)
UPPER(title) LIKE '% LOVE''%'
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
SELECT * FROM Customers WHERE CustomerName LIKE '%a';
SELECT * FROM Customers WHERE CustomerName LIKE '%or%';
The following SQL statement selects all customers with a CustomerName that starts with "a" and are at least 3 characters in length: SELECT * FROM Customers WHERE CustomerName LIKE 'a\_\_%';
sqlite3.execute(sql, parameters=(), /)
Execute an SQL statement. Values may be bound to the statement using placeholders.
conn = sqlite3.connect('movies.sqlite').cursor()
conn.execute("""SELECT COUNT(id) FROM directors""").fetchall()
keyword = "Love"
query = """
SELECT * FROM tracks
JOIN albums on albums.id = tracks.id
WHERE tracks.name Like ?
"""
c.execute(query, (f"%{keyword}%", )) # <- Will replace "?" in the query with %keyword%
rows = c.fetchall()
cur = con.cursor()
# Create table
cur.execute('''CREATE TABLE stocks
(date text, trans text, symbol text, qty real, price real)''')
Insert a row of data
cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
Save (commit) the changes
con.commit()
We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
con.close()
def md5sum(t):
return hashlib.md5(t).hexdigest()
con = sqlite3.connect(":memory:")
con.create_function("md5", 1, md5sum)
cur = con.cursor()
cur.execute("select md5(?)", (b"foo",))
print(cur.fetchone()[0])
con.close()
sqlite3.commit()
Commit any pending transaction to the database. If there is no open transaction, this method is a no-op.
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute('''create table stocks (date text, trans text, symbol text, qty real, price real)''')
cur.execute("""insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)""")
con.commit()
cur.close()
sqlite3.close()
Close the database connection. Any pending transaction is not committed implicitly; make sure to commit() before closing to avoid losing pending changes.
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print(cur.fetchone()["a"])
con.close()
sqlite3.executemany()
prepares a database operation (query or command) and executes it against all parameter sequences or mappings found in the sequence.
def collate_reverse(string1, string2):
if string1 == string2:
return 0
elif string1 < string2:
return 1
else:
return -1
con = sqlite3.connect(":memory:")
con.create_collation("reverse", collate_reverse)
cur = con.cursor()
cur.execute("create table test(x)")
cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])
cur.execute("select x from test order by x collate reverse")
for row in cur:
print(row)
con.close()
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table lang (name, first_appeared)")
This is the qmark style:
cur.execute("insert into lang values (?, ?)", ("C", 1972))
The qmark style used with executemany():
lang_list = [("Fortran", 1957),
("Python", 1991),
("Go", 2009),
]
cur.executemany("insert into lang values (?, ?)", lang_list)
And this is the named style:
cur.execute("select * from lang where first_appeared=:year", {"year": 1972})
print(cur.fetchall())
con.close()
SQL.UPPER
function converts a string to upper-case.
SELECT UPPER('SQL Tutorial is FUN!');
SELECT UPPER(CustomerName) AS UppercaseCustomerName FROM Customers;
SQL.VALUES
command specifies the values of an INSERT INTO statement.
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
SQL.LOWER
function converts a string to lower-case.
SELECT LOWER('SQL Tutorial is FUN!');
SELECT LOWER(CustomerName) AS LowercaseCustomerName FROM Customers;
