Creating a database, a table within the database and inserting some values into it in one go I've written a script which creates a new database, a table within the database, insert some values into it and fetch the values in one go. The way I've created the script below to achieve what I just said seems not to be an ideal way as there are too many repetitions in there. To be specific this two functions create_database() and connect() are almost identical. Moreover, I had to use mycursor = conn.cursor() twice within main function.
import mysql.connector

def create_database():
    mydb = mysql.connector.connect(
      host=&quot;localhost&quot;,
      user=&quot;root&quot;,
      passwd = &quot;&quot;,
      database=&quot;&quot;
    )
    return mydb

def connect(databasename):
    mydb = mysql.connector.connect(
      host=&quot;localhost&quot;,
      user=&quot;root&quot;,
      passwd = &quot;&quot;,
      database=databasename
    )
    return mydb

def store_data(item_name,item_link):
    mycursor.execute(&quot;INSERT INTO webdata (item_name,item_link) VALUES (%s,%s)&quot;, (item_name,item_link))
    conn.commit()

if __name__ == '__main__':
    db_name = &quot;newdatabase&quot;
    conn = create_database()
    mycursor = conn.cursor()
    try:
        mycursor.execute(f&quot;CREATE DATABASE {db_name}&quot;)
    except mysql.connector.errors.DatabaseError:
        pass
    conn = connect(db_name)
    mycursor = conn.cursor()
    mycursor.execute(&quot;DROP TABLE if exists webdata&quot;)
    mycursor.execute(&quot;CREATE TABLE if not exists webdata (item_name VARCHAR(255), item_link VARCHAR(255))&quot;)
    store_data(&quot;all questions&quot;,&quot;https://stackoverflow.com/questions/tagged/web-scraping&quot;)
    mycursor.execute(&quot;SELECT * FROM webdata&quot;)
    for item in mycursor.fetchall():
        print(item)

What it prints (expected result):
('all questions', 'https://stackoverflow.com/questions/tagged/web-scraping')
From the title alone, this sounds like something that should not be done, particularly since this is not a lightweight database such as SQLite. Creating the database, its tables, columns, constraints etc. is not, and should not, be the job of the application - but rather an external setup script with administrative permissions. The application, then, should not have such permissions and should only be able to insert/select/update/delete.
For some reason I thought autocommit is enabled by default for the MySQL Python connector, but apparently it's not:
https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html
So your explicit call to commit is fine.
I can't tell from the documentation whether the cursor and connection objects support context management. Try putting both of those in with statements, and if it complains that there's no __enter__, switch to a try/finally that guarantees cursor and connection closure.