In SQLite, the standard SQL SELECT statement is used to execute queries. Print('New generated user ID is: ', cursor.lastrowid) Newuser_photo = buffer(open('./John.jpg', 'rb').read())Ĭursor.execute('INSERT INTO USERS (NAME, PHOTO) VALUES (?, ?)', (newuser, newuser_photo)) The cursor object provides the attribute lastrowid that is used to fetch the last auto-generated ID after each SQL INSERT statement. ![]() The photo is a binary image file that is read from disk. Only the name and photo are inserted as the ID is automatically generated (AUTOINCREMENT option). The following example presents how to insert a row into the USERS table. The commit() method is provided by the connection object. The execute() method accepts parameterized SQL statements and is capable of managing binary data (passed as an argument of type buffer).ĭata modification SQL statements are run as a transaction and a commit is necessary using the commit() method. Multiple statements may be executed at once with the executescript() method. ![]() To insert a row into a table, a cursor object is created to execute the SQL statement using the execute() method. In SQLite, rows insertion is done using the standard SQL statement INSERT INTO. Print('The database is successfully open ') The following example presents how to create the USERS table that will also be used in the rest of the tutorial: import sqlite3 Multiple statements may be executed at once with the executescript() method instead. To create a table, a connection object is created from the database file then a cursor object is created to execute the SQL statement using the execute() method. In SQLite, tables are created using the standard SQL CREATE TABLE statement. Instead, the previous example should be writing in the following secure way: id = 123Ĭursor.execute('SELECT * FROM USERS WHERE ID = ?', id) Tables creation The following example presents an insecure way to perform a SQL query: id = 123Ĭursor.execute('SELECT * FROM USERS WHERE ID = %s' % id ) The recommended way is to use the API's parameter substitution features (place holders). Using the usual Python concatenation operations is insecure as it may lead to SQL injection vulnerabilities. Most of the time, SQL queries are built using Python variables. ![]() The following table summarizes the natively supported types by SQLite and their default corresponding Python types: Python type It is very useful when working with auto-increment columns An empty list is returned when the query returns no rows.Ī method to close the cursor and release resourcesĪn attribute that provides the rowid of the last modified row. The value None is returned when no more data is availableĪ method to fetch a list of rows from a SQL query. The most used methods and attributes are summarized in the following table and later sections provide examples of their usage: execute(sql)Ī method to execute one optionally parameterized SQL statementĪ method to execute multiple SQL statements (separated by ' ') at once.Ī method to fetch the next row from a SQL query. This object has a set of useful methods and attributes that are used to execute SQL statements and fetch results. The connection object provides the cursor() method that returns an object of type Cursor. Example: con = nnect(':memory:') Execution of SQL statements To create an in-memory database, the connect() function is passed the special string ' :memory:' argument. With this feature, the database is only created in memory (RAM) and destroyed when the process is terminated or the close() method is called. SQLite has support for in-memory databases. Print('The database is successfully open') The following example demonstrates how to create a SQLite database file ' my_users.db': import sqlite3 When the database is no longer needed, the connection object provides the close() method to release resources and close the file. It is passed the database file name and returns an object of type Connection that is used later for all interactions with the database. The sqlite3 module provides the function connect() to open a SQLite database file (or create a new one if it does not exist). It is used to hold the database objects (tables, rows, …). In SQLite, the database is represented by a file on disk. ![]() This tutorial presents how to work with SQLite using Python and the sqlite3 module. Python has support for SQLite via the sqlite3 module ( pysqlite) which provides an easy to use and powerful API. SQLite is a file-based relational database management system that allows applications to have an embedded SQL database without the need for a connection to an external database server. SQLite Python API tutorial TextSegment (By|For) Coders SQLite Python API tutorial
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |