{"id":102901,"date":"2020-05-13T08:43:16","date_gmt":"2020-05-13T00:43:16","guid":{"rendered":"http:\/\/4563.org\/?p=102901"},"modified":"2020-05-13T08:43:16","modified_gmt":"2020-05-13T00:43:16","slug":"%e5%9f%ba%e4%ba%8e-sqlalchemy-%e9%80%a0%e4%ba%86%e4%b8%80%e4%b8%aa%e6%98%93%e7%94%a8%e7%9a%84%e5%b0%8f%e5%9e%8b-orm-%e8%bd%ae%e5%ad%90","status":"publish","type":"post","link":"http:\/\/4563.org\/?p=102901","title":{"rendered":"\u57fa\u4e8e sqlalchemy \u9020\u4e86\u4e00\u4e2a\u6613\u7528\u7684\u5c0f\u578b ORM \u8f6e\u5b50"},"content":{"rendered":"
\n
\n
\n

\u57fa\u4e8e sqlalchemy \u9020\u4e86\u4e00\u4e2a\u6613\u7528\u7684\u5c0f\u578b ORM \u8f6e\u5b50 <\/h1>\n

<\/p>\n

\n
\u8cc7\u6df1\u5927\u4f6c : skyduy <\/span> <\/i> 54<\/span> <\/div>\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n
<\/p>\n

\u6e90\u7801<\/h2>\n

https:\/\/github.com\/skyduy\/dblink<\/p>\n

\u5b89\u88c5\uff1apip install dblink<\/code><\/p>\n

\u7279\u6027<\/h2>\n
    \n
  • \u53ef\u4ee5\u5bf9\u73b0\u6709\u6570\u636e\u5e93\u8fde\u63a5\uff0c\u65b9\u4fbf\u5728\u5404\u79cd\u573a\u666f\u4f7f\u7528<\/li>\n
  • \u7b80\u5355\u6613\u7528\uff0c\u63a5\u53e3\u7c7b\u4f3c Django-ORM<\/li>\n
  • \u652f\u6301\u6279\u91cf\u589e\u5220\u6539\u67e5<\/li>\n<\/ul>\n

    \u7b80\u4ecb<\/h2>\n

    \u5047\u8bbe\u6570\u636e\u5e93\u4e2d\u6709\u4e24\u5f20\u5df2\u5b58\u5728\u7684\u8868users<\/code>\u548caddress<\/code>:<\/p>\n

    CREATE TABLE users (     id INTEGER NOT NULL,     name VARCHAR(50),     fullname VARCHAR(50),     password VARCHAR(12),     PRIMARY KEY (id) );  CREATE TABLE addresses (     id INTEGER NOT NULL,     email_address VARCHAR NOT NULL,     user_id INTEGER,     PRIMARY KEY (id),     FOREIGN KEY(user_id) REFERENCES users (id) ); <\/code><\/pre>\n
    \u4f60\u53ef\u4ee5\u4f7f\u7528 Database Urls \u8fdb\u884c\u8fde\u63a5\u6570\u636e\u5e93\u3002<\/p>\n
    from dblink import Database, Table # \u624b\u52a8\u5173\u95ed\u8fde\u63a5 db = Database(url='sqlite:\/\/\/:memory:') user_table = Table('users', db) # \u589e\u5220\u6539\u67e5\u64cd\u4f5c db.close()  # \u63a8\u8350\u4f7f\u7528 with \u81ea\u52a8\u7ba1\u7406 with Database(url='postgresql:\/\/scott:[email protected]\/mydatabase') as db:     address_table = Table('addresses', db)     # \u589e\u5220\u6539\u67e5\u64cd\u4f5c <\/code><\/pre>\n
    \u4e0b\u9762\u662f\u4f7f\u7528\u4f8b\u5b50<\/p>\n
    \"\"\" Suppose you have two table: users and addresses. \"\"\" from dblink import Database, Table   with Database('sqlite:\/\/\/:memory:') as db:     table_user = Table('users', db)     table_address = Table('addresses', db)      # show description     print(table_user.description)     print(table_address.description)      # chain query, you can call delete on the single table result     table_user.query.filter(id=1).one_or_none()      table_user.query.filter(id__gte=2)                      .order_by('name')                      .values_list('id', 'name')      table_user.query.filter(id__in=[1, 2, 3])                      .filter(name__startswith='Yu').all()      table_user.query.order_by('-name')                      .values_list('fullname', flat=True, distinct=True)      table_user.query.distinct('name').values_list('name', flat=True)      table_user.query.filter(id__in=[1, 2, 3]).delete()      # join query     table_user.join(table_address)                .filter(id__lt=10000)                .filter(email_address__contains='gmail')                .filter(**{'addresses.id__gte': 100})                .values_list('user_id', 'name', 'email_address',                            table_address.id, 'users.fullname')      # get or insert     instance, create = table_user.get_or_insert(id=1, name='jone')      # single record operation.     table_user.insert({'id': 1, 'name': 'XiaoHong', 'password': 'psw'})      table_user.update({'id': 1, 'name': 'skyduy', 'password': 'psw'},                       unique_fields=['id'], update_fields=['name', 'password'])      table_user.insert_or_update(         {'id': 1, 'name': 'skyduy', 'password': 'psw'},         unique_fields=['id'], update_fields=['name', 'password']     )      table_user.delete({'id': 1, 'name': \"I don't matter\"},                       unique_fields=['id'])      # bulk operation     items = [{'id': 1, 'name': 'XiaoHong', 'password': 'haha'},              {'id': 2, 'name': 'skyduy', 'password': 'aha'},]     unique_fields = ['id']     update_fields = ['name']     table_user.bulk_insert(items)     table_user.bulk_delete(items, unique_fields)     table_user.bulk_update(items, unique_fields, update_fields)     table_user.bulk_insert_or_update(items, unique_fields, update_fields) <\/code><\/pre>\n<\/p><\/div>\n
     \u5927\u4f6c\u6709\u8a71\u8aaa<\/b> (3<\/span>)        <\/div>\n
     <\/div>\n<\/p><\/div>\n<\/p><\/div>\n
      \n
    • \n
      \n
      \n
       \u8cc7\u6df1\u5927\u4f6c : qile1 <\/span>  <\/div>\n
       <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                   \u652f\u6301\u5fae\u8f6f\u6570\u636e\u5e93 mssql \u4e0d\uff1f\u4e0d\u662f mysql\uff0csqlserver \u90a3\u79cd\u7684\u597d\u50cf\u67e5\u8be2\u4e0d\u4e00\u6837\uff01                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
    • \n
      \n
      \n
       \u8cc7\u6df1\u5927\u4f6c : CallMeReznov <\/span>  <\/div>\n
       <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                   sqllite \u9ed8\u8ba4\u7684\u5e93\u4e00\u822c\u8bb2\u9053\u7406\u9700\u6c42\u548c\u5199\u8d77\u6765\u90fd\u6bd4\u8f83\u5bb9\u6613\u5b9e\u73b0,\u4e3b\u8981\u75db\u70b9\u8fd8\u5728\u4e8e\u591a\u7ebf\u7a0b\u7684\u8fde\u63a5\u6c60\u7ba1\u7406\u4e0a
      \u6211\u524d\u51e0\u5929\u76f4\u63a5\u5c31\u662f\u7528\u7684\u81ea\u5e26\u7684\u5e93,\u4e5f\u5c31\u4e00\u4e2a\u7ebf\u7a0b\u5c31\u75af\u72c2\u62a5\u9519
      \u6700\u540e\u8fd8\u662f\u4e0a\u4e86 PersistentDB                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
    • \n
      \n
      \n
       \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : skyduy <\/span>  <\/div>\n
       <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                   @qile1 
      \u56e0\u4e3a\u8fd9\u4e2a\u662f\u57fa\u4e8e SQLAlchemy \u5c01\u88c5\u7684\uff0c\u6240\u4ee5\u652f\u6301\u3002<\/p>\n
      \u66f4\u6539 Database URL \u5373\u53ef\uff0c\u8be6\u89c1\uff1a https:\/\/docs.sqlalchemy.org\/en\/13\/core\/engines.html#microsoft-sql-server                                                            <\/p><\/div>\n<\/p><\/div>\n<\/li>\n
    • \n","protected":false},"excerpt":{"rendered":"
      \u57fa\u4e8e sqlalchemy \u9020\u4e86\u4e00…<\/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\/102901"}],"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=102901"}],"version-history":[{"count":0,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/102901\/revisions"}],"wp:attachment":[{"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=102901"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=102901"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=102901"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}