How to correctly load a list into a MySQL database using Python?

Question

Python

Good afternoon!
There is a list - very big, I unload it from a certain site.
It may vary in size.
how to insert it into the finished MySQL table?
below code.

import mysql.connector
from mysql.connector import Error

def create_connection(host_name, user_name, user_password):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print("Connection to MySQL DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")

    return connection

connection = create_connection("localhost", "root", "3551989kate1")
def create_database(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database created successfully")
    except Error as e:
        print(f"The error '{e}' occurred")
create_database_query = "CREATE DATABASE sm_app"
create_database(connection, create_database_query)

def execute_query(connection, query):
     cursor = connection.cursor()
     try:
         cursor.execute(query)
         connection.commit()
         print("Query executed successfully")
     except Error as e:
         print(f"The error '{e}' occurred")
create_users_table = """
CREATE TABLE IF NOT EXISTS vacancies (
  id INT AUTO_INCREMENT, 
  session_id INT, 
  time DATE, 
  title TEXT, 
  description TEXT,
  types TEXT,
  PRIMARY KEY (id)
) ENGINE = InnoDB
"""

execute_query(connection, create_users_table)

import requests # Модуль для обработки URL
from bs4 import BeautifulSoup # Модуль для работы с HTML
import time # Модуль для остановки программы
import matplotlib.pyplot as plt # Модуль для визуализации
from bs4 import BeautifulSoup as bs
import pandas as pd
num_of_page = 40
other_vacancies = []  # остальные вакансии будут валиться сюда
collected_data = [
  {'pattern': ['#c', 'c#','c#','#c','.net','core','#'], 'result': [] },
  {'pattern': ['ruby','ror','rails'], 'result': []},
  {'pattern': ['python','etl','питон'], 'result': []},
  {'pattern': ['php','рнр','битрикс','symphony','ravele','magento','yii','october','bitrix','symfony'], 'result': []},
  {'pattern': ['java','jаva','Java','spark'], 'result': []},
  {'pattern': ['javascript','node','js'], 'result': []},
  {'pattern': ['golang','разработчик go','go-разработчик','go','go разработчик'], 'result': []},
  {'pattern': ['c++','с++'], 'result': []},
  {'pattern': ['elixir'], 'result': []},
  {'pattern': ['scala'], 'result': []},
  {'pattern': ['rust'], 'result': []},
  {'pattern': ['kotlin'], 'result': []}
    

]

for i in range(num_of_page):
    URL ="https://career.habr.com/vacancies?divisions[]=backend&page=" + str(i+1)+ "&type=all"
    page = requests.get(URL)
    soup = bs(page.text, "html.parser")
    vacancies_names = soup.find_all('a', class_='vacancy-card__title-link')

    for name in vacancies_names:
        for data in collected_data:
            pattern_found = False
            if any([x in name.get_text().lower() for x in data['pattern']]):
                data['result'].append(name.get_text())
                pattern_found = True
                break
        if not pattern_found:
            other_vacancies.append(name.get_text())

sql = "INSERT INTO vacancies( title) VALUES ( %s)"
val = collected_data[0]['result']

cursor = connection.cursor()
cursor.executemany(sql, val)
connection.commit()

if I just change val to my list in the code (I attach the photo below),
then nothing works.

Answer 1

0) See how everything should work - https://dev.mysql.com/doc/connector-python/en/conn...
1) very large queries may not work, I recommend sending a maximum of 1000 records to the database at a time - break the query into partitions
2) You have an error in SQL, it shouldn't work like that - the field is missing
3) You have a record with user_id, you need to add it to your data.

Answer 2

The code in the example expects a list of 2-tuples.
The list in the screenshot consists of single lines. So either these curves, or you are submitting them to the wrong request.