Python SQL 操作

MonngoDB

连接

1
2
3
4
5
6
7
8
9
10
import pymongo

# 普通连接
client = MongoClient('localhost', 27017)
# client = MongoClient('mongodb://localhost:27017/')

# 密码连接
client = MongoClient('mongodb://username:password@localhost:27017/dbname')
db = client.mydatabase
# db = client['mydatabase']

增删查改

  • 增(insert)
1
2
3
4
5
6
7
8
9
# 增加一条记录
person = {'name': 'zone','sex':'boy'}
person_id = test.insert_one(person).inserted_id
print(person_id)

# 批量插入
persons = [{'name': 'zone', 'sex': 'boy'}, {'name': 'zone1', 'sex': 'boy1'}]
result = test.insert_many(persons)
print(result.inserted_ids)
  • 删(delete)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
import pymongo

myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]

# 删除地址为“Mountain 21”的文档：
myquery = { "address": "Mountain 21" }
mycol.delete_one(myquery)

# delete_many() 删除多个
myquery = { "address": {"$regex": "^S"} }
x = mycol.delete_many(myquery)
print(x.deleted_count, " documents deleted.")
1
2
3
4
5
6
7
# 删除单条记录
result1 = test.delete_one({'name': 'zone'})
pprint.pprint(result1)

# 批量删除
result1 = test.delete_many({'name': 'zone'})
pprint.pprint(result1)
  • 改(update)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# Valley 345”更改为“Canyon 123” 
myquery = { "address": "Valley 345" }
newvalues = { "$set": { "address": "Canyon 123" } }

mycol.update_one(myquery, newvalues)

#print "customers" after the update:
for x in mycol.find():
    print(x)

# update_many() 更新多个
myquery = { "address": { "$regex": "^S" } }
newvalues = { "$set": { "name": "Minnie" } }

x = mycol.update_many(myquery, newvalues)

print(x.modified_count, "documents updated.")
1
2
3
4
5
6
# 更新单条记录
res = test.update_one({'name': 'zone'}, {'$set': {'sex': 'girl girl'}})
print(res.matched_count)

# 更新多条记录
test.update_many({'name': 'zone'}, {'$set': {'sex': 'girl girl'}})
  • 查(Querry)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import pymongo

myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]

# 查找地址以字母“S”或更高开头的文档：
myquery = { "address": { "$gt": "S" } }

mydoc = mycol.find(myquery)

for x in mydoc:
    print(x)


# 正则表达式过滤
myquery = { "address": { "$regex": "^S" } }

mydoc = mycol.find(myquery)

for x in mydoc:
    print(x)
1
2
3
4
5
6
7
import pprint

# 查找多条记录
pprint.pprint(test.find())

# 添加查找条件
pprint.pprint(test.find({"sex": "boy"}).sort("name"))

排序

  • sort()方法
1
2
results = collection.find().sort('name', pymongo.ASCENDING)
print([result['name'] for result in results])

示例

1
2
3
4
5
6
7
8
9
10
client = pymongo.MongoClient('localhost')
b = client['MaFengWoView']

def save_to_mongo(view):
if db['MaFengWoView'].insert_one(view):
    print('Saving to MongoDB',view)
    return True
return False

 save_to_mongo(view)

MySQL

连接创建

  • 创建一个名为“customers”的表
1
2
3
4
5
6
7
8
9
10
11
12
import mysql.connector

mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
  • 检查表是否存在
1
2
3
4
5
6
mycursor = mydb.cursor()

mycursor.execute("SHOW TABLES")

for x in mycursor:
    print(x)
  • 创建表时创建主键(存在用ALTER TABLE)
1
2
3
4
5
6
7
8
9
10
11
12
import mysql.connector

mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

插入

  • 单行
1
2
3
4
5
6
7
8
9
mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")
  • 多行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
    ('Peter', 'Lowstreet 4'),
    ('Amy', 'Apple st 652'),
    ('Hannah', 'Mountain 21'),
    ('Michael', 'Valley 345'),
    ('Sandy', 'Ocean blvd 2'),
    ('Betty', 'Green Grass 1'),
    ('Richard', 'Sky st 331'),
    ('Susan', 'One way 98'),
    ('Vicky', 'Yellow Garden 2'),
    ('Ben', 'Park Lane 38'),
    ('William', 'Central st 954'),
    ('Chuck', 'Main Road 989'),
    ('Viola', 'Sideway 1633')
    ]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "was inserted.")

Select

  • 所有
1
2
3
4
5
6
7
8
mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
  • 列
1
2
3
4
5
6
7
8
mycursor = mydb.cursor()

mycursor.execute("SELECT name, address FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
     print(x)

参考

许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。

快来参与讨论吧~

本站由 VITAN 使用 Stellar 主题创建。
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议，转载请注明出处。