Will this SQLite based Python code be compatible with MySQL?

Question

I have a Python 3 application that uses SQLite directly, by executing SQL code strings (i.e., without using a ORM like SQLalchemy). The database is being initialized like this:

def db_inicialize():
    global DB_PATH
    print(" - Opening database...\n", DB_PATH)
    conn = sqlite3.connect(DB_PATH, detect_types=sqlite3.PARSE_DECLTYPES)
    c = conn.cursor()
    print(" - Locating or creating database...")
    try:
        c.execute("""CREATE TABLE IF NOT EXISTS dados (
                id                INTEGER PRIMARY KEY AUTOINCREMENT,
                destin            TEXT NOT NULL,
                estado            TEXT,
                obj_num           TEXT UNIQUE NOT NULL,
                data_exp          DATETIME,
                valor_cobr        TEXT,
                chq_recebido      TEXT,
                data_depositar    TEXT,
                vols              INTEGER,
                rma               TEXT,
                expedidor         TEXT NOT NULL,
                obs               TEXT,
                arquivado         BOOLEAN,
                data_depositado   DATETIME,
                data_ult_verif    DATETIME,
                data_ult_alt      DATETIME DEFAULT CURRENT_TIMESTAMP,
                estado_detalhado  TEXT
                )""")
    except:
        print(" - It was not possible to create the main table in database.")
        return False
    conn.commit()
    c.close()

Then I am running code like this to change things in the database:

def db_update_estado(info):
    global DB_PATH
    estado = verificar_estado(info)
    if estado != "- N/A -":
        estado_detalhado = obter_estado_detalhado(info)
        agora = datetime.now()
        try:
            conn = sqlite3.connect(DB_PATH)
            c = conn.cursor()
            c.execute("""UPDATE dados SET estado = ?, estado_detalhado = ?, data_ult_verif = ? WHERE obj_num = ?;""", (estado, estado_detalhado, agora, remessa))
            conn.commit()
            c.close()
        except:
            print(" - It was not possible to update this register in the database!")

Is it safe to use, for instance import pymysql, adjust the database connection statements and keep all the SQL related code? Or will I need to revise all the SQL code, change data types in the table initialization...?

Answer 1

This is too long for a comment.

MySQL and SQLite are different databases. They do have a lot of similarities, so if you are careful, you may be able to get code that would work in both. But I wouldn't plan on it.

For instance, your create table statement as text unique. This is not supported in MySQL. You would need to use varchar(xxx) where xxx is less than some value (I would use a practical maximum of 255).

There are numerous differences in the handling of date/time values. For instance, curdate() versus date('now'), and lots of differences in other functions as well. SQLite supports CTEs; MySQL does not.

I would recommend that you build the data-base specific functionality in its own modules. Or, use a common interface such as pandas.