更新postgres工具类

zhanghe06 · zhanghe06 · commit 5a1997365002 · 2015-10-30T16:09:40.000+08:00
diff --git a/tools/postgres.py b/tools/postgres.py
@@ -308,6 +308,8 @@ def update(self, table_name, update_field, condition=None):
             print sql
             cursor = self.conn.cursor()
             cursor.execute(sql)
+            self.conn.commit()
+            print '&#26356;&#26032;&#34892;&#25968;&#65306;%s' % cursor.rowcount
             cursor.close()
             return True
         except Exception, e:
@@ -333,20 +335,76 @@ def delete(self, table_name, condition=None):
             else:
                 sql_condition = ''
             # &#25340;&#25509;sql&#35821;&#21477;
-            sql = 'delete %s %s' % (table_name, sql_condition)
+            sql = 'delete from %s %s' % (table_name, sql_condition)
             print sql
             cursor = self.conn.cursor()
             cursor.execute(sql)
+            self.conn.commit()
+            print '&#21024;&#38500;&#34892;&#25968;&#65306;%s' % cursor.rowcount
             cursor.close()
+            print '&#21024;&#38500;&#25104;&#21151;'
+            return True
+        except Exception, e:
+            print e
+
+    def query_by_sql(self, sql=None):
+        """
+        &#26681;&#25454;sql&#35821;&#21477;&#26597;&#35810;
+        :return:
+        """
+        if self.is_conn_open() is False:
+            print '&#36830;&#25509;&#24050;&#26029;&#24320;'
+            return None
+        if sql is None:
+            print 'sql&#35821;&#21477;&#19981;&#33021;&#20026;&#31354;'
+            return None
+        # &#23433;&#20840;&#24615;&#26657;&#39564;
+        sql = sql.lower()
+        if not sql.startswith('select'):
+            print '&#26410;&#25480;&#26435;&#30340;&#25805;&#20316;'
+            return None
+        try:
+            cursor = self.conn.cursor()
+            cursor.execute(sql)
+            rows = cursor.fetchall()
+            cursor.close()
+            # print rows
+            return rows
+        except Exception, e:
+            print e
+
+    def update_by_sql(self, sql=None):
+        """
+        &#26681;&#25454;sql&#35821;&#21477;[&#22686;&#21024;&#25913;]
+        :return:
+        """
+        if self.is_conn_open() is False:
+            print '&#36830;&#25509;&#24050;&#26029;&#24320;'
+            return False
+        if sql is None:
+            print 'sql&#35821;&#21477;&#19981;&#33021;&#20026;&#31354;'
+            return False
+        # &#23433;&#20840;&#24615;&#26657;&#39564;
+        sql = sql.lower()
+        if not (sql.startswith('update') or sql.startswith('insert') or sql.startswith('delete')):
+            print '&#26410;&#25480;&#26435;&#30340;&#25805;&#20316;'
+            return False
+        try:
+            print sql
+            cursor = self.conn.cursor()
+            cursor.execute(sql)
+            self.conn.commit()
+            print '&#24433;&#21709;&#34892;&#25968;&#65306;%s' % cursor.rowcount
+            cursor.close()
+            print '&#25191;&#34892;&#25104;&#21151;'
             return True
         except Exception, e:
             print e
 
 
 def test_51job():
     """
-    &#27979;&#35797;Mysql&#31867;
-    :return:
+    &#27979;&#35797;51job
     """
     # &#23454;&#20363;&#21270;wl_crawl&#24211;&#30340;&#36830;&#25509;
     wl_crawl = Postgres(db_config_current, 'wl_crawl')
@@ -364,8 +422,12 @@ def test_51job():
 
 
 def test_china_hr():
+    """
+    &#27979;&#35797;china_hr
+    """
     # &#23454;&#20363;&#21270;wl_crawl&#24211;&#30340;&#36830;&#25509;
     wl_crawl = Postgres(db_config_current, 'wl_crawl')
+
     # &#26597;&#35810;&#24635;&#25968;
     count = wl_crawl.get_count('origin_position', ['source_type=6'])
     print 'china_hr&#32844;&#20301; &#21407;&#22987;&#24635;&#35760;&#24405;&#25968;&#65306;%s\n' % count
@@ -375,13 +437,33 @@ def test_china_hr():
     print 'china_hr&#32844;&#20301; &#28165;&#27927;&#24635;&#35760;&#24405;&#25968;&#65306;%s\n' % count
     count = wl_crawl.get_count('online_company', ['source_type=6'])
     print 'china_hr&#20844;&#21496; &#28165;&#27927;&#24635;&#35760;&#24405;&#25968;&#65306;%s\n' % count
-    # &#20851;&#38381;&#25968;&#25454;&#24211;&#36830;&#25509;
-    # wl_crawl.close_conn()
+
     # &#26597;&#35810;&#21333;&#26465;&#35760;&#24405;
     # wl_crawl.output_row('origin_company', ['source_type=6'])
     # wl_crawl.output_row('origin_position', ['source_type=6', 'id=157789'])
     # wl_crawl.output_row('online_position', ['source_type=6', 'id=157789'])
-    wl_crawl.output_rows('online_position', ['source_type=2'])
+    # wl_crawl.output_rows('origin_position', ['source_type=6', 'clean_flag=0'])
+    # wl_crawl.output_rows('online_position', ['source_type=6'])
+    # wl_crawl.output_rows('origin_company', ['source_type=6'])
+    # wl_crawl.output_rows('online_company', ['source_type=6', 'id=134064'])
+    # wl_crawl.output_rows('online_company', ['source_type=6'])
+
+    # &#36824;&#21407;&#28165;&#27927;&#29366;&#24577;
+    if 0:
+        wl_crawl.delete('online_position', ['source_type=6'])
+        wl_crawl.update('origin_position', ['clean_flag=0'], ['source_type=6', 'clean_flag>0'])
+        wl_crawl.delete('online_company', ['source_type=6'])
+        wl_crawl.update('origin_company', ['clean_flag=0'], ['source_type=6', 'clean_flag>0'])
+
+    # &#36824;&#21407;&#25968;&#25454;&#23548;&#20986;&#29366;&#24577;
+    if 0:
+        wl_crawl.update('online_position', ['export_flag=0'])
+    # &#26816;&#26597;&#23383;&#27573;&#20998;&#31867;&#38598;&#21512;
+    if 0:
+        sql = 'select distinct(salary_from) from origin_position where source_type=6'
+        result = wl_crawl.query_by_sql(sql)
+        print json.dumps(result, indent=4, ensure_ascii=False)
+
     # &#20851;&#38381;&#25968;&#25454;&#24211;&#36830;&#25509;(&#27979;&#35797;&#20877;&#27425;&#20851;&#38381;)
     wl_crawl.close_conn()
 
