Python和MySQL数据库简单操作

数据库分类

1. SQL（关系型数据库）
   • MySQL
   • Oracle
   • SQL Server
   • DB2
2. NoSQL 这个NoSQL表示是Not Only SQL,而不是No SQL。SQL的本质是保证事物的完整性(高并发的情况下)遵循ACID？，无法水平扩展。因而NoSQL基于CAP，在牺牲事物（有可能丢失）的情况下实行水平扩展，
   • MongoDB
   • Neo4j
   • HBase

MySQL的简单操作

1. 使用Navicat来创建表
2. 使用Python来操作SQL
   • 首先使用开发包，主要有三种mysql-connector、MySQLdb、SQLAlchemy。其中相比mysql-connector，MySQLdb用的比较多。而第一个则是官方给出的方案。
   • 安装方案如下：pip3 install mysql-connector <span>#安装mysql-connector</span>pip3 install Py<a href="https://www.xgss.net/tag/mysql" title="查看“MySQL”所有文章" target="_blank">MySQL</a> <span>#安装MySQLdb 3.x以上的替代方案</span>

mysql-connector实操

1. 创建链接<span>from</span> mysql <span>import</span> connectorconn = connector.connect(host = <span>'localhost'</span>, port = <span>3306</span> , user = <span>'root'</span>, password = <span>'laoduan2008'</span>, charset = <span>'utf8'</span>)conn.autocommit = <span>True</span> <span>#为了不重复输入commit</span> ERROR Record:
   • mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported MySQL8在这里和低版本不兼容，你可以重新安装MySQL（或者用Reconfigure选项），把认证的选项设置为“Use Legacy Authentication Method”， 或者你如果不是必须要用MySQL 8，可以降级到低版本。
2. 创建游标cursor = conn.cursor()sqltext = <span>'insert into users(name, address, email) values ("deamov","beijing","abc@blabla")'</span><span>#设置语句</span>text_temple = <span>'insert into users(name, address, email) values (%s ,%s ,%s)'</span>cursor.executemany(sql_template,userinfo)<span>#如何执行多条语句</span>
3. 执行SQLcursor.execute(sqltext)<span>#执行语句</span>
4. 关闭连接cursor.close()conn.close()

PyMySQL

1. 创建链接<span>import</span> pymysqlconn = pymysql.connect()conn.autocommit(<span>True</span>) ERROR Record
   • pymysql.err.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: NO)") 有可能是root账号的密码这些不能登陆，通过root账号登陆后，分配一个账号类似CREATE USER 'deamov'@'localhost IDENTIFIED WITH mysql_native_password BY 'password';。之后，use your_database_name选择了你要用的数据库后，将权限给你的新的账户grant all privileges on *.* to deamov@localhost。
2. 创建游标cursor = conn.cursor()<span>#mysql-connector用的是弱引用</span><span>#PyMySQL的游标可以在函数里用</span>
3. 执行SQLsqltext = <span>'select * from users'</span>cursor.excute(sqltext)<span>for</span> row <span>in</span> cursor: <span>print</span> row<span>#读取所有的</span><span>for</span> row <span>in</span> cursor.fetchmany(<span>2</span>): <span>print</span> row<span>#读取两条</span><span>from</span> faker <span>import</span> Factoryuserfaker = Factory.create()userinfo = [(userfaker.name(),userfaker.address(),userfaker.email()) <span>for</span> i <span>in</span> range(<span>10</span>)]text_temple = <span>'insert into users(name, address, email) values (%s ,%s ,%s)cursor.executemany(sql_template,userinfo)#批量插入</span>
4. 关闭链接cursor.close()conn.close()

SQLAlchemy

The Python SQL Toolkit and Object Relational Mapper。 ORM(O是面向对象，R是面向关系，M是Map)： 面向对象和面向关系的模拟关联。

• 表映射到类
  • 行映射到对象
  • 列映射到属性

优点如下

1. 隐藏数据库实现
2. 良好的数据库操作接口，简单，学习成本低。
3. 动态数据表映射

开发步骤

1. 创建链接<span>from</span> sqlalchemy <span>import</span> create_engine<span>from</span> sqlalchemy.ext.declarative <span>import</span> declarative_base<span>from</span> sqlalchemy <span>import</span> Column,Integer,String,DateTime,ForeignKeyengine = create_engine(<span>'mysql+mysqldb://deamov:password@127.0.0.1:3306/MySQLTest?charset=utf8'</span>,echo=<span>True</span>)<span>#echo这个参数是把sql的语句打印出来</span>
2. 创建映射(表的设计)Base = declarative_base()<span><span>class</span> <span>User</span><span>(Base)</span>:</span> __tablename__ = <span>'users'</span> id = Column(Integer, primary_key = Ture) name = Column(String(<span>40</span>)) orders = relationship(<span>'Order'</span>)<span><span>class</span> <span>Product</span><span>(Base)</span>:</span> __tablename__ = <span>'products'</span> id = Column(Integer, primary_key = Ture) name = Column(String(<span>40</span>)) orders = relationship(<span>'Order'</span>) <span>#这个orders不是ddl语言，而是针对面向对象编程而言的</span><span><span>class</span> <span>Order</span><span>(Base)</span>:</span> __tablename__ = <span>'orders'</span> id = Column(Integer,primary_key = <span>True</span>) otime = Column(DateTime) uid = Column(Integer, ForeignKey(<span>'users.id'</span>)) pid = Column(Integer, ForeignKey(<span>'products.id'</span>)) <span>#定义外键</span>
3. 初始化映射实例Base.metadata.create_all(engine)
4. 创建会话Session = sessionmaker(engine)session = Session()
5. 持久化对象实例DeamoV = User(name = <span>'DeamoV'</span>)session.add(DeamoV)p1 = Product(name = <span>'p1'</span>)session.add(p1)session.commit()<span>'''------'''</span><span>#DeamoV = session.query(User).filter(User.name=='DeamoV')</span><span>#返回的是一个集合，集合里是选择的实例类</span><span>#p1 = session.query(Product).filter(Product.name=='p1')</span><span>#这里的Product和User都是类的名字</span>o1 = Order(uid=p1.id ,pid=p1.id)<span>#添加了一个关系</span>session.add(o1)orders=DeamoV.orders<span>#这个时候获取这个关系，目前只有一个关系</span><span>for</span> order <span>in</span> orders: print(order.id)