標籤:postgre div one 轉換 category row fetchall 直接 panda
1 import pandas as pd 2 import psycopg2 3 from io import StringIO 4 import pymysql 5 conf={"mysql_form":{ 6 ‘host‘ : ‘localhost‘, 7 ‘port‘ : 3306, 8 ‘db‘ : ‘pricefluctuation‘, 9 ‘user‘ : ‘root‘,10 ‘passwd‘ : ‘huzu_mysql_QazWsx12#‘,11 ‘charset‘:‘utf8‘,12 },13 "mysql_test":{14 ‘host‘ : ‘192.168.2.251‘,15 ‘port‘ : 3306,16 ‘db‘ : ‘pricefluctuation‘,17 ‘user‘ : ‘hewen‘,18 ‘passwd‘ : ‘meiyoumima‘,19 ‘charset‘:‘utf8‘,20 },21 "postgres":{22 ‘host‘ : "120.79.135.9",23 ‘port‘ : "5432",24 ‘user‘ : "postgres",25 ‘password‘ : ‘33068080‘,26 27 },28 }29 def sql_to_df():30 con=pymysql.connect(**conf["mysql_form"])31 sql="select category_id,name,gameid,img_url from category where category_id in (11665,9653,6504) "32 try:33 with con.cursor() as cur:34 cur.execute(sql)35 datas=cur.fetchall()36 finally:37 con.close()38 df=pd.DataFrame(list(datas))39 return df40 41 def df_to_pg(df=None,db="spider",table=‘price_730‘):42 #這裡產生的StringIO類似於file檔案,可read,可write,不同的是file是在硬碟上的檔案,而StringIO是在記憶體中的檔案43 output=StringIO()44 #也可以直接將字串轉換成記憶體檔案 ,45 #將資料儲存到‘檔案’46 if not df :47 df=sql_to_df()48 df.to_csv(output,sep=‘\t‘,index=False,header=False)49 #使檔案定位到第一個字元50 output.seek(0)51 try:52 conn=psycopg2.connect(database=db,**conf["postgres"])53 cur=conn.cursor()54 cur.copy_from(output,table,null=‘‘)55 conn.commit()56 result=cur.rowcount57 finally:58 cur.close()59 conn.close()60 return result61 62 if __name__==‘__main__‘:63 # df=pd.read_csv(‘e:/730_price.csv‘)64 print(df_to_pg(table="category"))65
mysql 到postgresql