Python总结--基础知识-9

1.创建SQLite数据库

# create table and database
# 可通过sqlitespy打开数据库文件data.sqlite
import sqlite3
import os

dbPath = 'data.sqlite'
if not os.path.exists(dbPath):
    conn = sqlite3.connect(dbPath)  #连接数据库
    c = conn.cursor()     
    # 创建数据库
    c.execute('''create table persons
              (id int primary key not null,
               name text not null,
               age int not null,
               address char(100),
               salary real);''')
    conn.commit()  #将缓存提交到数据库后台保存
    conn.close()     #关闭数据库
    print('创建数据库成功')

# 插入数据到数据库
conn = sqlite3.connect(dbPath)
c = conn.cursor()
c.execute('delete from persons')  #删除原先的数据库内容
# 插入三条数据
c.execute('''
insert into persons(id,name,age,address,salary)
values(1,'Bill',32,'California',20000)
''')
c.execute('''
insert into persons(id,name,age,address,salary)
values(2,'Mike',30,'Texas',10000)
''')

c.execute('''
insert into persons(id,name,age,address,salary)
values(3,'John',45,'Norway',30000)
''')
conn.commit() #提交数据到数据库
print('insert success')

# 3. select排序
persons = c.execute('select name,age,address,salary from persons order by age')
print(type(persons))  #<class 'sqlite3.Cursor'>
result = []
for person in persons:
    value = {}
    value['name'] = person[0]
    value['age'] = person[1]
    value['address'] = person[2]
    result.append(value)
conn.close()
print(type(result)) #<class 'list'>  
print(result)  #[{'name': 'Mike', 'age': 30, 'address': 'Texas'}, {'name': 'Bill', 'age': 32, 'address': 'California'}]

import json
resultStr = json.dumps(result)
print(resultStr) #[{"name": "Mike", "age": 30, "address": "Texas"}, {"name": "Bill", "age": 32, "address": "California"}]

2.创建MySQL数据库

'''
pymysql
pip install pymysql
'''

from pymysql import *

def connectDB():
    db = connect('127.0.0.1','root','12345678','meituan',charset='utf8')
    return db
db = connectDB()
print(type(db))

def createTable(db):
    cursor = db.cursor()
    c = db.cursor()
    try:
        c.execute('''create table persons
                      (id int primary key not null,
                       name text not null,
                       age int not null,
                       address char(100),
                       salary real);''')
        db.commit()
        return True
    except:
        db.rollback()

    return False
if createTable(db):
    print('create table success')
else:
    print('create table failed')

def insertRecords(db):
    cursor = db.cursor()
    try:
        cursor.execute('delete from persons')
        cursor.execute('''
        insert into persons(id,name,age,address,salary)
        values(1,'Bill',32,'California',20000)
        ''')
        cursor.execute('''
        insert into persons(id,name,age,address,salary)
        values(2,'Mike',30,'Texas',10000)
        ''')

        cursor.execute('''
        insert into persons(id,name,age,address,salary)
        values(3,'John',45,'Norway',30000)
        ''')
        db.commit()
        return True
    except Exception as e:
        print(e)
        db.rollback()
    return False
if insertRecords(db):
    print('成功插入记录')
else:
    print('插入记录失败')
import json
def selectRecords(db):
    cursor = db.cursor()
    sql = 'select name,age,salary from persons order by age desc'
    cursor.execute(sql)
    results = cursor.fetchall()
    print(type(results))
    fields = ['name','age','salary']
    records = []
    for row in results:
        records.append(dict(zip(fields,row)))
    return json.dumps(records)


print(selectRecords(db))
db.close()

3.ORM框架

'''
SQLAlchemy和SQLObject是两种常用的ORM框架
SQLAlchemy:偏向于SQL，可以灵活地提交SQL语句
SQLObject：更加面向对象，无法自由使用原生的SQL语句
pip install sqlobject
'''

from sqlobject import *
from sqlobject.mysql import builder
import json

mysql = 'mysql://root:12345678@localhost:3306/meituan?charset=utf8'
sqlhub.processConnection = connectionForURI(mysql,driver='pymysql')

class Person(SQLObject):
    class sqlmeta:
        table = 't_persons'
    name = StringCol(length = 30)
    age = IntCol()
    address = StringCol(length = 30)
    salary = FloatCol()
try:
    Person.dropTable()
except:
    pass
Person.createTable()

print('成功创建了t_persons表')

# 插入记录
person1 = Person(name = 'Bill', age = 55,address='地球',salary=1234)
person2 = Person(name = 'Mike',age = 65,address='月球',salary=4321)
person3 = Person(name = 'John',age = 15,address='火星',salary=4000)
print('成功插入3条记录')

person2.name = '李宁'
person2.address = '木星'

# 查询表数据
persons = Person.selectBy(name = 'Bill')
print(persons[0])
print(persons[0].id)
print(persons[0].name)
print(persons[0].address)

# 删除数据
persons[0].destroySelf()

4.将XML文档保存在MongoDB数据库中

'''
NoSQL = Not Only SQL

1. 键值（key-value）数据库
Redis、Riak、Memcached
适用场景：用来存储用户信息，比如会员、配置文件、参数、购物车等。

2. 文档（Document-Oriented）数据库
MongoDB、CouchDB、RavenDB
适用场景：日志、分析数据

3. 列存储数据库
HBase、Cassandra
适用场景：日志、博客平台，标签可以存储到一列、类别可以存储到另一列、文章可以存储在另外一列

4. 图数据库
Neo4J、OrientDB
适用场景：在一些关系型强的数据看可以使用或推荐引擎

pip install pymongo
'''

from pymongo import *

Client = MongoClient()
db = Client.data
products = db.products
products.delete_many({'price':{'$gt':0}})

import xmltodict

f = open('files/products.xml','rt',encoding='utf-8')
xml = f.read()
f.close()
print(xml)

d = xmltodict.parse(xml)
productList = d['root']['products']['product']
print(productList)

for product in productList:
    product['price'] = int(product['price'])
    productId = products.insert_one(product).inserted_id
    print(productId)

for product in products.find({'price':{'$gt':10000}}):
    print(product)

Python总结–基础知识-10

温馨提示：
以上文章描述如有不清晰之处，欢迎在评论区评论，如有时间，会第一时间回复，谢谢！