pythonsqlsqliteparsingmariadb

Extract Table Data and Content from mariadb SQL-Statements with Python and sqlite3?

I have a sql dump file containing an entire maria db that I need to analyze. the dump is multiple GB big.

Problem is that I don't have access to a local db installation and won't get one in an acceptable time frame due to companies IT-Security restrictions.

Can I iterate and execute my dump via python in sqlite3, or extract it's data in a way that I can analyze it?

I've used this code snippet to iterate my dump and at least get all table names in return to get an overview over the db:

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 worked fine, however in my dump statements for creating tables and so on go over multiple lines, so this approach doesn't work neatly anymore. I've wrote the following to get the statements to 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 = ""

I'm wondering what additional steps are needed, since it is both sql databases transforming the SQL-statements should be possible theoretically. Is there any way to execute all the statements in sqlite? Where are the boundaries and caveats to this approach? Does sqlite not support some key concepts of SQL that I need to be aware of in this case? Can I extract the tables and their data in some other form?

Solution

  • 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.