跳到主要內容

Python-資料庫-mysql-pymysql

Python
資料庫
mysql-pymysql

安裝：
linux、mac：pip3 install PyMySql
windows    : 之後補充

import pymysql.cursors

connection = pymysql.connect(host='ip',
user='root', password='password', db='database_name',charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)

#    CREATE TABLE `users` (
#    `id` int(11) NOT NULL AUTO_INCREMENT,
#    `email` varchar(255) COLLATE utf8_bin NOT NULL,
#    `password` varchar(255) COLLATE utf8_bin NOT NULL,
#    PRIMARY KEY (`id`)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
#AUTO_INCREMENT=1 ;


with connection.cursor() as cursor:
    sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
    cursor.execute(sql, ('ab123ab456g@example.com', 'very-secret'))

connection.commit()

with connection.cursor() as cursor:
    sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
    cursor.execute(sql, ('ab123ab456g@example.com',))
    result = cursor.fetchone()
    print(result)


with connection.cursor() as cursor:
    sql = "Update  `password`=%s FROM `users` WHERE `email`=%s"
    cursor.execute(sql, ('new_password','ab123ab456g@example.com'))

with connection.cursor() as cursor:
    sql = "Delete FROM `users` WHERE `email`=%s"
    cursor.execute(sql, ('ab123ab456g@example.com'))

connection.close()

程式碼說明

匯入module
建立connection
＃字號後面建立table的SQL語法
第一個cursor，Insert
第二個cursor，Search
第三個cursor，Update
第四個cursor，Delete
sql：sql語法
cursor：指標的概念
execute：執行指令
close：一定要關閉

這邊在看的重點是，如何建立連結、如何執行指令
標籤：

留言

張貼留言

這個網誌中的熱門文章

Python-程式優化設計-module

class Objects, values and types CPython implementation detail:  For CPython,  The standard type hierarchy User-defined functions __doc__ The function’s documentation string, or  None  if unavailable; not inherited by subclasses Writable __name__ The function’s name Writable __qualname__ The function’s  qualified name New in version 3.3. Writable __module__ The name of the module the function was defined in, or  None  if unavailable. Writable __defaults__ A tuple containing default argument values for those arguments that have defaults, or  None  if no arguments have a default value Writable __code__ The code object representing the compiled function body. Writable __globals__ A reference to the dictionary that holds the function’s global variables — the global namespace of the module in which the function was defined. Read-only __dict__ The namespace supporting arbitrary function attributes. Writable __closure__ None ...
張貼留言
閱讀完整內容

python-設計模式-原型模式

python 設計模式 原型模式 import copy  class Prototype:     def __init__(self):        self.obj = {}     def Registor(self, key,new_obj):        self.obj[key] = new_obj     def UnRegistor(self, key):        del self.obj[key]    def DeepClone(self, name, **attr):        obj_clone = copy.deepcopy(self.obj.get(name))        obj_clone.__dict__.update(attr)        return obj_clone     def Clone(self, name, **attr):        obj_clone = copy.copy(self.obj.get(name))        obj_clone.__dict__.update(attr)        return obj_clone  class a:     def __init__(self):        self.name = ['a1','a2']  class b:     def __init__(self):        self.name = 'b'...
張貼留言
閱讀完整內容

Python-資料庫-mysql-sqlalchemy

Python 資料庫 mysql-sqlalchemy 安裝 Linux、mac : pip3 install SQLAlchemy windows     :之後補充 第一部分 from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker engine = create_engine('sqlite:///:memory', echo=False) Base = declarative_base() Session = sessionmaker(bind=engine) 第二部分 from test_sqlalchemy import * from sqlalchemy import Column, Integer, String class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) fullname = Column(String) password = Column(String) def __repr(self): return "<User(name={})>".format(self.name) 第三部分 from test_sqlalchemy import * from build_model import * Base.metadata.create_all(engine) user1 = User(name='user1', fullname='user1', password='user1') user2 = User(name='user2', fullname='user2...
張貼留言
閱讀完整內容