Python models.Engine类代码示例

    def insert_raster(self, path=None, srid=4326):
        """Import Raster into Postgis Table
        Uses raster2pgsql -Y -M -d -I -s <SRID> <PATH> <SCHEMA>.<DBTABLE>
        | psql -d <DATABASE>
        The sql processed by raster2pgsql is run
        as psql -U postgres -d <gisdb> -f <elev>.sql
        -Y uses COPY to insert data,
        -M VACUUM table,
        -d  Drops the table, recreates insert raster data
        """

        if not path:
            path = Engine.format_data_dir(self)

        raster_sql = "raster2pgsql -Y -M -d -I -s {SRID} \"{path}\" -F -t 100x100 {SCHEMA_DBTABLE}".format(
            SRID=srid,
            path=os.path.normpath(path),
            SCHEMA_DBTABLE=self.table_name())

        cmd_string = """ | psql -U {USER} -d {DATABASE} --port {PORT} --host {HOST} > {nul_dev} """.format(
            USER=self.opts["user"],
            DATABASE=self.opts["database"],
            PORT=self.opts["port"],
            HOST=self.opts["host"],
            nul_dev=os.devnull
        )

        cmd_stmt = raster_sql + cmd_string
        if self.debug:
            print(cmd_stmt)
        Engine.register_tables(self)
        try:
            subprocess.call(cmd_stmt, shell=True)
        except BaseException as e:
            pass

    def insert_data_from_file(self, filename):
        """Use PostgreSQL's "COPY FROM" statement to perform a bulk insert."""
        self.get_cursor()
        ct = len([True for c in self.table.columns if c[1][0][:3] == "ct-"]) != 0
        if (([self.table.cleanup.function, self.table.delimiter,
              self.table.header_rows] == [no_cleanup, ",", 1])
            and not self.table.fixed_width
            and not ct
            and (not hasattr(self.table, "do_not_bulk_insert") or not self.table.do_not_bulk_insert)):
            columns = self.table.get_insert_columns()
            filename = os.path.abspath(filename)
            statement = """
COPY """ + self.table_name() + " (" + columns + """)
FROM '""" + filename.replace("\\", "\\\\") + """'
WITH DELIMITER ','
CSV HEADER;"""
            try:
                self.execute("BEGIN")
                self.execute(statement)
                self.execute("COMMIT")
            except BaseException:
                self.connection.rollback()
                return Engine.insert_data_from_file(self, filename)
        else:
            return Engine.insert_data_from_file(self, filename)

    def insert_data_from_file(self, filename):
        """Calls MySQL "LOAD DATA LOCAL INFILE" statement to perform a bulk 
        insert."""
        self.get_cursor()
        ct = len([True for c in self.table.columns if c[1][0][:3] == "ct-"]) != 0
        if (self.table.cleanup.function == no_cleanup 
            and not self.table.fixed_width 
            and not ct
            and (not hasattr(self.table, "do_not_bulk_insert") or not self.table.do_not_bulk_insert)
            ):
            print ("Inserting data from " + os.path.basename(filename) + "...")
            
            columns = self.table.get_insert_columns()
            statement = """        
LOAD DATA LOCAL INFILE '""" + filename.replace("\\", "\\\\") + """'
INTO TABLE """ + self.table_name() + """
FIELDS TERMINATED BY '""" + self.table.delimiter + """'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\\n'
IGNORE """ + str(self.table.header_rows) + """ LINES
(""" + columns + ")"
            try:
                self.cursor.execute(statement)
            except Exception as e:
                print "Failed bulk insert (%s), inserting manually" % e
                self.disconnect() # If the execute fails the database connection can get hung up
                return Engine.insert_data_from_file(self, filename)
        else:
            return Engine.insert_data_from_file(self, filename)

    def insert_data_from_file(self, filename):
        """Use executemany to perform a high speed bulk insert

        Checks to see if a given file can be bulk inserted, and if so loads
        it in chunks and inserts those chunks into the database using
        executemany.

        """
        CHUNK_SIZE = 1000000
        self.get_cursor()
        ct = len([True for c in self.table.columns if c[1][0][:3] == "ct-"]) != 0
        if (([self.table.cleanup.function, self.table.header_rows] == [no_cleanup, 1])
            and not self.table.fixed_width
            and not ct
            and (not hasattr(self.table, "do_not_bulk_insert") or not self.table.do_not_bulk_insert)
            ):
            columns = self.table.get_insert_columns()
            filename = os.path.abspath(filename)
            try:
                bulk_insert_statement = self.get_bulk_insert_statement()
                with open(filename, 'r') as data_file:
                    data_chunk = data_file.readlines(CHUNK_SIZE)
                    del(data_chunk[:self.table.header_rows])
                    while data_chunk:
                        data_chunk_split = [row.split(self.table.delimiter)
                                            for row in data_chunk]
                        self.cursor.executemany(bulk_insert_statement, data_chunk_split)
                        data_chunk = data_file.readlines(CHUNK_SIZE)
                self.connection.commit()
            except:
                self.connection.rollback()
                return Engine.insert_data_from_file(self, filename)
        else:
            return Engine.insert_data_from_file(self, filename)

    def insert_data_from_file(self, filename):
        """Call MySQL "LOAD DATA LOCAL INFILE" statement to perform a bulk insert."""

        mysql_set_autocommit_off = """SET autocommit=0; SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0; SET sql_log_bin=0;"""
        mysql_set_autocommit_on = """SET GLOBAL innodb_flush_log_at_trx_commit=1; COMMIT; SET autocommit=1; SET unique_checks=1; SET foreign_key_checks=1;"""

        self.get_cursor()
        ct = len([True for c in self.table.columns if c[1][0][:3] == "ct-"]) != 0
        if (self.table.cleanup.function == no_cleanup and
                not self.table.fixed_width and
                not ct and
                (not hasattr(self.table, "do_not_bulk_insert") or not self.table.do_not_bulk_insert)):

            print("Inserting data from " + os.path.basename(filename) + "...")

            columns = self.table.get_insert_columns()
            statement = """
LOAD DATA LOCAL INFILE '""" + filename.replace("\\", "\\\\") + """'
INTO TABLE """ + self.table_name() + """
FIELDS TERMINATED BY '""" + self.table.delimiter + """'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\\n'
IGNORE """ + str(self.table.header_rows) + """ LINES
(""" + columns + ")"
            try:
                self.cursor.execute(mysql_set_autocommit_off)
                self.cursor.execute(statement)

                self.cursor.execute(mysql_set_autocommit_on)
            except Exception as e:
                self.disconnect()  # If the execute fails the database connection can get hung up
                self.cursor.execute(mysql_set_autocommit_on)
                return Engine.insert_data_from_file(self, filename)
        else:
            return Engine.insert_data_from_file(self, filename)

    def create_table(self):
        """Create the table by creating an empty csv file"""
        self.auto_column_number = 1
        table_path = os.path.join(self.opts["data_dir"], self.table_name())
        self.file = open_fw(table_path)
        self.output_file = open_csvw(self.file)
        column_list = self.table.get_insert_columns(join=False, create=True)
        self.output_file.writerow([u'{}'.format(val) for val in column_list])
        self.table_names.append((self.file, table_path))

        # Register all tables created to enable
        # testing python files having custom download function
        Engine.register_tables(self)

 def find_file(self, filename):
     """Checks for the given file and adds it to the list of all files"""
     result = Engine.find_file(self, filename)
     if not hasattr(self, "all_files"):
         self.all_files = set()
     if result:
         self.all_files.add(result)
     return result

 def format_insert_value(self, value, datatype):
     v = Engine.format_insert_value(self, value, datatype)
     if v == 'null': return ""
     try:
         if len(v) > 1 and v[0] == v[-1] == "'":
             v = '"%s"' % v[1:-1]
     except:
         pass
     return v

    def insert_vector(self, path=None, srid=4326):
        """Import Vector into Postgis Table

        -- Enable PostGIS (includes raster)
        CREATE EXTENSION postgis;

        -- Enable Topology
        CREATE EXTENSION postgis_topology;

        -- fuzzy matching needed for Tiger
        CREATE EXTENSION fuzzystrmatch;

        -- Enable US Tiger Geocoder
        CREATE EXTENSION postgis_tiger_geocoder;
        Uses shp2pgsql -I -s <SRID> <PATH/TO/SHAPEFILE> <SCHEMA>.<DBTABLE>
        | psql -U postgres -d <DBNAME>>

        The sql processed by shp2pgsql is run
        as  psql -U postgres -d <DBNAME>>
        shp2pgsql -c -D -s 4269 -i -I
         """
        if not path:
            path = Engine.format_data_dir(self)
        vector_sql = "shp2pgsql -d -I -W \"{encd}\"  -s {SRID} \"{path}\" \"{SCHEMA_DBTABLE}\"".format(
            encd=ENCODING,
            SRID=srid,
            path=os.path.normpath(path),
            SCHEMA_DBTABLE=self.table_name())

        cmd_string = """ | psql -U {USER} -d {DATABASE} --port {PORT} --host {HOST} > {nul_dev} """.format(
            USER=self.opts["user"],
            DATABASE=self.opts["database"],
            PORT=self.opts["port"],
            HOST=self.opts["host"],
            nul_dev=os.devnull
        )
        cmd_stmt = vector_sql + cmd_string
        if self.debug:
            print(cmd_stmt)
        Engine.register_tables(self)
        try:
            subprocess.call(cmd_stmt, shell=True)
        except BaseException as e:
            pass

 def format_insert_value(self, value, datatype):
     if datatype == "bool":
         try:
             if int(value) == 1:
                 return "TRUE"
             elif int(value) == 0:
                 return "FALSE"
         except:
             pass
     return Engine.format_insert_value(self, value, datatype)

 def format_insert_value(self, value, datatype):
     """Formats a value for an insert statement"""
     v = Engine.format_insert_value(self, value, datatype)
     if v == 'null':
         return ""
     try:
         if len(v) > 1 and v[0] == v[-1] == "'":
             v = '"%s"' % v[1:-1]
     except BaseException:
         pass
     return v

 def format_insert_value(self, value, datatype):
     """Format value for an insert statement."""
     v = Engine.format_insert_value(self, value, datatype)
     if v == None:
         return ""
     try:
         if len(v) > 1 and v[0] == v[-1] == "'":
             v = '"%s"' % v[1:-1]
     except:
         pass
     return v

 def format_insert_value(self, value, datatype):
     """Format value for an insert statement."""
     if datatype == "bool":
         try:
             if int(value) == 1:
                 return "TRUE"
             elif int(value) == 0:
                 return "FALSE"
         except BaseException:
             pass
     return Engine.format_insert_value(self, value, datatype)

    def create_table(self):
        """Create a table and commit.

        PostgreSQL needs to commit operations individually.
        Enable PostGis extensions if a script has a non tabular table.
        """
        if self.table and self.table.dataset_type and \
                not self.table.dataset_type == "TabularDataset":
            try:
                # Check if Postgis is installed and EXTENSION are Loaded
                self.execute("SELECT PostGIS_full_version();")
            except BaseException as e:
                print(e)
                print("Make sure that you have PostGIS installed\n"
                      "Open Postgres CLI or GUI(PgAdmin) and run:\n"
                      "CREATE EXTENSION postgis;\n"
                      "CREATE EXTENSION postgis_topology;")
                exit()
            return
        Engine.create_table(self)
        self.connection.commit()

 def convert_data_type(self, datatype):
     """MS Access can't handle complex Decimal types"""
     converted = Engine.convert_data_type(self, datatype)
     if "NUMERIC" in converted:
         converted = "NUMERIC"
     elif "VARCHAR" in converted:
         try:
             length = int(converted.split('(')[1].split(')')[0].split(',')[0])
             if length > 255:
                 converted = "TEXT"
         except:
             pass
     return converted