Mysql / Maridb Python Connector is not loading data into table

# Module Imports
import mariadb
import sys
import csv
from pathlib import Path

def connect_to_mariaDB(databse, user, passwd):
    # Connect to MariaDB Platform
    try: conn = mariadb.connect( 
        user=user, 
        password=passwd, 
        host="localhost", 
        port=3306, 
        database=databse 
    ) 
    except mariadb.Error as e: 
        print(f"Error connecting to MariaDB Platform: {e}") 
        sys.exit(1) 
    return conn

def check_if_table_exists_and_overwrite(conn, tableName, database, overwrite):
    cur = conn.cursor() 
    cur.execute(f"SELECT table_name FROM information_schema.tables WHERE table_schema = '{database}';") 
    for(table_name) in cur:
        if table_name[0] == tableName:
            if overwrite == "YES":
                print("table exists - DROP TABLE")
                cur.execute(f"DROP TABLE {tableName}")
                return True        
            else:
                return False
    return True

def import_file_into_db_table_(
filename, database, user, passwd, tableName,
create_table_statement = "", overwrite = False):

    conn = connect_to_mariaDB(database, user, passwd)
    cur = conn.cursor()
    if conn != None:
        print(f"Connection successful to database {database}")
        
        if check_if_table_exists_and_overwrite(conn, tableName, database, overwrite):
            cur.execute(create_table_statement)
            print("table is created")
            
            path = f"{Path().absolute()}\{filename}".replace("\","/")
            print(path)
            load_data_statement = f"""LOAD DATA INFILE '{path}' 
                                    INTO TABLE {tableName} 
                                    FIELDS TERMINATED BY ';' 
                                    OPTIONALLY ENCLOSED BY '"' 
                                    LINES TERMINATED BY '\n' 
                                    IGNORE 1 LINES
                                    """
            print(load_data_statement)
            cur.execute(load_data_statement)
            print("load data into table - successful")
            
        else:
            print("table exists - no permission to overwrite")

    cur.execute("SELECT * FROM student_mat;")
    for da in cur: 
        print(da)
        

# variables
filename = "student-mat.csv"
database = "dbs2021"
tableName = "student_mat"

# load the create_table_statement
create_table_statement = ""
path = f"{Path().absolute()}\create_table_statement.txt"
with open(path, newline='') as file:
    spamreader = csv.reader(file, delimiter='n', quotechar='|')

    for row in spamreader:
        create_table_statement += row[0]
        
parameters_length = len(sys.argv)
if parameters_length == 3:
    user, passwd = sys.argv[1], sys.argv[2]
    import_file_into_db_table_(filename, database, user, passwd, tableName, create_table_statement, "YES")
elif parameters_length == 4:
    user, passwd, overwrite = sys.argv[1], sys.argv[2], sys.argv[3]
    import_file_into_db_table_(filename, database, user, passwd, tableName, create_table_statement, overwrite)
else:
    print("wrong parametersnTry -user -passwd or additional -overwrite")

The code checks if there is a table with the same name in the db and then potentially drops it, creates a new table and loads the data of the csv file into the table. When executing the code it seems like everything is working but when going in the mariadb command prompt the created table is empty even though when outputting the table in the code it is filled.

Answer

By default MariaDB Connector/Python doesn’t use autocommit mode.

You need either set autocommit=True when establishing the connection or you have to commit your changes with conn.commit().

Advertisement

Answer