In this tutorial, you have learned how to create an SQLite database on disk and in memory from a Python program using sqlite3 module. import sqlite3ĭef create_connection (): """ create a database connection to a database that residesĬreate_connection() Code language: Python ( python ) The following program creates an SQLite database in the memory. If you pass the file name as :memory: to the connect() function of the sqlite3 module, it will create a new database that resides in the memory (RAM) instead of a database file on disk. If you skip the folder path c:\sqlite\db, the program will create the database file in the current working directory (CWD). Let’s run the program and check the c:\sqlite\db folder. Note that the prefix r in the r"C:\sqlite\db\pythonsqlite.db" instructs Python that we are passing a raw string. Second, we pass the path of the database file to the create_connection() function to create the database. It is a good programming practice that you should always close the database connection when you complete with it. If everything is fine, we display the SQLite database version. In case an error occurs, we catch it within the try except block and display the error message. After that, call the fetchall () method of the cursor object to fetch the data. Next, create a Cursor object using the cursor method of the Connection object. By using the Connection object, you can perform various database operations. To query data in an SQLite database from Python, you use these steps: First, establish a connection to the SQLite database by creating a Connection object. It returns a Connection object that represents the database. The connect() function opens a connection to an SQLite database. Inside the function, we call the connect() function of the sqlite3 module. import sqlite3ĭef create_connection (db_file): """ create a database connection to a SQLite database """Ĭreate_connection( r"C:\sqlite\db\pythonsqlite.db")įirst, we define a function called create_connection() that connects to an SQLite database specified by the database file db_file. Or you can place the database file a folder of your choice. In this tutorial, you have learned how to develop a Python program to query data from tables in an SQLite database.Note that you must create the c:\sqlite\db folder first before you execute the program. Select_all_tasks(conn) Code language: Python ( python )ĭef create_connection (db_file): """ create a database connection to the SQLite databaseĭef select_task_by_priority (conn, priority): """ This main() function creates a connection to the database C:\sqlite\db\pythonsqlite.db and calls the functions to query all rows from the tasks table and select tasks with priority 1: def main ():ĭatabase = r"C:\sqlite\db\pythonsqlite.db" # create a database connection The fetchall() method fetched all matching tasks by the priority. When the cursor executed the SELECT statement, it substituted the question mark ( ?) by the priority argument. The question mark ( ?) in the query is the placeholder. In the select_task_by_priority() function, we selected the tasks based on a particular priority. This function query tasks by priority: def select_task_by_priority (conn, priority): """Ĭur.execute( "SELECT * FROM tasks WHERE priority=?", (priority,)) In the select_all_tasks() function, we created a cursor, executed the SELECT statement, and called the fetchall() to fetch all tasks from the tasks table. Print(row) Code language: Python ( python ) This function selects all rows from the tasks table and displays the data: def select_all_tasks (conn): """ Return conn Code language: Python ( python ) In the following example, we will use the tasks table created in the creating tables tutorial.įirst, create a connection to an SQLite database specified by a file: def create_connection (db_file): """ create a database connection to the SQLite database The nnect () function returns a Connection object that we will use to interact with the SQLite database held in the file aquarium.db. So let’s start by asking the very basic question what on earth is a database I assume you are already familiar with the Python language. We can connect to a SQLite database using the Python sqlite3 module: import sqlite3 connection nnect('aquarium.db') import sqlite3 gives our Python program access to the sqlite3 module.
0 Comments
Leave a Reply. |