Advanced sqlite3 Python Tutorial

#1. create a memory-based database

```
import sqlite3
conn = sqlite3.connect(':memory:')
```

#2. create a regular expression function inside of SQL

```
import re
def regular_expression(expr, item):
    reg = re.compile(expr)
    return reg.search(item) is not None
conn.create_function("REGEXP", 2, regular_expression) # 2 here means two parameters. REGEXP is a fixed value
```

#3. create a table, and insert two values to it

```
cursor = conn.cursor()
cursor.execute('CREATE TABLE aTable (aColumn TEXT)')
cursor.executemany(
    'INSERT INTO aTable (aColumn) VALUES (?)',
    [('aaa"fuckingA"',),('bbb"fuckingB"',), ('just"AA"',)])
```

#4. select a column from a table by re

```
cursor.execute('SELECT aColumn FROM aTable WHERE aColumn REGEXP (?)',[r'\w+"\w+B"'])
data=cursor.fetchall()
print(data)
print()
```

#5. select rows from a table by re

```
for row in cursor.execute('SELECT * FROM aTable WHERE aColumn REGEXP (?)', [r'\w+"\w+A"']):
    print(row)
```

#6. check if rows exist in conditions

```
cursor.execute(''' SELECT * FROM aTable WHERE aColumn='bbb"fuckingB"' AND aColumn='bbb"fuckingB"' ''')

#cursor.execute(''' SELECT * FROM aTable WHERE aColumn=:key AND aColumn=:key2 ''', {"key": "value", "key2": "whatever"})

if len(cursor.fetchall()): {
    print('Table exists.')
}
```