#Connect to sqlite database python code

FOREIGN KEY (product_id) REFERENCES products (id), The final table to define will be the line items table which gives a detailed accounting of the products in each order. FOREIGN KEY (customer_id) REFERENCES customers (id))''' > cur.execute(orders_sql) Since SQLite does not support an actual date/time data type (or data class to be consistent with the SQLite vernacular) all dates will be represented as text values. The next table to define will be the orders table which associates customers to orders via a foreign key and the date of their purchase. > cur.execute( '''SELECT sql FROM sqlite_master WHERE type='table' To get a look at the schema of the tables query the sql column of the same table where the type is still 'table' and the name is equal to 'customers' and/or 'products'. > cur.execute( 'SELECT name FROM sqlite_master WHERE type='table'') To see all the tables in the currently connected database query the name column of the sqlite_master table where the type is equal to 'table'. You can query the sqlite_master table, a built-in SQLite metadata table, to verify that the above commands were successful. I then create a products table in a similar way. I then call the execute(.) method of the cursor object passing it the customers_sql variable. With the cursor created I then wrote the SQL to create the customers table, giving it a primary key along with a first and last name text field and assign it to a variable called customers_sql. The cursor object is used to execute SQL statements on the SQLite database.

#Connect to sqlite database python code

The above code creates a connection object then uses it to instantiate a cursor object.

price real NOT NULL)''' > cur.execute(products_sql) last_name text NOT NULL)''' > cur.execute(customers_sql) > con = db_connect() # connect to the database > cur = con.cursor() # instantiate a cursor obj > customers_sql = ''' In a Python interpreter, in the same directory as the db_utils.py module defined previously, enter the SQL for creating the customers and products tables follows: > from db_utils import db_connect A common pattern in database design for transactional systems of this type are to break the orders into two additional tables, orders and line items (sometimes referred to as order details) to achieve greater normalization. Upon inspecting this data it is evident that it contains information about customers, products, and orders. However, to aid in our discussion of SQLite database programming with Python I will be working off the premise that a database needs to be created for a fictitious book store that has the below data already collected on book sales. I will not be going into the details of this practice and will instead leave it up to reader to further investigate. There are many design considerations that go into defining the tables of a relational database, which entire books have been written about. In order to create database tables you need to have an idea of the structure of the data you are interested in storing. # create a default path to connect to and create (if necessary) a database # called 'database.sqlite3' in the same directory as this scriptДEFAULT_PATH = os.path.join(os.path.dirname(_file_), 'database.sqlite3') You will find that in everyday database programming you will be constantly creating connections to your database, so it is a good idea to wrap this simple connection statement into a reusable generalized function. import sqlite3Д¬on = nnect( '/path/to/file/db.sqlite3') To establish a connection all you need to do is pass a file path to the connect(.) method in the sqlite3 module, and if the database represented by the file does not exists one will be created at that path. Creating a Database and Making a ConnectionД¬reating a new SQLite database is as simple as creating a connection using the sqlite3 module in the Python standard library. However, it is not uncommon to hear it being used for small to medium web and desktop applications. SQLite is often the technology of choice for small applications, particularly those of embedded systems and devices like phones and tablets, smart appliances, and instruments. SQLite is a single file relational database bundled with most standard Python installs. This tutorial will cover using SQLite in combination with Python's sqlite3 interface.