How to Create A SQL database in Python

Hi everyone! Today’s blog will cover python and SQL. Now, if you don’t know what python or sql is, that’s okay, we’ll cover it. Let’s get into it!

RDBMS, also known as relational database management software, aka SQL, gives python structure to adequately store information into a table. Python can build schemas through SQL and create the shape for the application.

SQL stands for sorting query language. We can use certain keywords such as ‘SELECT’, ‘ORDER BY’, ‘WHERE’, etc. for the database to find, query and return a result for the user to see.

Python, is a scripting language that‘s diverse: it can be used for data science or for web development. Most importantly for this article, python and sqlite3 (the SQL module for python) can be used together to create a database.

SET UP

Make sure you have the latest version of python installed. You can go to the developer website and download the latest version (link)

Once you have the latest version of python installed, open the editor of your choice and create a new file named,

main.py

Next, import at the top of your main.py file:

import sqlite3

sqlite3 is a module that is prebuilt into the latest version of python. Therefore you don’t need to pip install sqlite3 as a package.

Create the Database and Connect it

Now we need to create the database. To initialize the database, type:

conn = sqlite3.connect('database.db')

Initialize a variable (I’ve called mine ‘conn’, short for connection). Call the sqlite3 module and invoke connect to a new database. Make sure to type ‘.db’ after the database name!

Next, create a new line under this and type:

cursor = conn.cursor()

The cursor function allows the user to manipulate query results. So this is necessary!

Create the Table

So we’ve created the database, now we need to create the table. This is where some classic SQL comes into play:

cursor.execute("""CREATE TABLE character (
     name string,
     age integer 
)
""")

Here you can see that I used some classic SQL to create a table. the triple quotes before the CREATE TABLE clause and after the initialization of the table allow you to write your SQL code in python on multiple lines. It looks cleaner and it’s easier to read!

Where I wrote ‘character’, that is where your table name goes. ‘name’ and ‘age’ are the columns that go into the table that expect string and integer datatype values respectively.

So far this is what your main.py file should look like!

import sqlite3
conn = sqlite3.connect("database.db")
cursor = conn.cursor()
cursor.execute("""CREATE TABLE character (
    name string, 
    age integer)
""")
print("this works!")
conn.commit()
conn.close()

To make sure it runs correctly, for safe measure, you can add print(“this works!”) at the end of the file and run python3 main.py in your terminal. You should get a the “this works!” message in return.

Once the table is created, comment out the create table code. We can avoid technical errors regarding a surplus of character tables attempting to be made.

conn.commit() and conn.close() commit the SQL actions and close them after they are done. It’s good practice to incorporate conn.commit() and conn.close() after every SQL clause you write.

INSERT (one value)

cursor.execute('INSERT INTO character VALUES (?, ?)', ('crystal', 24))

When you run python3 main.py, you should be able to see the table with ‘crystal’ with the age 24 in the character table in the terminal.

INSERT (many values)

list = [('Wompa', 35), ('Sully', 300), ('Woody', 63)]
cursor.executemany('INSERT INTO character VALUES (?,?)', list)

Where javascript has arrays, in python we call them lists. To insert many values into the character table in this database, we can use .executemany() to do this. We can use list as the second argument in this function.

Here is an example of how to use it like the code above:

conn = sqlite3.connect("database.db")
cursor = conn.cursor()
# cursor.execute("""CREATE TABLE character (
#    name string, 
#    age integer)
# """)
list = [('Wompa', 35), ('Sully', 300), ('Woody', 63)]
cursor.executemany('INSERT INTO character VALUES (?,?)', list)
print(cursor.fetchall())
conn.commit()
conn.close()

SELECT

cursor.execute('SELECT * FROM character')

This statement above says, select all the values from the character table. This returns the entire table.

cursor.execute('SELECT * FROM character WHERE name="crystal"')

You can query for specific values in the table by using the WHERE clause.

Here is an example of how to use the SELECT clause with the code above:

conn = sqlite3.connect("database.db")
cursor = conn.cursor()
# cursor.execute("""CREATE TABLE character (
#    name string, 
#    age integer)
# """)
cursor.execute('SELECT * FROM character WHERE name="crystal"')
print(cursor.fetchone())
conn.commit()
conn.close()

UPDATE

cursor.execute('UPDATE character SET name="not crystal" WHERE rowid=1')

To update a query, you should use the SET clause and define where the row is by the rowid. This allows you to re-set the name to be ‘not crystal’ where the row id is 1.

Here is an example of how to use update with the code above:

conn = sqlite3.connect("database.db")
cursor = conn.cursor()
# cursor.execute("""CREATE TABLE character (
#    name string, 
#    age integer)
# """)
cursor.execute('UPDATE character SET name="not crystal" WHERE rowid=1')
print(cursor.fetchone())
conn.commit()
conn.close()

DELETE

cursor.execute('DELETE FROM character WHERE rowid=1')

The DELETE clause lets us delete a specific instance in the table using the WHERE clause and the rowid again.

conn = sqlite3.connect("database.db")
cursor = conn.cursor()
# cursor.execute("""CREATE TABLE character (
#    name string, 
#    age integer)
# """)
cursor.execute('DELETE FROM character WHERE rowid=1')
conn.commit()
conn.close()

DROP A TABLE

cursor.execute('DROP TABLE character')

This lets you delete the entire table!

conn = sqlite.connect("database.db")
cursor = conn.cursor()
# cursor.execute("""CREATE TABLE character (
#    name string, 
#    age integer)
# """)
cursor.execute('DROP TABLE character')
conn.commit()
conn.close()

There are other ways to create a table in python using sql. There are additionally, different packages you can install to make a table and incorporate the relationships they have. The code I provided for you today stands as an example for a very simple basis for creating tables in python with SQL. It’s a great intro to understand how to make the tables before jumping in deeper, using different packages and furthering that with web development. Popular packages include SQLalchemy with Flask and this allows you to set up your fetches for your application and to create the database as well. I hope this article was a easy reminder of SQL and how easy it is to create with python. Please reach out to me if you have any questions and happy hacking!

-Crystal