import sqlite3

conn = sqlite3.connect("users.db")
c = conn.cursor()

c.execute("CREATE TABLE user (name text, age integer)")

c.execute("INSERT INTO user VALUES ('User A', 42)")
c.execute("INSERT INTO user VALUES ('User B', 43)")

conn.commit()

c.execute("SELECT * FROM user")
print(c.fetchall())

conn.close()

[(u'User A', 42), (u'User B', 43)]

>>> import sqlite3

>>> conn = sqlite3.connect('users.db')

Alternatively, you can also supply the special name `:memory:` to create a temporary database in RAM, as follows:

>>> conn = sqlite3.connect(':memory:')

##

   c = conn.cursor()

   # Create table
   c.execute('''CREATE TABLE stocks
               (date text, trans text, symbol text, qty real, price real)''')
  
   # Insert a row of data
   c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
  
   # Save (commit) the changes
   conn.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.
   conn.close()

You can change this attribute to a callable that accepts the cursor and the original row as a tuple and will return the real result row.

   def dict_factory(cursor, row):
       d = {}
       for i, col in enumerate(cursor.description):
           d[col[0]] = row[i]
       return d

   conn = sqlite3.connect(":memory:")
   conn.row_factory = dict_factory

Executes a _single_ SQL statement. The SQL statement may be parametrized (i. e. placeholders instead of SQL literals). 
The sqlite3 module supports two kinds of placeholders: question marks `?` (“qmark style”) and named placeholders `:name` (“named style”).

   import sqlite3
   conn = sqlite3.connect(":memory:")
   cur = conn.cursor()
   cur.execute("create table people (name, age)")

   who = "Sophia"
   age = 37
   # This is the qmark style:
   cur.execute("insert into people values (?, ?)",
               (who, age))

   # And this is the named style:
   cur.execute("select * from people where name=:who and age=:age",
               {"who": who, "age": age})  # the keys correspond to the placeholders in SQL

   print(cur.fetchone())

Executes an SQL command against all parameter sequences or mappings found in the sequence sql. The sqlite3 module also allows using an iterator yielding parameters instead of a sequence.

   L = [(1, 'abcd', 'dfj', 300),    # A list of tuples to be inserted into the database
        (2, 'cfgd', 'dyfj', 400),
        (3, 'sdd', 'dfjh', 300.50)]                           

   conn = sqlite3.connect("test1.db")
   conn.execute("create table if not exists book (id int, name text, author text, price real)")
   conn.executemany("insert into book values (?, ?, ?, ?)", L)

   for row in conn.execute("select * from book"):
       print(row)

You can also pass iterator objects as a parameter to executemany, and the function will iterate over the each tuple of values that the iterator returns. The iterator must return a tuple of values.

   import sqlite3

   class IterChars:
       def __init__(self):
           self.count = ord('a')

       def __iter__(self):
           return self

       def __next__(self):            # (use next(self) for Python 2)
           if self.count > ord('z'):
               raise StopIteration
           self.count += 1
           return (chr(self.count - 1),) 

   conn = sqlite3.connect("abc.db")
   cur = conn.cursor()
   cur.execute("create table characters(c)")

   theIter = IterChars()
   cur.executemany("insert into characters(c) values (?)", theIter)

   rows = cur.execute("select c from characters")
   for row in rows:
       print(row[0]),

This is a nonstandard convenience method for executing multiple SQL statements at once. It issues a `COMMIT` statement first, then executes the SQL script it gets as a parameter.

`sql_script` can be an instance of `str` or `bytes`.

import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.executescript("""
     create table person(
         firstname,
         lastname,
         age
     );

     create table book(
         title,
         author,
         published
     );

     insert into book(title, author, published)
     values (
         'Dirk Gently''s Holistic Detective Agency',
         'Douglas Adams',
         1987
     );
     """)

The next set of functions are used in conjunction with `SELECT` statements in SQL. To retrieve data after executing a `SELECT` statement, you can either treat the cursor as an iterator, call the cursor’s `fetchone()` method to retrieve a single matching row, or call `fetchall()` to get a list of the matching rows.

Example of the iterator form:

import sqlite3
stocks = [('2006-01-05', 'BUY', 'RHAT', 100, 35.14),
          ('2006-03-28', 'BUY', 'IBM', 1000, 45.0),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.0),
          ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)]
conn = sqlite3.connect(":memory:")
conn.execute("create table stocks (date text, buysell text, symb text, amount int, price real)")
conn.executemany("insert into stocks values (?, ?, ?, ?, ?)", stocks)    
cur = conn.cursor()

for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
    print(row)

# Output:
# ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
# ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
# ('2006-04-06', 'SELL', 'IBM', 500, 53.0)
# ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)

Fetches the next row of a query result set, returning a single sequence, or None when no more data is available. 
 
   cur.execute('SELECT * FROM stocks ORDER BY price')
   i = cur.fetchone()
   while(i): 
       print(i)
       i = cur.fetchone()

   # Output:
   # ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
   # ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
   # ('2006-04-06', 'SELL', 'IBM', 500, 53.0)
   # ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)

cur.execute('SELECT * FROM stocks ORDER BY price')
print(cur.fetchmany(2))

# Output:    
# [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)]

cur.execute('SELECT * FROM stocks ORDER BY price')
print(cur.fetchall())

# Output:
# [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0), ('2006-04-06', 'SELL', 'IBM', 500, 53.0), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)]

None     <->     NULL
int      <->     INTEGER/INT
float    <->     REAL/FLOAT
str      <->     TEXT/VARCHAR(n)
bytes    <->     BLOB