Working with Databases There are many different databases

Working with Databases

There are many different databases that one can use e. g. MS Access, Microsoft Sequel Server, My. SQL. For our purposes, we will use a lightweight database called SQLite 3. We can import the SQLite 3 library as follows… import sqlite 3 We can now use the database.

Next, we use the inbuilt function connect() to open a connection between our python program and the SQLite database. Import the SQLite 3 library as follows… import sqlite 3 My. DB =sqlite 3. connect(‘Ninja. Turtle. db’) Note!! We have used a RELATIVE file path here. This means the SQLite DB will be located in the same folder as your Python Program Note!! Name your database here…call it whatever you want!!!

If you want to go for ABSOLUTE file path…then here is an example My. DB =sqlite 3. connect(‘N: \Computing\Ninja. Turtle. db’) Note!! Make sure you have no spaces in your file path! Note!! Name your database here…call it whatever you want!!!

1. Sqlite library imported – ok 2. Connection to database – ok 3. Next, we need to create a cursor object (this will allow us to work with and manipulate our database) import sqlite 3 My. DB = sqlite 3. connect(‘N: \My. Documents. . . etc’) c=My. DB. cursor() Now we can create our first table of data!

We use DDL (Data Definition Language) to create a table in SQlite. import sqlite 3 My. DB = sqlite 3. connect(‘N: \My. Documents. . . etc’) c=My. DB. cursor() #create a table of students c. execute('''CREATE TABLE Student (Student. ID text, Firstname text, Surname text, DOB date, Form. Group text) ''')

The standard DDL for creating a table in SQLite is: #create a table example c. execute('''CREATE TABLE Name. Of. Table (Field Data type, Field Data type) ''')

Now, to insert data into our new table called Student #insert data into our table c. execute(''‘INSERT INTO Student VALUES (‘ 001’, ’Mary’, ’Berry’, ’ 1/1/1994’, ’ 11 W’) ‘’’) Make sure you are inserting data in the right order and with an appropriate data type.

Now you need to save changes using the COMMIT() function and close using the close() function #insert data into our table c. execute(''‘INSERT INTO Student VALUES (‘ 001’, ’Mary’, ’Berry’, ’ 1/1/1994’, ’ 11 W’)’’’) #Save changes using the commit() function My. DB. commit() #Close the database connection My. DB. close()