SQLite

SQLite is probably the most straightforward database to connect to with a Python application since you don’t need to install any external Python SQL modules to do so. By default, yourPython installation contains a Python SQL library namedsqlite3 that you can use to interact with an SQLite database.

What’s more, SQLite databases areserverless andself-contained, since they read and write data to a file. This means that, unlike with MySQL and PostgreSQL, you don’t even need to install and run an SQLite server to perform database operations!

Here’s how you usesqlite3 to connect to an SQLite database in Python:

 1importsqlite3 2fromsqlite3importError 3 4defcreate_connection(path): 5connection=None 6try: 7connection=sqlite3.connect(path) 8print("Connection to SQLite DB successful") 9exceptErrorase:10print(f"The error '{e}' occurred")1112returnconnection

Here’s how this code works:

• Lines 1 and 2 importsqlite3 and the module’sError class.
• Line 4 defines a function.create_connection() that accepts the path to the SQLite database.
• Line 7 uses.connect() from thesqlite3 module and takes the SQLite database path as a parameter. If the database exists at the specified location, then a connection to the database is established. Otherwise, a new database is created at the specified location, and a connection is established.
• Line 8 prints the status of the successful database connection.
• Line 9 catches anyexception that might be thrown if.connect() fails to establish a connection.
• Line 10 displays the error message in the console.

sqlite3.connect(path) returns aconnection object, which is in turn returned bycreate_connection(). Thisconnection object can be used to execute queries on an SQLite database. The following script creates a connection to the SQLite database:

connection=create_connection("E:\\sm_app.sqlite")

Once you execute the above script, you’ll see that a database filesm_app.sqlite is created in the root directory. Note that you can change the location to match your setup.

MySQL

Unlike SQLite, there’s no default Python SQL module that you can use to connect to a MySQL database. Instead, you’ll need to install aPython SQL driver for MySQL in order to interact with a MySQL database from within a Python application. One such driver ismysql-connector-python. You can download this Python SQL module withpip:

$pipinstallmysql-connector-python

Note that MySQL is aserver-based database management system. One MySQL server can have multiple databases. Unlike SQLite, where creating a connection is tantamount to creating a database, a MySQL database has a two-step process for database creation:

1. Make a connection to a MySQL server.
2. Execute a separate query to create the database.

Define a function that connects to the MySQL database server and returns the connection object:

 1importmysql.connector 2frommysql.connectorimportError 3 4defcreate_connection(host_name,user_name,user_password): 5connection=None 6try: 7connection=mysql.connector.connect( 8host=host_name, 9user=user_name,10passwd=user_password11)12print("Connection to MySQL DB successful")13exceptErrorase:14print(f"The error '{e}' occurred")1516returnconnection1718connection=create_connection("localhost","root","")

In the above script, you define a functioncreate_connection() that accepts three parameters:

1. host_name
2. user_name
3. user_password

Themysql.connector Python SQL module contains a method.connect() that you use in line 7 to connect to a MySQL database server. Once the connection is established, theconnection object is returned to the calling function. Finally, in line 18 you callcreate_connection() with the host name, username, and password.

So far, you’ve only established the connection. The database is not yet created. To do this, you’ll define another functioncreate_database() that accepts two parameters:

1. connection is theconnection object to the database server that you want to interact with.
2. query is the query that creates the database.

Here’s what this function looks like:

defcreate_database(connection,query):cursor=connection.cursor()try:cursor.execute(query)print("Database created successfully")exceptErrorase:print(f"The error '{e}' occurred")

To execute queries, you use thecursor object. Thequery to be executed is passed tocursor.execute() instring format.

Create a database namedsm_app for your social media app in the MySQL database server:

create_database_query="CREATE DATABASE sm_app"create_database(connection,create_database_query)

Now you’ve created a databasesm_app on the database server. However, theconnection object returned by thecreate_connection() is connected to the MySQL database server. You need to connect to thesm_app database. To do so, you can modifycreate_connection() as follows:

 1defcreate_connection(host_name,user_name,user_password,db_name): 2connection=None 3try: 4connection=mysql.connector.connect( 5host=host_name, 6user=user_name, 7passwd=user_password, 8database=db_name 9)10print("Connection to MySQL DB successful")11exceptErrorase:12print(f"The error '{e}' occurred")1314returnconnection

You can see in line 8 thatcreate_connection() now accepts an additional parameter calleddb_name. This parameter specifies the name of the database that you want to connect to. You can pass in the name of the database you want to connect to when you call this function:

connection=create_connection("localhost","root","","sm_app")

The above script successfully callscreate_connection() and connects to thesm_app database.

PostgreSQL

Like MySQL, there’s no default Python SQL library that you can use to interact with a PostgreSQL database. Instead, you need to install athird-party Python SQL driver to interact with PostgreSQL. One such Python SQL driver for PostgreSQL ispsycopg2. Execute the following command on yourterminal to install thepsycopg2 Python SQL module:

$pipinstallpsycopg2

Like with the SQLite and MySQL databases, you’ll definecreate_connection() to make a connection with your PostgreSQL database:

importpsycopg2frompsycopg2importOperationalErrordefcreate_connection(db_name,db_user,db_password,db_host,db_port):connection=Nonetry:connection=psycopg2.connect(database=db_name,user=db_user,password=db_password,host=db_host,port=db_port,)print("Connection to PostgreSQL DB successful")exceptOperationalErrorase:print(f"The error '{e}' occurred")returnconnection

You usepsycopg2.connect() to connect to a PostgreSQL server from within your Python application.

You can then usecreate_connection() to create a connection to a PostgreSQL database. First, you’ll make a connection with the default databasepostgres by using the following string:

connection=create_connection("postgres","postgres","abc123","127.0.0.1","5432")

Next, you have to create the databasesm_app inside the defaultpostgres database. You can define a function to execute any SQL query in PostgreSQL. Below, you definecreate_database() to create a new database in the PostgreSQL database server:

defcreate_database(connection,query):connection.autocommit=Truecursor=connection.cursor()try:cursor.execute(query)print("Query executed successfully")exceptOperationalErrorase:print(f"The error '{e}' occurred")create_database_query="CREATE DATABASE sm_app"create_database(connection,create_database_query)

Once you run the script above, you’ll see thesm_app database in your PostgreSQL database server.

Before you execute queries on thesm_app database, you need to connect to it:

connection=create_connection("sm_app","postgres","abc123","127.0.0.1","5432")

Once you execute the above script, a connection will be established with thesm_app database located in thepostgres database server. Here,127.0.0.1 refers to the database server host IP address, and5432 refers to the port number of the database server.

SQLite

To execute queries in SQLite, usecursor.execute(). In this section, you’ll define a functionexecute_query() that uses this method. Your function will accept theconnection object and a query string, which you’ll pass tocursor.execute().

.execute() can execute any query passed to it in the form of string. You’ll use this method to create tables in this section. In the upcoming sections, you’ll use this same method to execute update and delete queries as well.

Here’s your function definition:

defexecute_query(connection,query):cursor=connection.cursor()try:cursor.execute(query)connection.commit()print("Query executed successfully")exceptErrorase:print(f"The error '{e}' occurred")

This code tries to execute the givenquery and prints an error message if necessary.

Next, write yourquery:

create_users_table="""CREATE TABLE IF NOT EXISTS users (  id INTEGER PRIMARY KEY AUTOINCREMENT,  name TEXT NOT NULL,  age INTEGER,  gender TEXT,  nationality TEXT);"""

This says to create a tableusers with the following five columns:

1. id
2. name
3. age
4. gender
5. nationality

Finally, you’ll callexecute_query() to create the table. You’ll pass in theconnection object that you created in the previous section, along with thecreate_users_table string that contains the create table query:

execute_query(connection,create_users_table)

The following query is used to create theposts table:

create_posts_table="""CREATE TABLE IF NOT EXISTS posts(  id INTEGER PRIMARY KEY AUTOINCREMENT,  title TEXT NOT NULL,  description TEXT NOT NULL,  user_id INTEGER NOT NULL,  FOREIGN KEY (user_id) REFERENCES users (id));"""

Since there’s a one-to-many relationship betweenusers andposts, you can see a foreign keyuser_id in theposts table that references theid column in theusers table. Execute the following script to create theposts table:

execute_query(connection,create_posts_table)

Finally, you can create thecomments andlikes tables with the following script:

create_comments_table="""CREATE TABLE IF NOT EXISTS comments (  id INTEGER PRIMARY KEY AUTOINCREMENT,  text TEXT NOT NULL,  user_id INTEGER NOT NULL,  post_id INTEGER NOT NULL,  FOREIGN KEY (user_id) REFERENCES users (id) FOREIGN KEY (post_id) REFERENCES posts (id));"""create_likes_table="""CREATE TABLE IF NOT EXISTS likes (  id INTEGER PRIMARY KEY AUTOINCREMENT,  user_id INTEGER NOT NULL,  post_id integer NOT NULL,  FOREIGN KEY (user_id) REFERENCES users (id) FOREIGN KEY (post_id) REFERENCES posts (id));"""execute_query(connection,create_comments_table)execute_query(connection,create_likes_table)

You can see thatcreating tables in SQLite is very similar to using raw SQL. All you have to do is store the query in a string variable and then pass that variable tocursor.execute().

MySQL

You’ll use themysql-connector-python Python SQL module to create tables in MySQL. Just like with SQLite, you need to pass your query tocursor.execute(), which is returned by calling.cursor() on theconnection object. You can create another functionexecute_query() that accepts theconnection andquery string:

 1defexecute_query(connection,query): 2cursor=connection.cursor() 3try: 4cursor.execute(query) 5connection.commit() 6print("Query executed successfully") 7exceptErrorase: 8print(f"The error '{e}' occurred")

In line 4, you pass thequery tocursor.execute().

Now you can create yourusers table using this function:

create_users_table="""CREATE TABLE IF NOT EXISTS users (  id INT AUTO_INCREMENT,  name TEXT NOT NULL,  age INT,  gender TEXT,  nationality TEXT,  PRIMARY KEY (id)) ENGINE = InnoDB"""execute_query(connection,create_users_table)

The query for implementing the foreign key relation is slightly different in MySQL as compared to SQLite. What’s more, MySQL uses theAUTO_INCREMENT keyword (compared to the SQLiteAUTOINCREMENT keyword) to create columns where the values areautomatically incremented when new records are inserted.

The following script creates theposts table, which contains a foreign keyuser_id that references theid column of theusers table:

create_posts_table="""CREATE TABLE IF NOT EXISTS posts (  id INT AUTO_INCREMENT,  title TEXT NOT NULL,  description TEXT NOT NULL,  user_id INTEGER NOT NULL,  FOREIGN KEY fk_user_id (user_id) REFERENCES users(id),  PRIMARY KEY (id)) ENGINE = InnoDB"""execute_query(connection,create_posts_table)

Similarly, to create thecomments andlikes tables, you can pass the correspondingCREATE queries toexecute_query().

PostgreSQL

Like with SQLite and MySQL databases, theconnection object that’s returned bypsycopg2.connect() contains acursor object. You can usecursor.execute() to execute Python SQL queries on your PostgreSQL database.

Define a functionexecute_query():

defexecute_query(connection,query):connection.autocommit=Truecursor=connection.cursor()try:cursor.execute(query)print("Query executed successfully")exceptOperationalErrorase:print(f"The error '{e}' occurred")

You can use this function to create tables, insert records, modify records, and delete records in your PostgreSQL database.

Now create theusers table inside thesm_app database:

create_users_table="""CREATE TABLE IF NOT EXISTS users (  id SERIAL PRIMARY KEY,  name TEXT NOT NULL,  age INTEGER,  gender TEXT,  nationality TEXT)"""execute_query(connection,create_users_table)

You can see that the query to create theusers table in PostgreSQL is slightly different than SQLite and MySQL. Here, the keywordSERIAL is used to create columns that increment automatically. Recall that MySQL uses the keywordAUTO_INCREMENT.

In addition, foreign key referencing is also specified differently, as shown in the following script that creates theposts table:

create_posts_table="""CREATE TABLE IF NOT EXISTS posts (  id SERIAL PRIMARY KEY,  title TEXT NOT NULL,  description TEXT NOT NULL,  user_id INTEGER REFERENCES users(id))"""execute_query(connection,create_posts_table)

To create thecomments table, you’ll have to write aCREATE query for thecomments table and pass it toexecute_query(). The process for creating thelikes table is the same. You only have to modify theCREATE query to create thelikes table instead of thecomments table.

SQLite

To insert records into your SQLite database, you can use the sameexecute_query() function that you used to create tables. First, you have to store yourINSERT INTO query in a string. Then, you can pass theconnection object andquery string toexecute_query(). Let’s insert five records into theusers table:

create_users="""INSERT INTO  users (name, age, gender, nationality)VALUES  ('James', 25, 'male', 'USA'),  ('Leila', 32, 'female', 'France'),  ('Brigitte', 35, 'female', 'England'),  ('Mike', 40, 'male', 'Denmark'),  ('Elizabeth', 21, 'female', 'Canada');"""execute_query(connection,create_users)

Since you set theid column to auto-increment, you don’t need to specify the value of theid column for theseusers. Theusers table will auto-populate these five records withid values from1 to5.

Now insert six records into theposts table:

create_posts="""INSERT INTO  posts (title, description, user_id)VALUES  ("Happy", "I am feeling very happy today", 1),  ("Hot Weather", "The weather is very hot today", 2),  ("Help", "I need some help with my work", 2),  ("Great News", "I am getting married", 1),  ("Interesting Game", "It was a fantastic game of tennis", 5),  ("Party", "Anyone up for a late-night party today?", 3);"""execute_query(connection,create_posts)

It’s important to mention that theuser_id column of theposts table is aforeign key that references theid column of theusers table. This means that theuser_id column must contain a value thatalready exists in theid column of theusers table. If it doesn’t exist, then you’ll see an error.

Similarly, the following script inserts records into thecomments andlikes tables:

create_comments="""INSERT INTO  comments (text, user_id, post_id)VALUES  ('Count me in', 1, 6),  ('What sort of help?', 5, 3),  ('Congrats buddy', 2, 4),  ('I was rooting for Nadal though', 4, 5),  ('Help with your thesis?', 2, 3),  ('Many congratulations', 5, 4);"""create_likes="""INSERT INTO  likes (user_id, post_id)VALUES  (1, 6),  (2, 3),  (1, 5),  (5, 4),  (2, 4),  (4, 2),  (3, 6);"""execute_query(connection,create_comments)execute_query(connection,create_likes)

In both cases, you store yourINSERT INTO query as a string and execute it withexecute_query().

MySQL

There are two ways to insert records into MySQL databases from a Python application. The first approach is similar to SQLite. You can store theINSERT INTO query in a string and then usecursor.execute() to insert records.

Earlier, you defined a wrapper functionexecute_query() that you used to insert records. You can use this same function now to insert records into your MySQL table. The following script inserts records into theusers table usingexecute_query():

create_users="""INSERT INTO  `users` (`name`, `age`, `gender`, `nationality`)VALUES  ('James', 25, 'male', 'USA'),  ('Leila', 32, 'female', 'France'),  ('Brigitte', 35, 'female', 'England'),  ('Mike', 40, 'male', 'Denmark'),  ('Elizabeth', 21, 'female', 'Canada');"""execute_query(connection,create_users)

The second approach usescursor.executemany(), which accepts two parameters:

1. The query string containing placeholders for the records to be inserted
2. Thelist of records that you want to insert

Look at the following example, which inserts two records into thelikes table:

sql="INSERT INTO likes ( user_id, post_id ) VALUES (%s,%s )"val=[(4,5),(3,4)]cursor=connection.cursor()cursor.executemany(sql,val)connection.commit()

It’s up to you which approach you choose to insert records into your MySQL table. If you’re an expert in SQL, then you can use.execute(). If you’re not much familiar with SQL, then it may be more straightforward for you to use.executemany(). With either of the two approaches, you can successfully insert records into theposts,comments, andlikes tables.

PostgreSQL

In the previous section, you saw two approaches for inserting records into SQLite database tables. The first uses an SQL string query, and the second uses.executemany().psycopg2 follows this second approach, though.execute() is used to execute a placeholder-based query.

You pass the SQL query with the placeholders and the list of records to.execute(). Each record in the list will be atuple, where tuple values correspond to the column values in the database table. Here’s how you can insert user records into theusers table in a PostgreSQL database:

users=[("James",25,"male","USA"),("Leila",32,"female","France"),("Brigitte",35,"female","England"),("Mike",40,"male","Denmark"),("Elizabeth",21,"female","Canada"),]user_records=", ".join(["%s"]*len(users))insert_query=(f"INSERT INTO users (name, age, gender, nationality) VALUES{user_records}")connection.autocommit=Truecursor=connection.cursor()cursor.execute(insert_query,users)

The script above creates a listusers that contains five user records in the form of tuples. Next, you create a placeholder string with five placeholder elements (%s) that correspond to the five user records. The placeholder string isconcatenated with the query that inserts records into theusers table. Finally, the query string and the user records are passed to.execute(). The above script successfully inserts five records into theusers table.

Take a look at another example of inserting records into a PostgreSQL table. The following script inserts records into theposts table:

posts=[("Happy","I am feeling very happy today",1),("Hot Weather","The weather is very hot today",2),("Help","I need some help with my work",2),("Great News","I am getting married",1),("Interesting Game","It was a fantastic game of tennis",5),("Party","Anyone up for a late-night party today?",3),]post_records=", ".join(["%s"]*len(posts))insert_query=(f"INSERT INTO posts (title, description, user_id) VALUES{post_records}")connection.autocommit=Truecursor=connection.cursor()cursor.execute(insert_query,posts)

You can insert records into thecomments andlikes tables with the same approach.

SQLite

To select records using SQLite, you can again usecursor.execute(). However, after you’ve done this, you’ll need to call.fetchall(). This method returns a list of tuples where each tuple is mapped to the corresponding row in the retrieved records.

To simplify the process, you can create a functionexecute_read_query():

defexecute_read_query(connection,query):cursor=connection.cursor()result=Nonetry:cursor.execute(query)result=cursor.fetchall()returnresultexceptErrorase:print(f"The error '{e}' occurred")

This function accepts theconnection object and theSELECT query and returns the selected record.

select_users="SELECT * from users"users=execute_read_query(connection,select_users)foruserinusers:print(user)

(1, 'James', 25, 'male', 'USA')(2, 'Leila', 32, 'female', 'France')(3, 'Brigitte', 35, 'female', 'England')(4, 'Mike', 40, 'male', 'Denmark')(5, 'Elizabeth', 21, 'female', 'Canada')

select_posts="SELECT * FROM posts"posts=execute_read_query(connection,select_posts)forpostinposts:print(post)

(1, 'Happy', 'I am feeling very happy today', 1)(2, 'Hot Weather', 'The weather is very hot today', 2)(3, 'Help', 'I need some help with my work', 2)(4, 'Great News', 'I am getting married', 1)(5, 'Interesting Game', 'It was a fantastic game of tennis', 5)(6, 'Party', 'Anyone up for a late-night party today?', 3)

select_users_posts="""SELECT  users.id,  users.name,  posts.descriptionFROM  posts  INNER JOIN users ON users.id = posts.user_id"""users_posts=execute_read_query(connection,select_users_posts)forusers_postinusers_posts:print(users_post)

(1, 'James', 'I am feeling very happy today')(2, 'Leila', 'The weather is very hot today')(2, 'Leila', 'I need some help with my work')(1, 'James', 'I am getting married')(5, 'Elizabeth', 'It was a fantastic game of tennis')(3, 'Brigitte', 'Anyone up for a late night party today?')

select_posts_comments_users="""SELECT  posts.description as post,  text as comment,  nameFROM  posts  INNER JOIN comments ON posts.id = comments.post_id  INNER JOIN users ON users.id = comments.user_id"""posts_comments_users=execute_read_query(connection,select_posts_comments_users)forposts_comments_userinposts_comments_users:print(posts_comments_user)

('Anyone up for a late night party today?', 'Count me in', 'James')('I need some help with my work', 'What sort of help?', 'Elizabeth')('I am getting married', 'Congrats buddy', 'Leila')('It was a fantastic game of tennis', 'I was rooting for Nadal though', 'Mike')('I need some help with my work', 'Help with your thesis?', 'Leila')('I am getting married', 'Many congratulations', 'Elizabeth')

cursor=connection.cursor()cursor.execute(select_posts_comments_users)cursor.fetchall()column_names=[description[0]fordescriptionincursor.description]print(column_names)

['post', 'comment', 'name']

select_post_likes="""SELECT  description as Post,  COUNT(likes.id) as LikesFROM  likes,  postsWHERE  posts.id = likes.post_idGROUP BY  likes.post_id"""post_likes=execute_read_query(connection,select_post_likes)forpost_likeinpost_likes:print(post_like)

('The weather is very hot today', 1)('I need some help with my work', 1)('I am getting married', 2)('It was a fantastic game of tennis', 1)('Anyone up for a late night party today?', 2)

MySQL

The process of selecting records in MySQL is absolutely identical to selecting records in SQLite. You can usecursor.execute() followed by.fetchall(). The following script creates a wrapper functionexecute_read_query() that you can use to select records:

defexecute_read_query(connection,query):cursor=connection.cursor()result=Nonetry:cursor.execute(query)result=cursor.fetchall()returnresultexceptErrorase:print(f"The error '{e}' occurred")

Now select all the records from theusers table:

select_users="SELECT * FROM users"users=execute_read_query(connection,select_users)foruserinusers:print(user)

The output will be similar to what you saw with SQLite.

PostgreSQL

The process of selecting records from a PostgreSQL table with thepsycopg2 Python SQL module is similar to what you did with SQLite and MySQL. Again, you’ll usecursor.execute() followed by.fetchall() to select records from your PostgreSQL table. The following script selects all the records from theusers table and prints them to the console:

defexecute_read_query(connection,query):cursor=connection.cursor()result=Nonetry:cursor.execute(query)result=cursor.fetchall()returnresultexceptOperationalErrorase:print(f"The error '{e}' occurred")select_users="SELECT * FROM users"users=execute_read_query(connection,select_users)foruserinusers:print(user)

Again, the output will be similar to what you’ve seen before.

SQLite

Updating records in SQLite is pretty straightforward. You can again make use ofexecute_query(). As an example, you can update the description of the post with anid of2. First,SELECT the description of this post:

select_post_description="SELECT description FROM posts WHERE id = 2"post_description=execute_read_query(connection,select_post_description)fordescriptioninpost_description:print(description)

You should see the following output:

('The weather is very hot today',)

The following script updates the description:

update_post_description="""UPDATE  postsSET  description = "The weather has become pleasant now"WHERE  id = 2"""execute_query(connection,update_post_description)

Now, if you execute theSELECT query again, you should see the following result:

('The weather has become pleasant now',)

The output has been updated.

MySQL

The process of updating records in MySQL withmysql-connector-python is also a carbon copy of thesqlite3 Python SQL module. You need to pass the string query tocursor.execute(). For example, the following script updates the description of the post with anid of2:

update_post_description="""UPDATE  postsSET  description = "The weather has become pleasant now"WHERE  id = 2"""execute_query(connection,update_post_description)

Again, you’ve used your wrapper functionexecute_query() to update the post description.

PostgreSQL

The update query for PostgreSQL is similar to what you’ve seen with SQLite and MySQL. You can use the above scripts to update records in your PostgreSQL table.

SQLite

You can again useexecute_query() to delete records from YOUR SQLite database. All you have to do is pass theconnection object and the string query for the record you want to delete toexecute_query(). Then,execute_query() will create acursor object using theconnection and pass the string query tocursor.execute(), which will delete the records.

As an example, try to delete the comment with anid of5:

delete_comment="DELETE FROM comments WHERE id = 5"execute_query(connection,delete_comment)

Now, if you select all the records from thecomments table, you’ll see that the fifth comment has been deleted.

MySQL

The process for deletion in MySQL is also similar to SQLite, as shown in the following example:

delete_comment="DELETE FROM comments WHERE id = 2"execute_query(connection,delete_comment)

Here, you delete the second comment from thesm_app database’scomments table in your MySQL database server.

PostgreSQL

The delete query for PostgreSQL is also similar to SQLite and MySQL. You can write a delete query string by using theDELETE keyword and then passing the query and theconnection object toexecute_query(). This will delete the specified records from your PostgreSQL database.