SQLite is different from Oracle, MySQL, and other server-level databases. SQLite is just an embedded database engine, which is especially suitable for accessing an appropriate amount of data on devices with limited resources (such as mobile phones, PDAs, etc.).
Although SQLite supports most SQL 92 syntax and allows developers to use SQL statements to operate the data in the database, unlike Oracle, MySQL, and other databases, SQLite does not need to install and start the server process. SQLite database is just a file, it does not need the server process.
In fact, SQLite operation is just a more convenient file operation. As you will see later, when an application creates or opens an SQLite database, it just opens a file for reading and writing. Therefore, some people say that SQLite is a bit like Microsoft Access. But SQLite’s function is much more powerful than Microsoft Access.
1. Python & SQLite Database – Where Is The SQLite DB & SQLite Python Module
Installed Folder.
When you install Python, it will install the SQLite database and the Python module that can manage the SQLite database by default. If you install Python on Windows, you can find the sqlite3.dll file in the DLLs subdirectory of the Python installation directory, which is the core file of the SQLite database. You can also find the SQLite python module directory in the Python installation directory lib subdirectory like below, below is the SQLite database directory and Python module for SQLite directory on macOS.
# This is the SQLite database installed directory in Python 3.7.
/Library/Frameworks/Python.framework/Versions/3.7/lib/sqlite3.21.0/
├── libsqlite3.21.0.dylib
└── pkgIndex.tcl
# This is the SQLite database Python module installed directory in Python 3.7.
/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/sqlite3/
├── __init__.py
├── __pycache__
│ ├── __init__.cpython-37.opt-1.pyc
│ ├── __init__.cpython-37.pyc
│ ├── dbapi2.cpython-37.opt-1.pyc
│ ├── dbapi2.cpython-37.pyc
│ ├── dump.cpython-37.opt-1.pyc
│ └── dump.cpython-37.pyc
├── dbapi2.py
├── dump.py
└── test
├── __init__.py
├── __pycache__
│ ├── __init__.cpython-37.opt-1.pyc
│ ├── __init__.cpython-37.pyc
│ ├── backup.cpython-37.opt-1.pyc
│ ├── backup.cpython-37.pyc
│ ├── dbapi.cpython-37.opt-1.pyc
│ ├── dbapi.cpython-37.pyc
│ ├── dump.cpython-37.opt-1.pyc
│ ├── dump.cpython-37.pyc
│ ├── factory.cpython-37.opt-1.pyc
│ ├── factory.cpython-37.pyc
│ ├── hooks.cpython-37.opt-1.pyc
│ ├── hooks.cpython-37.pyc
│ ├── regression.cpython-37.opt-1.pyc
│ ├── regression.cpython-37.pyc
│ ├── transactions.cpython-37.opt-1.pyc
│ ├── transactions.cpython-37.pyc
│ ├── types.cpython-37.opt-1.pyc
│ ├── types.cpython-37.pyc
│ ├── userfunctions.cpython-37.opt-1.pyc
│ └── userfunctions.cpython-37.pyc
├── backup.py
├── dbapi.py
├── dump.py
├── factory.py
├── hooks.py
├── regression.py
├── transactions.py
├── types.py
└── userfunctions.py
2. How To Use SQLite In Python.
Since SQLite database and its Python module have been installed with Python by default, we can manage the SQLite database directly in Python source code.
- Open a terminal and type python to go to the python interactive console.
- Import the SQLite3 DB python module with the command import sqlite3.
- Then we can call the sqlite3 module function or attribute like below.
$ python Python 3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 16:52:21) [Clang 6.0 (clang-600.0.57)] on darwin Type "help", "copyright", "credits" or "license" for more information. >>> import sqlite3 >>> # this global variable displays the API version number of the database module. For database modules that support DB API version 2.0, the variable value is usually 2.0. If this variable does not exist, the database module may not support DB API 2.0 yet. Readers should consider using other database modules that support the database. >>> sqlite3.apilevel '2.0' >>> # the global variable specifies the thread safety level of the database module. The value of the level is 0-3, where 3 means that the module is completely thread safe, 1 means that the module has partial thread safety, and threads can share the module, but can't share the connection, 0 means that threads can't share the module at all. >>> sqlite3.threadsafety 1 # this global variable specifies which style of parameters can be used when the SQL statement requires parameters. in general it is qmark(?). >>> sqlite3.paramstyle 'qmark'
3. How To Connect To SQLite DB & Create SQLite DB Table In Python.
- Call sqlite3.connect method to create a new SQLite database or open an existing SQLite database ( if the SQLite DB file exists ) in the current directory.
# Create or open the SQLite database test-sqlite.db >>> conn = sqlite3.connect('test-sqlite.db') # If you want to create an in-memory database, just use memory to replace the SQLite database file name. >>> conn = sqlite3.connect('memory') - Get the SQLite database cursor object.
>>> cursor = conn.cursor()
- Use the above cursor object to execute the DDL SQL statement to create a table.
>>> cursor.execute(''' create table user_account( ... ... id integer primary key autoincrement, ... ... user_name text, ... ... passwd text, ... ... email text) ''') <sqlite3.Cursor object at 0x7fd20b28e5e0> - Close the above SQLite cursor and connection object in order.
>>> cursor.close() >>> >>> conn.close()
- Now you can open another terminal window and go to the directory which you just run the above command, you can find the SQLite database file is created in that folder.
$ ls -l ...... -rw-r--r-- 1 songzhao admin 12288 Mar 5 11:33 test-sqlite.db ......
4. How To Use SQLite Studio To Open The Above SQLite Database.
- After creating the SQLite database and table in Python source code, I recommend you to use SQLite Studio to manage your SQLite database file. It supports both macOS, Windows, and Linux OS.
- My OS is macOS, you can read the article How To Install SQLite3 On Mac section 3 to learn how to install and use it on macOS. It is similar to Windows and Linux OS.
- After you install the SQLite Studio, click the Add a database icon on the top menu bar to browse the above SQLite database file to open it, and you can see the created user_account table under the database name in the SQLite Studio left panel.