benchmark for gevent and sqlite3

benchmark.py

import gevent
from gevent import monkey
monkey.patch_all()
import os
import timeit
import sqlalchemy
import sqlalchemy.event
import sqlalchemy.pool
from sqlalchemy import Table, Column, Integer, String, MetaData

try:
    from pysqlite2 import dbapi2
except ImportError:
    from sqlite3 import dbapi2
print dbapi2.sqlite_version_info

if os.path.exists('tt.db'):
    os.remove('tt.db')


def set_sqlite_pragma(dbapi_connection, connection_record):
    dbapi_connection.isolation_level = None
    cursor = dbapi_connection.cursor()
    cursor.execute('PRAGMA journal_mode=wal')
    print cursor.fetchone()
    cursor.execute('PRAGMA synchronous = NORMAL')
    dbapi_connection.commit()
    dbapi_connection.isolation_level = ''


metadata = MetaData()

records = Table(
    'records', metadata,
    Column('id', Integer, primary_key=True),
    Column('code', String)
)

engine = sqlalchemy.create_engine('sqlite:///tt.db', module=dbapi2, poolclass=sqlalchemy.pool.QueuePool)
sqlalchemy.event.listen(engine, 'connect', set_sqlite_pragma)

metadata.create_all(engine)


def block_test(times=100, run_foo=False):
    def ins(n):
        with engine.connect() as conn:
            conn.execute(records.insert().values(code=n))

    def foo():
        for j in range(5):
            print(j)
            gevent.sleep(0.1)

    greens = [gevent.spawn(ins, i) for i in range(times)]
    if run_foo:
        greens.append(gevent.spawn(foo))
    gevent.joinall(greens)


print timeit.Timer(stmt='block_test(times=1000)', setup='from __main__ import block_test').timeit(number=1)
print engine.execute(records.count()).fetchone()
print timeit.Timer(stmt='block_test(times=1000, run_foo=True)', setup='from __main__ import block_test').timeit(number=1)
print engine.execute(records.count()).fetchone()

benchmark2.py

import os
import timeit
import sqlite3

if os.path.exists('tt.db'):
    os.remove('tt.db')
    
conn = sqlite3.connect('tt.db')
conn.execute("""
CREATE TABLE records (
id INTEGER PRIMARY KEY,
code STRING
);
""")
conn.commit()

def block_test(times=100):
    conn = sqlite3.connect('tt.db')
    c = conn.cursor()
    c.execute('PRAGMA journal_mode=wal')
    print c.fetchone()

    def ins(n):
        cursor = conn.cursor()
        cursor.execute('INSERT INTO records(code) VALUES(?)', (n,))
        cursor.close()
        conn.commit()
        
    for i in range(times):
        ins(i)

    conn.close()

print timeit.Timer(stmt='block_test()', setup='from __main__ import block_test').timeit(number=1)
print conn.execute('SELECT count(id) FROM records').fetchone()

bqlite_bench.py

import gevent
from gevent import monkey
monkey.patch_all()
import os
import timeit
from pysqlite2 import dbapi2 as sqlite3

if os.path.exists('tt.db'):
    os.remove('tt.db')
    
conn = sqlite3.connect('tt.db')
if (u'BERKELEY_DB',) in conn.execute('PRAGMA COMPILE_OPTIONS').fetchall():
    print('using berkeley db')
    
conn.execute("""
CREATE TABLE records (
id INTEGER PRIMARY KEY,
code STRING
);
""")
conn.commit()

def block_test(times=1000, run_foo=False):
    conn = sqlite3.connect('tt.db')
    conn.isolation_level = None

    def ins(n):
        cursor = conn.cursor()
        cursor.execute('INSERT INTO records(code) VALUES(?)', (n,))
        cursor.close()
        conn.commit()
    
    def foo():
        for i in range(5):
            print(i)
            gevent.sleep(0.5)

    greens = [gevent.spawn(ins, i) for i in range(times)]
    if run_foo:
        greens.append(gevent.spawn(foo))
    gevent.joinall(greens)
    conn.close()

print timeit.Timer(stmt='block_test()', setup='from __main__ import block_test').timeit(number=1)
print conn.execute('SELECT count(id) FROM records').fetchone()
print timeit.Timer(stmt='block_test(run_foo=True)', setup='from __main__ import block_test').timeit(number=1)
print conn.execute('SELECT count(id) FROM records').fetchone()