Export a table inside a SQLite database to CSV
553862
def sqlite_to_csv(
    input_filename,
    table_name,
    output_filename,
    dialect=csv.excel,
    batch_size=10000,
    encoding="utf-8",
    callback=None,
    query=None,
):
    """"""

    # TODO: should be able to specify fields
    # TODO: should be able to specify custom query

    if isinstance(dialect, six.text_type):
        dialect = csv.get_dialect(dialect)

    if query is None:
        query = "SELECT * FROM {}".format(table_name)
    connection = sqlite3.Connection(input_filename)
    cursor = connection.cursor()
    result = cursor.execute(query)
    header = [item[0] for item in cursor.description]
    fobj = open_compressed(output_filename, mode="w", encoding=encoding)
    writer = csv.writer(fobj, dialect=dialect)
    writer.writerow(header)
    total_written = 0
    for batch in rows.plugins.utils.ipartition(result, batch_size):
        writer.writerows(batch)
        written = len(batch)
        total_written += written
        if callback:
            callback(written, total_written)
    fobj.close()
1298151
def export(self, db):
        
        dbnode = ET.Element('database')
    
        if self.include_comment:
            now = datetime.now()
            filepath = db.filepath
            if filepath:
                comment = ET.Comment('Generated by keepassdb from {0} on {1}'.format(filepath, now.strftime("%c")))
            else:
                comment = ET.Comment('Generated by keepassdb on {0}'.format(now.strftime("%c")))
            dbnode.append(comment)
        
        def _date(dt):
            if dt == const.NEVER:
                return 'Never'
            else:
                # 2012-12-20T20:56:56
                return dt.strftime('%Y-%m-%dT%H:%M:%S')
            
        def group_to_xml(group, node):
            gnode = ET.SubElement(node, 'group')
            title = ET.SubElement(gnode, 'title')
            title.text = group.title
            icon = ET.SubElement(gnode, 'icon')
            icon.text = str(group.icon)
            
            for subgroup in group.children:
                group_to_xml(subgroup, gnode)
            
            for entry in group.entries:
                if entry.title == 'Meta-Info' and entry.username == 'SYSTEM':
                    continue
                enode = ET.SubElement(gnode, 'entry')
                ET.SubElement(enode, 'title').text = entry.title
                ET.SubElement(enode, 'username').text = entry.username
                ET.SubElement(enode, 'password').text = entry.password
                ET.SubElement(enode, 'url').text = entry.url
                ET.SubElement(enode, 'comment').text = entry.notes
                ET.SubElement(enode, 'icon').text = str(entry.icon)
                ET.SubElement(enode, 'creation').text = _date(entry.created)
                ET.SubElement(enode, 'lastaccess').text = _date(entry.accessed)
                ET.SubElement(enode, 'lastmod').text = _date(entry.modified)
                ET.SubElement(enode, 'expire').text = _date(entry.expires)
            return gnode
        
        for group in db.root.children:
            dbnode.append(group_to_xml(group, dbnode))
            
        xmlstr = ET.tostring(dbnode)
        if self.prettyprint:
            reparsed = minidom.parseString(xmlstr)
            xmlstr = reparsed.toprettyxml(indent=" ")
        
        return xmlstr
500491
def table_dump(self, table):
        """"""
        if not table: raise ValueError("no table")

        cmds = []
        logger.info('------- dumping table {}'.format(table))
        cmd = self._get_args(
            "pg_dump",
            "--table={}".format(table),
            #"--data-only",
            "--clean",
            "--no-owner",
            "--column-inserts",
        )
        cmds.append((cmd, {}))

        outfile_path = self._get_outfile_path(table)
        cmds.append(('gzip > "{}"'.format(outfile_path), {"shell": True}))

        #cmd += ' | {}'.format(' | '.join(pipes))
        #cmd += ' > {}'.format(outfile_path)

        self._run_cmds(cmds)
        logger.info('------- dumped table {}'.format(table))
        return True
277019
def _save_db():
    """"""
    from pyci.utility import json_serial
    import json
    vms("Serializing DB to JSON in {}".format(datapath))
    with open(datapath, 'w') as f:
        json.dump(db, f, default=json_serial)
1298432
def generate_schema(table, export_fields, output_format, output_fobj):
    

    if output_format in ("csv", "txt"):
        from rows import plugins

        data = [
            {
                "field_name": fieldname,
                "field_type": fieldtype.__name__.replace("Field", "").lower(),
            }
            for fieldname, fieldtype in table.fields.items()
            if fieldname in export_fields
        ]
        table = plugins.dicts.import_from_dicts(
            data, import_fields=["field_name", "field_type"]
        )
        if output_format == "txt":
            plugins.txt.export_to_txt(table, output_fobj)
        elif output_format == "csv":
            plugins.csv.export_to_csv(table, output_fobj)

    elif output_format == "sql":
        # TODO: may use dict from rows.plugins.sqlite or postgresql
        sql_fields = {
            rows.fields.BinaryField: "BLOB",
            rows.fields.BoolField: "BOOL",
            rows.fields.IntegerField: "INT",
            rows.fields.FloatField: "FLOAT",
            rows.fields.PercentField: "FLOAT",
            rows.fields.DateField: "DATE",
            rows.fields.DatetimeField: "DATETIME",
            rows.fields.TextField: "TEXT",
            rows.fields.DecimalField: "FLOAT",
            rows.fields.EmailField: "TEXT",
            rows.fields.JSONField: "TEXT",
        }
        fields = [
            "    {} {}".format(field_name, sql_fields[field_type])
            for field_name, field_type in table.fields.items()
            if field_name in export_fields
        ]
        sql = (
            dedent(
                """
                CREATE TABLE IF NOT EXISTS {name} (
                {fields}
                );
                """
            )
            .strip()
            .format(name=table.name, fields=",\n".join(fields))
            + "\n"
        )
        output_fobj.write(sql)

    elif output_format == "django":
        django_fields = {
            rows.fields.BinaryField: "BinaryField",
            rows.fields.BoolField: "BooleanField",
            rows.fields.IntegerField: "IntegerField",
            rows.fields.FloatField: "FloatField",
            rows.fields.PercentField: "DecimalField",
            rows.fields.DateField: "DateField",
            rows.fields.DatetimeField: "DateTimeField",
            rows.fields.TextField: "TextField",
            rows.fields.DecimalField: "DecimalField",
            rows.fields.EmailField: "EmailField",
            rows.fields.JSONField: "JSONField",
        }
        table_name = "".join(word.capitalize() for word in table.name.split("_"))

        lines = ["from django.db import models"]
        if rows.fields.JSONField in [
            table.fields[field_name] for field_name in export_fields
        ]:
            lines.append("from django.contrib.postgres.fields import JSONField")
        lines.append("")

        lines.append("class {}(models.Model):".format(table_name))
        for field_name, field_type in table.fields.items():
            if field_name not in export_fields:
                continue

            if field_type is not rows.fields.JSONField:
                django_type = "models.{}()".format(django_fields[field_type])
            else:
                django_type = "JSONField()"
            lines.append("    {} = {}".format(field_name, django_type))

        result = "\n".join(lines) + "\n"
        output_fobj.write(result)
1188444
def to_sqlite3(self, location=":memory:"):
        """"""

        def decimal_to_sqlite3(n):
            return int(100*n)

        def sqlite3_to_decimal(s):
            return Decimal(s)/100

        sqlite3.register_adapter(Decimal, decimal_to_sqlite3)
        sqlite3.register_converter("decimal", sqlite3_to_decimal)

        con = sqlite3.connect(location, detect_types=sqlite3.PARSE_COLNAMES |
                sqlite3.PARSE_DECLTYPES)
        cur = con.cursor()
        cur.execute("""create table transactions(
                           id primary key,
                           xfer date,
                           posted date,
                           message text,
                           amount decimal,
                           total decimal)""")
        for t in self:
            cur.execute("INSERT INTO transactions values(?,?,?,?,?,?)",
                (t.index, t.xfer, t.posted, t.message, t.amount, t.total))
        return con
289235
def _create_db(self):
        
        log.debug("Creating sqlite database")
        self._conn = sqlite3.connect(self._db_filename)

        # create table structure
        self._conn.cursor().execute("""
        CREATE TABLE IF NOT EXISTS {}
        (
          ts REAL,
          arbitration_id INTEGER,
          extended INTEGER,
          remote INTEGER,
          error INTEGER,
          dlc INTEGER,
          data BLOB
        )
        """.format(self.table_name))
        self._conn.commit()

        self._insert_template = "INSERT INTO {} VALUES (?, ?, ?, ?, ?, ?, ?)".format(self.table_name)
752150
def table(args):
    
    import csv

    p = OptionParser(table.__doc__)
    p.set_sep(sep=",")
    opts, args = p.parse_args(args)

    if len(args) != 1:
        sys.exit(not p.print_help())

    htmlfile, = args
    page = open(htmlfile).read()
    soup = BeautifulSoup(page)

    for i, tabl in enumerate(soup.findAll('table')):
        nrows = 0
        csvfile = htmlfile.rsplit(".", 1)[0] + ".{0}.csv".format(i)
        writer = csv.writer(open(csvfile, "w"), delimiter=opts.sep)
        rows = tabl.findAll('tr')
        for tr in rows:
            cols = tr.findAll('td')
            if not cols:
                cols = tr.findAll('th')

            row = []
            for td in cols:
                try:
                    cell = "".join(td.find(text=True))
                    cell = unescape(cell)
                except TypeError:
                    cell = ""
                row.append(cell)
            writer.writerow(row)
            nrows += 1
        logging.debug("Table with {0} rows written to `{1}`.".format(nrows, csvfile))
1215926
def convert_to_sqlite(self, destination=None, method="shell", progress=False):
        """"""
        # Display progress bar #
        if progress: progress = tqdm.tqdm
        else:        progress = lambda x:x
        # Default path #
        if destination is None: destination = self.replace_extension('sqlite')
        # Delete if it exists #
        destination.remove()
        # Method with shell and a temp file #
        if method == 'shell':     return self.sqlite_by_shell(destination)
        # Method without a temp file #
        if method == 'object':    return self.sqlite_by_object(destination, progress)
        # Method with dataframe #
        if method == 'dataframe': return self.sqlite_by_df(destination, progress)
28949
def to_csv(columns: nil)
      attributes = if columns
                     self.columns & columns.map(&:to_sym)
                   else
                     self.columns
                   end

      ::CSV.generate_line(attributes)
    end
92464
function saveDB (sql) {
        let data = sql.export();
        let buffer = new Buffer(data);

        mkdirp.sync(dbDirPath, '0755');
        fs.writeFileSync(dbFilePath, buffer);
    }
942935
def database_to_SimaPro_csv(self):

        
        self.parameter_scan()

        csv_args = {}
        csv_args['processes'] = []
        db = self.database['items']
        
        product_filter = lambda x: db[x]['type'] == 'product'
        process_filter = lambda x: db[x]['type'] == 'process'

        processes = list(filter(process_filter, db))
        products = list(filter(product_filter, db))

        created_exchanges = []

        project_input_params = []
        project_calc_params = []

        for k in processes:
            item = db[k]

            current = {}
            current['name'] = item['name']
            current['id'] = (self.name.replace(" ", "") + "XXXXXXXX")[:8] + ('00000000000' + str(randint(1, 99999999999)))[-11:]
            current['unit'] = item['unit']
            current['exchanges'] = []
            
            process_params = []
            
            production_filter = lambda x: x['type'] == 'production'
            
            output_code = list(filter(production_filter, item['exchanges']))[0]['input'][1]
            
            for e in item['exchanges']:

                if e['type'] == 'technosphere':
                    this_exchange = {}

                    this_code = e['input'][1]

                    formatted_name = self.get_name(this_code)
                    this_exchange['formatted_name'] = formatted_name
                    
                    param_key = (this_code,  output_code)
                    #print(param_key)
                    #param_check = (formatted_name, item['name'])
                    this_param = self.parameter_map[param_key]
                    
                    process_params.append(this_param)
                   
                    this_exchange['amount'] = this_param 

                    this_exchange['unit'] = self.get_unit(this_code)

                    current['exchanges'].append(this_exchange)

                elif e['type'] == 'production':
                    this_code = e['input'][1]
                    name = self.get_name(this_code)
                    current['output_name'] = name

                    created_exchanges.append(name)
                    
                # process parameters
                
            for p in process_params:
                if self.params[p]['function'] is None:
                    project_input_params.append({'name': p, 'comment': self.params[p]['description']})
                else:
                    project_calc_params.append({'name': p, 'comment': self.params[p]['description'], 'formula': self.params[p]['function']})

            csv_args['processes'].append(current)
            
        for k in products:
            this_item = db[k]
            this_name = this_item['name']
            
            if this_item['name'] in created_exchanges:
                #print ('{} already created'.format(this_name))
                pass
            else:
                #print ('Need to create {}'.format(this_name))
                
                current = {}
                current['name'] = this_name
                current['output_name'] = this_name
                current['id'] = (self.name.replace(" ", "") + "XXXXXXXX")[:8] + ('00000000000' + str(randint(1, 99999999999)))[-11:]
                current['unit'] = this_item['unit']
                #current['exchanges'] = []
                
                if 'ext_link' in this_item.keys():
                    
                    ext_link = this_item['ext_link']
                    if ext_link[0] != self.database['name']:
                        db_filter = lambda x: x['name'] == ext_link[0]
                        extdb = list(filter(db_filter, self.external_databases))[0]['items']

                        ext_item = extdb[ext_link]
                        if ext_link[0] != self.biosphereName:
                            ref_prod = ext_item['reference product']
                            name = ext_item['name'].replace(" " + ref_prod, "")
                            location = ext_item['location']
                            system_model = "Alloc Def"
                            process_type = "U"
                            unit = unnormalise_unit(ext_item['unit'])

                            simaPro_name = "{} {{{}}}| {} | {}, {}".format(ref_prod.capitalize(), location, name, system_model, process_type)

                            #print ('{} has an external link to {}'.format(this_name, simaPro_name))
                            
                            current['exchanges'] = [{'formatted_name': simaPro_name, 'unit': unit, 'amount': 1}]
                        else:
                            #print('{} has a biosphere exchange - need to sort this out'.format(this_name))
                            #print(ext_item)
                            unit = unnormalise_unit(ext_item['unit'])
                            formatted_name = ext_item['name']

                            if 'air' in ext_item['categories']:
                                current['air_emissions'] = [{'formatted_name': formatted_name, 'subcompartment': '', 'unit': unit, 'amount': 1, 'comment': 'emission of {} to air'.format(formatted_name)}]

                            elif 'water' in ext_item['categories']:
                                current['water_emissions'] = [{'formatted_name': formatted_name, 'subcompartment': '', 'unit': unit, 'amount': 1, 'comment': 'emission of {} to water'.format(formatted_name)}]

                            elif 'soil' in ext_item['categories']:
                                current['soil_emissions'] = [{'formatted_name': formatted_name, 'subcompartment': '', 'unit': unit, 'amount': 1, 'comment': 'emission of {} to soil'.format(formatted_name)}]

                            else:
                                print('{} has a biosphere exchange that isnt to air water or soil')
                                print(ext_item)
                        
                    else:
                        warnings.warn('{} has NO internal or external link - it is burden free'.format(this_name))
                    
                csv_args['processes'].append(current)
                created_exchanges.append(this_name)
            
        #print(csv_args)
        #print(created_exchanges)
        
        csv_args['project'] = {}
        
        #NOTE - currently external parameters can only be constants

        csv_args['project']['calculated_parameters'] = project_calc_params
        
        #add the external parameters to the input parameter list        
        for p in self.ext_params:
            project_input_params.append({'name': p['name'], 'comment': p['description'], 'default': p['default']})
        
        csv_args['project']['input_parameters'] = project_input_params

        #print (csv_args)

        env = Environment(
            loader=PackageLoader('lcopt', 'templates'),
        )

        filename = "{}_database_export.csv".format(self.name.replace(" ", "_"))

        csv_template = env.get_template('export.csv')
        
        output = csv_template.render(**csv_args)

        if self.save_option == 'curdir':
            base_dir = os.getcwd()
        else:
            base_dir = os.path.join(storage.simapro_dir, self.name.replace(" ", "_"))

        if not os.path.isdir(base_dir):
            os.mkdir(base_dir)

        efn = os.path.join(base_dir, filename)

        with open(efn, "w") as f:
            f.write(output)
        
        return efn
145585
def sqlite_by_object(self, destination, progress):
        """"""
        db = SQLiteDatabase(destination)
        db.create()
        for script in self.sqlite_dump_string(progress): db.cursor.executescript(script)
        db.close()
1139824
def db_create_table(self, table_name, columns):
        
        formatted_columns = ''
        for col in set(columns):
            formatted_columns += '"{}" text, '.format(col.strip('"').strip('\''))
        formatted_columns = formatted_columns.strip(', ')

        create_table_sql = 'CREATE TABLE IF NOT EXISTS {} ({});'.format(
            table_name, formatted_columns
        )
        try:
            cr = self.db_conn.cursor()
            cr.execute(create_table_sql)
        except sqlite3.Error as e:
            self.handle_error(e)
1361858
def table2csv(table_data):
    
    text_data = [tuple(value2json(vals, pretty=True) for vals in rows) for rows in table_data]

    col_widths = [max(len(text) for text in cols) for cols in zip(*text_data)]
    template = ", ".join(
        "{{" + text_type(i) + "|left_align(" + text_type(w) + ")}}"
        for i, w in enumerate(col_widths)
    )
    text = "\n".join(expand_template(template, d) for d in text_data)
    return text
798221
def sqlite_dump_shell(self, script_path):
        """"""
        # First the schema #
        shell_output('mdb-schema "%s" sqlite >> "%s"' % (self.path, script_path))
        # Start a transaction, speeds things up when importing #
        script_path.append("\n\n\nBEGIN TRANSACTION;\n")
        # Then export every table #
        for table in self.tables:
            command = 'mdb-export -I sqlite "%s" "%s" >> "%s"'
            shell_output(command % (self.path, table, script_path))
        # End the transaction
        script_path.append("\n\n\nEND TRANSACTION;\n")
18020
def sqlite_dump_string(self, progress):
        """"""
        # First the schema #
        mdb_schema = sh.Command("mdb-schema")
        yield mdb_schema(self.path, "sqlite").encode('utf8')
        # Start a transaction, speeds things up when importing #
        yield "BEGIN TRANSACTION;\n"
        # Then export every table #
        mdb_export = sh.Command("mdb-export")
        for table in progress(self.tables):
            yield mdb_export('-I', 'sqlite', self.path, table).encode('utf8')
        # End the transaction
        yield "END TRANSACTION;\n"