Python‎ > ‎

sqlite3

http://www.adilwaseem.com/

Definition
Sqlite3 is a built-in database in Python. import sqlite3 syntax is required to import the module in the Shell editor.

Methods 
  • __call__(self, /, *args, **kwargs)--- Call self as a function.
  •  __enter__(...) --  For context manager. Non-standard.
  •  __exit__(...) -- For context manager. Non-standard.
  •  __init__(self, /, *args, **kwargs) -- Initialize self. See help(type(self)) for accurate signature.
  •  __new__(*args, **kwargs) from builtins.type --  Create and return a new object. See help(type) for accurate signature.
  •  close(...) --  Closes the connection.
  •  commit(...) --  Commit the current transaction.
  • create_aggregate(...) -- Creates a new aggregate. Non-standard.
  • create_collation(...) -- Creates a collation function. Non-standard.
  • create_function(...) -- Creates a new function. Non-standard.
  • cursor(...) -- Return a cursor for the connection.
  • enable_load_extension(...) --Enable dynamic loading of SQLite extension modules. Non-standard.
  • execute(...) -- Executes a SQL statement. Non-standard.
  • executemany(...) --Repeatedly executes a SQL statement. Non-standard.
  • executescript(...) -- Executes a multiple SQL statements at once. Non-standard.
  • interrupt(...) -- Abort any pending database operation. Non-standard.
  •  iterdump(...) -- Returns iterator to the dump of the database in an SQL text format. Non-standard.
  • load_extension(...) -- Load SQLite extension module. Non-standard.
  • rollback(...) -- Roll back the current transaction.
  • set_authorizer(...) -- Sets authorizer callback. Non-standard.
  • set_progress_handler(...) -- Sets progress handler callback. Non-standard.
  • set_trace_callback(...) -- Sets a trace callback called for each SQL statement (passed as unicode). Non-standard.
Examples
Here, this examples, mydb.db database is created and in that database Table3 is created with Fields, name, email, telephone and income. The values are displayed through for loop by order of income in html file.
Steps for creating virtual environment, startproject, startapp, creating database, and migrating the data, and running server to check the app.
  • virtualenenv pythonlake
  • cd pythonlake
  • .\Scripts\activate
  • pip install django
  • python .\Scripts\django-admin.py startproject mysite
  • python manage.py migrate
  • python manage.py startapp mydb
  • python manage.py makemigrations mydb




mysite/settings.py

INSTALLED_APPS = (
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles','mydb',
)

mysite/urls.py


from mydb.views import DataBase
urlpatterns = [
    url(r'^$', DataBase),
]

mydb/views.py

from django.shortcuts import render

import sqlite3

def DataBase (request):
    conn=sqlite3.connect('mydb.db')
    cursor=conn.cursor()
    cursor.execute('''CREATE TABLE table3(name text, phone text, email text, income text)''')
    cursor.execute('''INSERT INTO table3 VALUES(' Erica ', ' 571 402 2992 ', ' erica.steve@ibm.com ', '  180000 ')''')
    cursor.execute('''INSERT INTO table3 VALUES(' Strube ', ' 571 402 2212 ', ' marya_strube@care.com ', ' 890000 ')''')
    conn.commit()

    for row in cursor.execute('SELECT * FROM table3 ORDER BY income'):
        response=row

    return render(request, 'mydb\index.html', {'response':response} )

mydb/index.html

<!DOCTYPE html>
<html>
<head lang="en">
    <meta charset="UTF-8">
    <title>Table3</title>
</head>
<body>

{{response}}

</body>
</html>




Examples
The database contact_form is created using Sqlite3. 
>>> import sqlite3
>>>conn=sqlite3.connect('contact_form.db')
>>>cursor=conn.cursor()
>>> cursor.execute('''CREATE TABLE table1(name text, phone text, email text, income text)''')
<sqlite3.Cursor object at 0x0000000004329810>
>>> cursor.execute("INSERT INTO table1 VALUES('ADIL', '571 247 8186', 'adil@pythonlake.com', '150000')")
<sqlite3.Cursor object at 0x0000000004329810>
>>> conn.commit()
>>>for row in cursor.execute('SELECT * FROM table1 ORDER BY name'):
print(row)
('ADIL', '571 247 8186', 'adil@pythonlake.com', '150,000')

>>>for row in cursor.execute('SELECT * FROM table1 ORDER BY name'):
print(row+row)

('ADIL', '571 247 8186', 'adil@pythonlake.com', '150,000', 'ADIL', '571 247 8186', 'adil@pythonlake.com', '150,000')

>>>for row in cursor.execute('SELECT * FROM table1 ORDER BY name'):
row.count("Adil")
0
>>> for row in cursor.execute('SELECT * FROM table1 ORDER BY name'):
len(row)
4


sqlite3


Comments