Python/ SQL project —automatic quote posting on Instagram

Some time ago I saw TikTok about quote posting on Instagram. In this case, it's not important why, but I wanted to know, can I make this process automated. So I divided the project into 3 steps:

1. scrape quotes somewhere from the internet and save them to a database using Python/SQL;
2. create an algorithm that can put quotes on some image template using Python;
3. design an algorithm that can create posts on Instagram automatically using Python.

The final result looks like this. It's an Instagram account where I can make some minor adjustments to my code and automatically it will upload as many posts as I wanna. I assume there are available tools on the internet where this can be done without coding, but my goal was just to try and make it myself. To follow along you need just very basic Python knowledge, if you know a little bit of SQL and HTML, it will also help. But overall it's very basic and I hope easy to understand.

Let's start with step 1. We will need to scrape quotes from the web, for that we will use Pythons libraries “BeautifulSoup” and we will also need the “request” library. Later for saving data to the database, let's use the sqlite3. Import the following libraries:

from bs4 import BeautifulSoup
import requests
import sqlite3

You need to find a website that has a lot of quotes (or other data if you are creating something similar). For example, I found this site “quotes.toscrape.com”. I'm not sure was it created for learning reasons, but while it's online we can use it. On this website, scraping is super easy. First, we need to create a request to get website data, and let's save it in the variable “page”.

page = requests.get("https://quotes.toscrape.com/page/1/")

On the Chrome browser, directly on the quote, you can click right mouse button and “inspect” to find where the quote and an author can be found.

As you can see in the image, all quotes are made in HTML tag span and class=“text”. Authors are made in HTML tag “small” and class=“author”. We will use this information in “BeautifulSoup” to get this information to our code. In three lines of code will get all quotes and all authors:

soup = BeautifulSoup(page.text, "lxml")
quote_texts = soup.find_all("span", {"class": "text"})
authors = soup.find_all("small", {"class": "author"})

Now “quote_texts” and “authors” contains lists of all quotes and all authors. With for loop, you can access individual quotes and authors, just add “.text” at the end to get rid of all unnecessary HTML information. Let's check the length of the list and loop through every quote and author and just print them to the console to see if everything works:

for i in range(len(authors)):
    print(quote_texts[i].text)
    print(authors[i].text)

One more thing which must be done is to replace single quote marks with something else and remove double quote marks because we will get an error. Let's replace single quote marks with @ symbol:

quote_texts[i].text.replace('“', '').replace('”', '').replace("'", "@")

Now we get all data we need, we can start working on saving data to the database. In this example let's use sqlite3. First, we need to create a new database and a new table in this database, where we can add data later. The table will have three columns:

1. id, which will have autoincrement, so values are unique
2. author
3. quote

db = sqlite3.connect("my_quotes.db")
cursor = db.cursor()
cursor.execute("CREATE TABLE quotes (id INTEGER PRIMARY KEY AUTOINCREMENT , author VARCHAR(255) NOT NULL, quote TEXT NOT NULL)")
db.commit()

Run this code just once and delete it or comment it out, if you try to make another table with the same name, you will get an error message. To test if the database is active and a table is created, let's try to add data. You can use this code to test it:

db = sqlite3.connect("my_quotes.db")
cursor = db.cursor()
cursor.execute(f"INSERT INTO quotes (author, quote) VALUES('test author', 'test_qoute');")
db.commit()

To see if data is uploading to your database you can do a SQL query or an easier way is to download “DB Browser for SQLite”. Open your database there and you can see your data in a quite nice interface. Now we need to use the previously created “for loop” and start adding data to the database. Sometimes there could be some additional quote symbols and there could be an error so let's use “try/ except” to not fall into errors. As this is the last thing to do in the first point, let's put everything in function “start_scraping”, so it's easier to use later. Access to a database I left outside on purpose, cause we will need that access to query data in the next step. First step full code looks like this:

db = sqlite3.connect("my_quotes.db")
cursor = db.cursor()
def start_scraping(url_adr):
    page = requests.get(url_adr)
    soup = BeautifulSoup(page.text, "lxml")
    quote_texts = soup.find_all("span", {"class": "text"})
    authors = soup.find_all("small", {"class": "author"})

    for i in range(len(authors)):
        quote = quote_texts[i].text.replace('“', '').replace('”', '').replace("'", "@")
        author = authors[i].text

        try:
            cursor.execute(f"INSERT INTO quotes (author, quote) VALUES('{author}', '{quote}');")
            db.commit()
        except:
            print(f" there was problem with this quote -{quote_texts}- and author {authors}")
start_scraping("https://quotes.toscrape.com/page/1/")

Just one last thing to the first step — if you want to get more than the first page, you can use “ for loop” and “f string”. For example to get the first five pages of quotes:

for i in range(5):
    start_scraping(f"https://quotes.toscrape.com/page/{i+1}/")

Now we can move on step 2 and put those quotes on the same template image. I'm not very good with designs, so I used all help I can get from online tools. In this case, I used canva.com, to create my template image. If you also choose this option, there are templates for Instagram posts, so you will get the correct image size and some ideas for design. My image from canva in the final looks like this:

Let's write some code to put quotes on this image automatically. Add some more libraries which will help us do the task:

from PIL import Image, ImageFont, ImageDraw
import textwrap

Let's save one quote and one author in temporary variables, so we have some text to upload on images:

temp_quote = "The world as we have created it is a process of our thinking. It cannot be changed without changing our thinking."
temp_author = "Albert Einstein"

For the next code part, you need to prepare some stuff. First, copy the background image somewhere in the project folder where it is accessible. In this example, an image is called “background_img.png”. Next, you need to add to the project folder some font files, you can google for example times.ttf to get times new roman font or choose another what you like better.

image = Image.open("background_img.png")

text_font = ImageFont.truetype("times.ttf", 80)
text_font_small = ImageFont.truetype("times.ttf", 30)

edit_image = ImageDraw.Draw(image)
W, H = 1080, 1080

para = textwrap.wrap(temp_quote, width=25)
current_h, pad = 300, 10

for line in para:
    w, h = edit_image.textsize(line, font=text_font)
    edit_image.text(((W - w) / 2, current_h), line, fill="#523e36", font=text_font)
    current_h += h + pad

w1, h1 = edit_image.textsize(temp_author, font=text_font_small)
edit_image.text((W - w1 - 100, 900), temp_author, ("#523e36"), font=text_font_small)

image.show()

Shortly what this code does:

• opens template image
• creates two fonts with different font heights (font height is one of two values which will be necessary to adjust automatically on some rules because some quotes are short and some are very long). One is for quotes and one for authors.
• variable “para” contains a list of quote parts. Parameter “width=25” is dividing the quote by a max of 25 characters per line (this is the second value which will be necessary to adjust automatically on some rules)
• variable “current_h” is text position from the top
• for loop goes through every quote line and adds text to the image according to previous data and here you can additionally add text color in “fill”. It is calculated so the text is always in the middle horizontally.
• below for loop is code for adding the author to the image. Here it is calculated so the author's name is in the lower right corner
• the last thing is “image.show()” to preview your result and if you want to save the image using “image.save(“image_name.png)”

At this stage result should look like this:

One more thing to this, as you probably saw, some quotes are short and some are quite long. We need somehow adapt font size and text wrap accordingly. In this case, we can use if/ else statements and through some tests try to understand values. It will look something like this (with more values):

if len(quote) <= 60:
    font_height_ = 120
    wrap_width_ = 15
else:
    font_height_ = 60
    wrap_width_ = 35

Now we can get quotes from a database using SQL, let's save that information in a variable called “data”. When printing add .fetchall() to see results:

db = sqlite3.connect("my_quotes.db")
cursor = db.cursor()
data = cursor.execute("SELECT * FROM quotes")
print(data.fetchall())

As this part came to end, let's put everything in function:

db = sqlite3.connect("my_quotes.db")
cursor = db.cursor()
def add_text_to_image():
    data = cursor.execute("SELECT * FROM quotes")

    for curr_data in data.fetchall():
        author = curr_data[1]
        quote = curr_data[2].replace("@", "'")

        if len(quote) <= 60:
            font_height_ = 120
            wrap_width_ = 15
        elif 60 < len(quote) <= 101:
            font_height_ = 90
            wrap_width_ = 23
        elif 101 < len(quote) < 180:
            font_height_ = 80
            wrap_width_ = 25
        else:
            font_height_ = 60
            wrap_width_ = 35

        image = Image.open("background_img.png")

        text_font = ImageFont.truetype("times.ttf", font_height_)
        text_font_small = ImageFont.truetype("times.ttf", 30)

        edit_image = ImageDraw.Draw(image)
        W, H = 1080, 1080

        para = textwrap.wrap(quote, width=wrap_width_)
        current_h, pad = 300, 10

        for line in para:
            w, h = edit_image.textsize(line, font=text_font)
            edit_image.text(((W - w) / 2, current_h), line, fill="#523e36", font=text_font)
            current_h += h + pad

        w1, h1 = edit_image.textsize(author, font=text_font_small)
        edit_image.text((W - w1 - 100, 900), author, "#523e36", font=text_font_small)

        image.show()
add_text_to_image()

In the result, we have quotes from the database on the template background image:

There could be quotes that will not look so good in the template (too much text for example), then you need to play with text heights and text wrap width. Step two is finished, let's move on to the final step.

There is more than one way how we could upload our quotes to Instagram. I think the easier would be automatically moving the mouse on the screen in specific coordinates. That can be done using PyAutoGui. Additionally, we will use the “time” library to make some small delays. Let's add libraries:

import pyautogui
import time

PyAutoGui allows you to move the mouse on the screen automatically using x and y coordinates. So we need to capture all x/ y coordinates on your screen. To capture coordinates, you can use code like this:

time.sleep(2)
print(f"x postion = {pyautogui.position()[0]}")
print(f"y postion = {pyautogui.position()[1]}")

Run your code, after that, you will have 2 seconds to position your mouse in the place where the click will be necessary and the code will print x/ y coordinates. The first time you will have to add the quote to Instagram manually and capture all x/y coordinates. Some commands from PyAutoGui which will be necessary:

• pyautogui.moveTo(x, y, t) => x/y are coordinates and t is the seconds how long this move will happen (if you have slow internet you will need to put more seconds)
• pyautogui.click() => this command imitates left mouse click
• pyautogui.write(“ “) => this command allows you to write something, for example, you will need to write the caption on Instagram

And that's all commands you need to put a post on Instagram. If you haven't used PyAutoGui maybe it is hard to understand what is happening, just start with small experiments, for example, try to click some button on any website automatically.

Put your images in an easily accessible folder so it's always in one place. I can share how the final code looked on my laptop. If you are using a 13-inch MacBook most of the coordinates will probably be very similar:

wait = 2
time.sleep(5)

for i in range(50):
    pyautogui.moveTo(331, 167, 1)
    pyautogui.click()
    pyautogui.moveTo(1063, 166, 1)
    pyautogui.click()
    pyautogui.moveTo(743, 603, wait)
    pyautogui.click()
    pyautogui.moveTo(273, 288, wait)
    pyautogui.click()
    pyautogui.moveTo(397, 262, 1)
    pyautogui.click()
    pyautogui.moveTo(629, 264, 1)
    pyautogui.rightClick()
    pyautogui.moveTo(691, 296, 1)
    pyautogui.click()
    pyautogui.moveTo(629, 264, 3)
    pyautogui.click()
    pyautogui.moveTo(1266, 712, 1)
    pyautogui.click()
    pyautogui.moveTo(986, 244, wait)
    pyautogui.click()
    pyautogui.moveTo(1157, 242, wait)
    pyautogui.click()
    pyautogui.moveTo(887, 336, wait)
    pyautogui.click()
    pyautogui.write(" ")
    pyautogui.keyDown("option")
    pyautogui.press("3")
    pyautogui.keyUp("option")
    pyautogui.write("philosophy_quotes ")
    pyautogui.keyDown("option")
    pyautogui.press("3")
    pyautogui.keyUp("option")
    pyautogui.write("quotes")
    pyautogui.moveTo(1150, 243, wait)
    time.sleep(5)
    pyautogui.click()
    pyautogui.moveTo(1397, 164, 20)
    pyautogui.click()

If you have never used PyAutoGui, just experiment a little bit and you will fast understand how it really works. So that's all I wanted to share for this project, thanks for reading and I hope it will help on your project.