{"id":92640,"date":"2020-03-15T19:38:27","date_gmt":"2020-03-15T11:38:27","guid":{"rendered":"http:\/\/4563.org\/?p=92640"},"modified":"2020-03-15T19:38:27","modified_gmt":"2020-03-15T11:38:27","slug":"%e4%b8%ba%e4%ba%86%e7%bb%83%e4%b9%a0%e6%89%93%e5%8c%85%e5%88%b0-pypi%ef%bc%8c%e5%86%99%e4%ba%86%e4%b8%80%e4%b8%aa%e5%a4%9a%e7%ba%bf%e7%a8%8b%e7%89%88%e7%9a%84%e5%bf%ab%e9%80%9f%e8%af%bb-sql-%e5%88%b0","status":"publish","type":"post","link":"http:\/\/4563.org\/?p=92640","title":{"rendered":"\u4e3a\u4e86\u7ec3\u4e60\u6253\u5305\u5230 pypi\uff0c\u5199\u4e86\u4e00\u4e2a\u591a\u7ebf\u7a0b\u7248\u7684\u5feb\u901f\u8bfb sql \u5230 DataFrame()\uff0c\u5199 sql \u5230 csv \u4ee5\u53ca\u5feb\u901f\u8fc1\u79fb\u8868\u7684 pip \u5305\u3002"},"content":{"rendered":"<div>\n<div>\n<div>\n<h1>                  \u4e3a\u4e86\u7ec3\u4e60\u6253\u5305\u5230 pypi\uff0c\u5199\u4e86\u4e00\u4e2a\u591a\u7ebf\u7a0b\u7248\u7684\u5feb\u901f\u8bfb sql \u5230 DataFrame()\uff0c\u5199 sql \u5230 csv \u4ee5\u53ca\u5feb\u901f\u8fc1\u79fb\u8868\u7684 pip \u5305\u3002               <\/h1>\n<p> <\/p>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : wuwukai007 <\/span>  <span><i><\/i> 72<\/span> <\/div>\n<div> <\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<div isfirst=\"1\"> <\/p>\n<h4>\u5e26\u6709\u8fdb\u5ea6\u6761\u663e\u793a\u7684 \u591a\u7ebf\u7a0b\u5feb\u8bfb\u5feb\u5199 sql,\u5feb\u901f\u8fc1\u79fb\u8868\u7684 python \u5305<\/h4>\n<p>\u9879\u76ee\u5730\u5740:https:\/\/github.com\/tosmart01\/fastsql<\/p>\n<h5>\u4e00\u3001\u5b89\u88c5<\/h5>\n<ul>\n<li>pip install fast_sql<\/li>\n<\/ul>\n<h5>\u4e8c\u3001\u4f9d\u8d56\u73af\u5883<\/h5>\n<ul>\n<li>python3.6+<\/li>\n<li>mysql | oracle<\/li>\n<\/ul>\n<h5>\u4e8c\u3001\u793a\u4f8b<\/h5>\n<ul>\n<li>\u5feb\u8bfb\u8bfb\u53d6 sql \u751f\u6210 DataFrame<\/li>\n<\/ul>\n<pre><code>import fast_sql from sqlalchemy import create_engine sql = \"select * from test where id &lt;1000000\" con = create_engine(\"oracle+cx_oracle:\/\/wuwukai:[email&#160;protected]:1521\/helowin\") res = fast_sql.read_sql(sql,con,show_progress=True,thread_num=8) res.head() <\/code><\/pre>\n<p>image:<\/p>\n<p><img decoding=\"async\" src=\"http:\/\/4563.org\/wp-content\/uploads\/2020\/05\/20200519_5ec4490a777af.png\" alt=\"\u4e3a\u4e86\u7ec3\u4e60\u6253\u5305\u5230 pypi\uff0c\u5199\u4e86\u4e00\u4e2a\u591a\u7ebf\u7a0b\u7248\u7684\u5feb\u901f\u8bfb sql \u5230 DataFrame()\uff0c\u5199 sql \u5230 csv \u4ee5\u53ca\u5feb\u901f\u8fc1\u79fb\u8868\u7684 pip \u5305\u3002\" \/><\/p>\n<ul>\n<li>\n<p>\u5feb\u901f\u8bfb sql \u751f\u6210 csv<\/p>\n<pre><code>sql = \"select * from test where id &lt;1000000\" path = '\/home\/test.csv' con = create_engine(\"oracle+cx_oracle:\/\/wuwukai:[email&#160;protected]:1521\/helowin\") res = fast_sql.to_csv(sql,con,path_or_buf=path,                      show_progress=True,thread_num=8,index=None)  <\/code><\/pre>\n<\/li>\n<li>\n<p>\u5feb\u901f\u8868\u8fc1\u79fb<\/p>\n<pre><code>#1.\u5728\u7ebf\u8fc1\u79fb sql = \"select * from student where id &lt;1000000\" con = create_engine(\"oracle+cx_oracle:\/\/wuwukai:[email&#160;protected]:1521\/helowin\") to_db = create_engine(\"mysql+pymysql:\/\/root:[email&#160;protected]:3306\/aps_2\") res = fast_sql.to_sql(sql,from_db=con,to_db=to_db,if_exists='delete',               mode='rw',to_table='stu',delete_cache=True,show_progress=True,) <\/code><\/pre>\n<p><img decoding=\"async\" src=\"http:\/\/4563.org\/wp-content\/uploads\/2020\/05\/20200519_5ec4490bd05ce.png\" alt=\"\u4e3a\u4e86\u7ec3\u4e60\u6253\u5305\u5230 pypi\uff0c\u5199\u4e86\u4e00\u4e2a\u591a\u7ebf\u7a0b\u7248\u7684\u5feb\u901f\u8bfb sql \u5230 DataFrame()\uff0c\u5199 sql \u5230 csv \u4ee5\u53ca\u5feb\u901f\u8fc1\u79fb\u8868\u7684 pip \u5305\u3002\" \/><\/p>\n<pre><code># 2.\u79bb\u7ebf\u8fc1\u79fb\uff0c\u5148\u5e8f\u5217\u5316\u5230\u672c\u5730\uff0c\u5728\u8fc1\u79fb sql = \"select * from student where id &lt;1000000\" con = create_engine(\"oracle+cx_oracle:\/\/wuwukai:[email&#160;protected]:1521\/helowin\") to_db = create_engine(\"mysql+pymysql:\/\/root:[email&#160;protected]:3306\/aps_2\") # \u5199\u5165\u672c\u5730\uff0c\u4fdd\u5b58\u5728\u5f53\u524d\u5de5\u4f5c\u76ee\u5f55 res = fast_sql.to_sql(sql,from_db=con,              mode='r',to_table='stu',show_progress=True,) # \u5165\u5e93\uff0cfile_path \u4e3a\u4fdd\u5b58\u7684\u76ee\u5f55 res = fast_sql.to_sql(sql,to_db=con,file_path='\/home\/test'             mode='w',to_table='stu',show_progress=True,)  <\/code><\/pre>\n<\/li>\n<\/ul>\n<h5>\u4e09\u3001\u53c2\u6570\u8bf4\u660e<\/h5>\n<pre><code>\u5176\u4ed6\u53c2\u6570\u517c\u5bb9 pandas read_sql,to_csv sql: sql \u8bed\u53e5 con: \u6570\u636e\u5e93\u8fde\u63a5 sqlalchemy \u8fde\u63a5\u5bf9\u8c61 | sqlalchemy \u8fde\u63a5\u5b57\u7b26\u4e32 | pymysql \u8fde\u63a5\u5bf9\u8c61 thread_num: \u5f00\u542f\u7684\u7ebf\u7a0b\u6570\u91cf encoding: \u7f16\u7801 show_progress: \u662f\u5426\u663e\u793a\u8fdb\u5ea6\u6761 from_db: \u6570\u636e\u6e90\u5e93 to_db: \u76ee\u6807\u5e93 if_exists: \u76ee\u6807\u5e93\u76f8\u540c\u6570\u636e\u662f\u5426\u5220\u9664 delete append orthe(\u4e0d\u505a\u5904\u7406) to_table: \u76ee\u6807\u5e93\u8868\u540d\uff0c\u9ed8\u8ba4\u4e0e\u539f\u59cb sql \u8868\u540d\u76f8\u540c mode: r &gt; \u8bfb\u53d6 sql \u5e8f\u5217\u5316\u5230\u672c\u5730       w &gt; \u5c06\u5e8f\u5217\u5316\u7684\u6587\u4ef6\u5165\u5e93       rw &gt; \u4ece\u6e90\u5e93\u8bfb\u53d6\u5199\u5165\u5230\u76ee\u6807\u5e93 file_path: \u8bfb\u53d6\u6570\u636e\u5e8f\u5217\u5316\u8def\u5f84 delete_cache: \u662f\u5426\u5220\u9664\u8fc1\u79fb\u8fc7\u7a0b\u4e2d\u7f13\u5b58\u7684\u5e8f\u5217\u5316\u6587\u4ef6 to_columns: \u6307\u5b9a\u76ee\u6807\u5e93\u7684\u5217\u540d\uff0c\u9ed8\u8ba4\u4e0e\u539f\u5217\u540d\u76f8\u540c      <\/code><\/pre>\n<\/p><\/div>\n<div> <b>\u5927\u4f6c\u6709\u8a71\u8aaa<\/b> (<span>0<\/span>)        <\/div>\n<div> <\/div>\n<\/p><\/div>\n<\/p><\/div>\n<ul>\n<li>\n","protected":false},"excerpt":{"rendered":"<p>\u4e3a\u4e86\u7ec3\u4e60\u6253\u5305\u5230 pypi\uff0c\u5199\u4e86\u4e00\u4e2a&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[],"tags":[],"_links":{"self":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/92640"}],"collection":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=92640"}],"version-history":[{"count":0,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/92640\/revisions"}],"wp:attachment":[{"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=92640"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=92640"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=92640"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}