How can I extract structure and content from a MariaDB SQL dump?

Question

I have an SQL dump file containing an entire MariaDB which is multiple gigabytes big. I don't have access to a local database installation due to company security restrictions. Can I execute my dump via Python in SQLite or extract its data so I can analyze it?

I iterate my dump for table names to get an overview of the database:

table_list=[]
with open(dmp.file ,encoding='cp437') as f:
    for line in f:
        line = line.strip()
        if line.lower().startswith('create table'):
            table_name = re.findall('create table `([\w_]+)`', line.lower())
            table_list.extend(table_name)

for x in table_list:
    print(x)

This works but my dump's SQL statements span multiple lines, so I wrote the following to get the statements on one line:

currentLine = ""
with open(File,encoding='cp437') as f:
for line in f:
   line = line.strip()
   currentLine = currentLine + " " + line
   if line.lower().endswith(';') == True:
       with open(NewFileOneLiner.txt', "a", encoding="utf-8") as g:
       g.write(currentLine.lstrip() + '\n')
       currentLine = ""

What additional steps are needed (since both are SQL databases, transforming the SQL statements should be possible)? Is there any way to execute all statements in SQLite? What are the boundaries of and caveats to this approach (does SQLite not support concepts of SQL that I need to be aware of)? Can I extract the tables and their data in some other form?

the source file is from a maria DB, but i want to read it in in sqlite. I know it isnt the desired way to do but due to restrictions I can't install a db — Bending Rodriguez
– Bending Rodriguez, Commented Apr 17, 2024 at 13:00
No, you need to understand what you're asking and actually say what you want. Dumps aren't backups, they're SQL scripts containing CREATE TABLE commands followed by INSERT commands. They aren't data files. To read anything in there, you need to parse the SQL commands. You can read each line and check if it contains CREATE TABLE something with a regular expression. Parsing the INSERT statements with regular expressions is a lot harder, assuming it's possible — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 17, 2024 at 13:05
SQLite has an extremely limited subset of SQL and different behaviors from MariaDB. It's impractical to try to translate anything but the simplest SQL and I wouldn't trust the results. I would suggest talking with IT about getting the tools necessary to do your job. If they won't you can install MariaDB on a cheap virtual machine. — Schwern
– Schwern, Commented Apr 23, 2024 at 6:00
@Mr.Irrelevant Yes, its main selling point is it does not need a server. Its intended use is to be embedded in a program. However, you'd have the same problem trying to execute a SQL dump on any different database. While there is a SQL standard nobody follows it completely (it's enormous) and everybody extends it. — Schwern
– Schwern, Commented Apr 23, 2024 at 6:07

Schwern · Accepted Answer · 2024-04-23 18:57:15Z

2

While there is a SQL standard nobody follows it completely (it's enormous) and everybody extends it. Every SQL database has a different dialect, implements different portions of the spec, and has their own quirks and extensions. Worse, some very standard SQL are not standard. For example, the standard has nothing to say about indexes, they are considered an implementation issue. Only the simplest SQL can be expected to run the same between different implementations.

SQLite is designed to be a "lightweight" database. Its main feature is it requires no server, a program can have its own stand alone database. SQLite has an extremely limited subset of SQL and different behaviors from MariaDB. It's impractical to try to translate anything but the simplest SQL and I wouldn't trust the results.

I would suggest talking with IT about getting the tools necessary to do your job, they should provide you with a MariaDB server. If they won't, go to the person who assigned you the task and inform them. Alternatively, you can install MariaDB on a cheap virtual machine for less than $1. Then ~~take some office supplies~~ ask for reimbursement.

answered Apr 23, 2024 at 18:57

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Schwern Over a year ago

@Mr.Irrelevant It's not just SQLite. Except for trivial SQL, you can't expect a SQL dump from any database to run on any other implementation. Portable SQL must be carefully written.

user4157124 · Accepted Answer · 2025-10-27 22:41:05Z

Convert the INSERT statements to a CSV file so the data can be imported into other tools.

Function to extract INSERT statements from a specific table:

def extract_inserts(file_name, table_name):
    result = ""
    with open(file_name, "br",) as file_object:
        for line in file_object:
            string_line = str(line.decode('latin1'))
            if f"INSERT INTO `{table_name.upper()}" in string_line.upper():
                result += string_line.replace(
                        "),(", "\n").replace(
                            f"""INSERT INTO `{table_name}` VALUES (""", "").replace(
                                ';\n"', ""
                            )
    return result

Specify a list of tables:

TABLE_NAMES = ["Table1", "Table2", "Table3"]

Run extract_inserts() over the desired tables:

if __name__ == "__main__":
    file_name = r'YourInputFilePath'

    table_names = TABLE_NAMES

    for table_name in TABLE_NAMES:
         print(f"writing to file --->{table_name}.txt")
         with open(r'YourOutputFilePath', "w", encoding="latin1") as file_handle:
             dml = extract_inserts(file_name, table_name)
             file_handle.write(dml)

Collectives™ on Stack Overflow

How can I extract structure and content from a MariaDB SQL dump?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related