· 8 years ago · Jul 05, 2018, 05:20 PM
1#!/usr/bin/env python
2
3import os, sys, tempfile, csv
4import re, inspect, ast
5from subprocess import Popen, PIPE, STDOUT
6
7"""
8Class for dumping data into a SQLite database.
9You will need the sqlite3 command line client in your PATH,
10even if you are on Windows and not UNIX (maybe)
11
12The simplest use case is as follows:
13
14# import the class
15from sqlitemagic import SQLiteMagician
16
17# Your data should be a list of equal length tuples
18data = [(x,) for x in range(100)]
19
20# Create an SQLiteMagician to do stuff for you
21wizzard = SQLiteMagician('my_database.db')
22
23# Conjure a table with all your data populated
24# and the table created with the same name as
25# the variable passed in, so 'data' in this case.
26wizzard.conjure(data)
27
282013-08-10 Matt Oates
29MattOates[AT]gmail[DOT]com
30>:3
31"""
32class SQLiteMagician(object):
33
34 #Default command line client to use to access SQLite3
35 command = "sqlite3"
36
37 #Python to SQLite type map, anything not in here will be mapped to NONE
38 types = {"int":"INTEGER","str":"TEXT","float":"REAL"}
39
40 #Constructor, what db to use
41 def __init__(self,database):
42 self.database = database
43
44 #Using an example data record infer the SQLite field types.
45 #Field names will be made up if a header list was not specified
46 def _get_field_spec(self,data,header=None,fieldname="field"):
47 types = [
48 "NONE" if t == "NoneType" else t
49 for t in [ SQLiteMagician.types.get( type( d ).__name__ )
50 for d in data[0]
51 ]
52 ]
53
54 #If no header was passed in create a load of dumby field titles
55 if header == None:
56 header = ["field%s" % n for n in range(len(data[0]))]
57
58 #Return the fieldspec e.g. "field1 INTEGER, field2 TEXT"
59 return ", ".join(["%s %s" % field for field in zip(header,types)])
60
61 #Convoluted frame stack walk and source scrape to get what the calling statement to a function looked like.
62 #Specifically return the name of the variable passed as parameter found at position pos in the parameter list.
63 def _caller_param_name(pos):
64 #The parameter name to return
65 param = None
66 #Get the frame object for this function call
67 thisframe = inspect.currentframe()
68 try:
69 #Get the parent calling frames details
70 frames = inspect.getouterframes(thisframe)
71 #Function this function was just called from that we wish to find the calling parameter name for
72 function = frames[1][3]
73 #Get all the details of where the calling statement was
74 frame,filename,line_number,function_name,source,source_index = frames[2]
75 #Read in the source file in the parent calling frame upto where the call was made
76 with open(filename) as source_file:
77 head=[source_file.next() for x in xrange(line_number)]
78 source_file.close()
79
80 #Build all lines of the calling statement, this deals with when a function is called with parameters listed on each line
81 lines = []
82 #Compile a regex for matching the start of the function being called
83 regex = re.compile(r'\.?\s*%s\s*\(' % (function))
84 #Work backwards from the parent calling frame line number until we see the start of the calling statement (usually the same line!!!)
85 for line in reversed(head):
86 lines.append(line.strip())
87 if re.search(regex, line):
88 break
89 #Put the lines we have groked back into sourcefile order rather than reverse order
90 lines.reverse()
91 #Join all the lines that were part of the calling statement
92 call = "".join(lines)
93 #Grab the parameter list from the calling statement for the function we were called from
94 match = re.search('\.?\s*%s\s*\((.*)\)' % (function), call)
95 paramlist = match.group(1)
96 #If the function was called with no parameters raise an exception
97 if paramlist == "":
98 raise LookupError("Function called with no parameters.")
99 #Use the Python abstract syntax tree parser to create a parsed form of the function parameter list 'Name' nodes are variable names
100 parameter = ast.parse(paramlist).body[0].value
101 #If there were multiple parameters get the positional requested
102 if type(parameter).__name__ == 'Tuple':
103 #If we asked for a parameter outside of what was passed complain
104 if pos >= len(parameter.elts):
105 raise LookupError("The function call did not have a parameter at postion %s" % pos)
106 parameter = parameter.elts[pos]
107 #If there was only a single parameter and another was requested raise an exception
108 elif pos != 0:
109 raise LookupError("There was only a single calling parameter found. Parameter indices start at 0.")
110 #If the parameter was the name of a variable we can use it otherwise pass back None
111 if type(parameter).__name__ == 'Name':
112 param = parameter.id
113 finally:
114 #Remove the frame reference to prevent cyclic references screwing the garbage collector
115 del thisframe
116 #Return the parameter name we found
117 return param
118
119 #Create a table given a single record of data and perhaps a header of field names
120 def create_table(self,table,data,header=None,header_included=False):
121
122 #Grab the header if its the first record of the data
123 if header_included:
124 header = data.pop(0)
125
126 #Get the field spec for this table
127 field_spec = self._get_field_spec(data,header)
128
129 #Run the query using a subprocess to the sqlite3 command
130 sqlite3 = Popen([SQLiteMagician.command, self.database], stdout=PIPE, stdin=PIPE, stderr=STDOUT)
131 db = sqlite3.communicate(input='CREATE TABLE IF NOT EXISTS %s(%s);\n.quit\n' % (table,field_spec))[0]
132
133 #Dump a variable to an SQLite database using the maximum amount of magic possible
134 def conjure(self,data,table=None,header=None,header_included=False):
135 #If the table name was not specified use stack inspection to see what the data variable was called!
136 #Magical and horrible, but I want an interface thats incredibly simple for the simplest case of dumping a variable.
137 if not table:
138 table = _caller_param_name(0)
139 #Create a table to put the data into
140 self.create_table(table,data,header,header_included)
141 #Dump as normal
142 self.dump(data,table)
143
144 def dump(self,data,table):
145
146 #Name of the pipe, use tempfile to create some random filename usually in /tmp
147 data_pipe = tempfile.mktemp('datapump')
148 #Create the actual pipe 'file' where the OS knows you wanted a pipe type thing
149 os.mkfifo( data_pipe, 0644 )
150
151 #Create a child process to run in parallel
152 child = os.fork()
153
154 #If child is 0 we are the child
155 if child == 0:
156 #Have the child send the command to sqlite3
157 #Create a forked process running sqlite3 with a pipe that we can send commands to
158 sqlite3 = Popen([SQLiteMagician.command, self.database], stdout=PIPE, stdin=PIPE, stderr=STDOUT)
159
160 #Tell sqlite3 to import from our named pipe
161 db = sqlite3.communicate(input='.separator "\\t"\n.import %s %s\n.quit\n' % (data_pipe,table))[0]
162 sys.exit(1)
163
164 #If we are the parent process stream data to the child
165 else:
166 #File handle to pipe to write data into table
167 data_pipe_write_handle = open(data_pipe,'w')
168 #Have the original program send data down the pipe
169 writer = csv.writer(data_pipe_write_handle, delimiter="\t")
170 writer.writerows(data)
171 #Close the pipe once we are done!
172 data_pipe_write_handle.close()
173 #Wait for SQLite3 to finish importing, waiting on all child processes
174 os.wait()
175 #Remove the named pipe file we created because its junk and we dont want a clash
176 os.unlink(data_pipe)