通过python操作mysql和orm操作mysql

python3.0需要安装pymysql

数据连接操作

# Author:danchengimport pymysql#创建链接conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123', db='test')#创建游标cursor = conn.cursor()#执行SQL，并返回收影响行数offect_row = cursor.execute("insert into first(name) values('dancheng')")#提交，不然无法保存新建或者修改的数据conn.commit()#offect_row = cursor.execute("select * from first")#print(cursor.fetchall())#关闭游标cursor.close()#关闭连接conn.close()

多条数据的插入和链接

# Author:danchengimport pymysql#创建链接conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123', db='test')#创建游标cursor = conn.cursor()data = [    ("qidehan"),    ("liushuai"),    ("sunkeqiang")]#插入多条语句cursor.executemany("insert into first(name) values(%s)", data)conn.commit()#关闭游标cursor.close()#关闭连接conn.close()

ORM操作mysql

需要下载sqlalchemy文件包

利用orm创建一个表

# Author:danchengimport sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringengine = create_engine("mysql+pymysql://root:123@localhost/test", encoding='utf-8', echo=True)Base = declarative_base() #生成orm基类class User(Base):    __tablename__ = 'user'      #表名    id = Column(Integer, primary_key=True)    name = Column(String(32))    password = Column(String(64))Base.metadata.create_all(engine)   #创建表结构

添加数据

# Author:danchengimport sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy.orm import sessionmakerengine = create_engine("mysql+pymysql://root:123@localhost/test", encoding='utf-8', echo=True)#添加数据Session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例Session = Session_class()  # 生成session实例 #cursoruser_obj = User(name="dancheng", password="123")  # 生成你要创建的数据对象print(user_obj.name, user_obj.id)  # 此时还没创建对象呢，不信你打印一下id发现还是NoneSession.add(user_obj)  # 把要创建的数据对象添加到这个session里， 一会统一创建print(user_obj.name, user_obj.id)  # 此时也依然还没创建Session.commit()  # 现此才统一提交，创建数据

查询

data = Session.query(User).filter(User.id > 1).filter(User.id < 3).first()

修改

data = Session.query(User).filter(User.id > 1).filter(User.id < 3).first()#print(data)data.name = 'dancheng1'Session.commit()  # 现此才统一提交，创建数据