User Controls
Using python(or bash, i'm fine with either) for .sql data parsing.
-
2016-06-03 at 9 PM UTCSo a friend of mine has a a couple of huge DB's and he is trying to extract information relevant to his interests from it in either of the following formats.
DBNAME:username:first name:lastname:email:IP:password:
Preferably, or like this:
examplename:myusername:::myemail::mypassword:mysalt
Where if the last name is missing the script would just omit placing an entry in favor of a : symbol. Although a string with the value "null" would be acceptable as well. Now i know how to use grep to pick out email adresses and IP's but idk how to do the rest. Most sql related python libs and related documentation seem to have to do more with parsing data into *SQL format and building DB's than actually comprehensively extracting data except for the xmllib but the file is in .sql format. Also before you mention AWK/GAWK, that shit looks like absolute gibberish to me but if you know of an awk script i can invoke from a bash script in a "for i in x" loop or similar i would be much obliged. Alternatively and perhaps preferably for me personally, a pythonic solution would be best. -
2016-06-04 at 2:22 AM UTCSo you have a .sql file and you're trying to turn it into this format? What SQL dialect is it? The obvious approach would be execute the sql file against the appropriate database (so like if it came out of mysql, run it against mysql) then use a scripting language with binds to that database to read the info back out and write to the format you're looking for. I'd try sqlite to start because it's part of the python standard lib, you can build dbs in memory, and you don't need an external server (which can be a pain in the ass to set up and you'll have to manage state between runs).
Your alternative is pretty much to try to parse the SQL and extract the data directly. If your sql is very homogenous you could do some simple ad hoc string splitting but any robust solution necessitates a pretty complex parser (I mean maybe not that complex as parsers go, but building any parser from a formal language is, uhh, it's a well understood problem but there's just a lot to learn in that space). -
2016-06-04 at 3:02 AM UTC
So you have a .sql file and you're trying to turn it into this format? What SQL dialect is it? The obvious approach would be execute the sql file against the appropriate database (so like if it came out of mysql, run it against mysql) then use a scripting language with binds to that database to read the info back out and write to the format you're looking for. I'd try sqlite to start because it's part of the python standard lib, you can build dbs in memory, and you don't need an external server (which can be a pain in the ass to set up and you'll have to manage state between runs).
Your alternative is pretty much to try to parse the SQL and extract the data directly. If your sql is very homogenous you could do some simple ad hoc string splitting but any robust solution necessitates a pretty complex parser (I mean maybe not that complex as parsers go, but building any parser from a formal language is, uhh, it's a well understood problem but there's just a lot to learn in that space).
I see, and when i say friend i literally mean an associate of mine who i'm trying to help out not SWIM lol. I'll ask him for a DB, see what's up but he's a stingy motherfucker and it's a pretty valuable DB. -
2016-06-04 at 8:40 AM UTCyou can probably just load it up locally using sqlite. the data output you're after doesn't need a programming language; you can get it out with just regular sql
