1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 4 from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey 5 6 metadata = MetaData() 7 8 user = Table('user', metadata, 9 Column('id', Integer, primary_key=True), 10 Column('name', String(20)), 11 ) 12 13 color = Table('color', metadata, 14 Column('id', Integer, primary_key=True), 15 Column('name', String(20)), 16 ) 17 engine = create_engine("mysql+pymysql://root:qwe@127.0.0.1:3306/chouti_db?charset=utf8", max_overflow=5) 18 19 conn = engine.connect() 20 21 # 创建SQL语句，INSERT INTO "user" (id, name) VALUES (:id, :name) 22 conn.execute(user.insert(),{'id':7,'name':'seven'}) 23 conn.close() 24 25 # sql = user.insert().values(id=123, name='wu') 26 # conn.execute(sql) 27 # conn.close() 28 29 # sql = user.delete().where(user.c.id > 1) 30 31 # sql = user.update().values(fullname=user.c.name) 32 # sql = user.update().where(user.c.name == 'jack').values(name='ed') 33 34 # sql = select([user, ]) 35 # sql = select([user.c.id, ]) 36 # sql = select([user.c.name, color.c.name]).where(user.c.id==color.c.id) 37 # sql = select([user.c.name]).order_by(user.c.name) 38 # sql = select([user]).group_by(user.c.name) 39 40 # result = conn.execute(sql) 41 # print result.fetchall() 42 # conn.close() 43 44 增删改查
pymysql
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql # 创建连接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1', charset='utf8')
# 创建游标
cursor = conn.cursor(cursor = pymysql.Cursors.DictCursor)
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
