python&mysql

第一种方法，使用 pymysql库

import pymysql.cursors

# Connect to the database

connection = pymysql.connect(host='localhost',

                             user='root',

                             password='root',

                             db='spider',

                             charset='utf8mb4',

                             cursorclass=pymysql.cursors.DictCursor)

try:

    with connection.cursor() as cursor:

        # Create a new record

        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"

        cursor.execute(sql, ('[email protected]', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save

    # your changes.

    connection.commit()

    with connection.cursor() as cursor:

        # Read a single record

        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"

        cursor.execute(sql, ('[email protected]',))

        result = cursor.fetchone()

        print(result)

    with connection.cursor() as cursor:

        sql = "SELECT COUNT(*) as count FROM `users`"

        cursor.execute(sql)

        result = cursor.fetchone()

        print(result)

finally:

    connection.close()

此方法基于sql语句，较复杂

第二种，使用peewee库，其实peewee是基于pymysql库的一个ORM框架，将表封装成了类，将记录封装成了对象。学习成本较高

from peewee import *

from datetime import date

db = MySQLDatabase("spider",host="127.0.0.1",port=3306,user="root",password="root")

class Person(Model):

    name = CharField(max_length=20,null=True) # 最大长度20，可以为null

    birthday = DateField()

    class Meta:

        database = db

        table_name = "users"

if __name__ == "__main__":

    # 如果没表会创建表

    db.create_tables([Person])

# 新增数据

uncle_bob = Person(name='Bob',birthday=date(1960,1,5))

print(uncle_bob.save())

# 查询数据

# 一条(get方法在取不到数据会抛出异常)

try:

    per = Person.get(Person.name == 'Bob')

except:

    print('娶不到')

else:

    print(per.name)

# 多条

# query是modelselect对象，可以当作list来操作  因为它实现了__getitem魔法方法

query = Person.select().where(Person.name == 'Bob')

print(query)

for person in query:

    person.birthday = date(2020,2,2)

    # 修改数据

    person.save() #在没有数据存在的时候新增数据，存在的时候修改数据

    # 删除数据

    person.delete_instance()

    print('多条：',person.name)