多多色-多人伦交性欧美在线观看-多人伦精品一区二区三区视频-多色视频-免费黄色视屏网站-免费黄色在线

國內(nèi)最全I(xiàn)T社區(qū)平臺(tái) 聯(lián)系我們 | 收藏本站
阿里云優(yōu)惠2
您當(dāng)前位置：首頁 > 數(shù)據(jù)庫 > 數(shù)據(jù)庫應(yīng)用 > python 連接各類主流數(shù)據(jù)庫簡(jiǎn)單示例

python 連接各類主流數(shù)據(jù)庫簡(jiǎn)單示例

來源：程序員人生   發(fā)布時(shí)間：2016-06-17 08:27:00 閱讀次數(shù)：3256次

本篇博文主要介紹Python連接各種數(shù)據(jù)庫的方法及簡(jiǎn)單使用
包括關(guān)系數(shù)據(jù)庫：sqlite,mysql,mssql
非關(guān)系數(shù)據(jù)庫：MongoDB,Redis

代碼寫的比較清楚，直接上代碼

1.連接sqlite

# coding=utf⑻ # http://www.runoob.com/sqlite/sqlite-python.html import sqlite3 import traceback try: # 如果表不存在,就創(chuàng)建 with sqlite3.connect('test.db') as conn: print("Opened database successfully") # 刪除表 conn.execute("DROP TABLE IF EXISTS COMPANY") # 創(chuàng)建表 sql = """ CREATE TABLE IF NOT EXISTS COMPANY (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); """ conn.execute(sql) print("create table successfully") # 添加數(shù)據(jù) conn.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES (?, ?, ?, ? )", [('Paul', 32, 'California', 20000.00), ('Allen', 25, 'Texas', 15000.00), ('Teddy', 23, 'Norway', 20000.00), ('Mark', 25, 'Rich-Mond ', 65000.00), ('David', 27, 'Texas', 85000.00), ('Kim', 22, 'South-Hall', 45000.00), ('James', 24, 'Houston', 10000.00)]) # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ( 'Paul', 32, 'California', 20000.00 )") # # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ('Allen', 25, 'Texas', 15000.00 )") # # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ('Teddy', 23, 'Norway', 20000.00 )") # # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 )") # # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ( 'David', 27, 'Texas', 85000.00 )"); # # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ( 'Kim', 22, 'South-Hall', 45000.00 )") # # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ( 'James', 24, 'Houston', 10000.00 )") # 提交,否則重新運(yùn)行程序時(shí),表中無數(shù)據(jù) conn.commit() print("insert successfully") # 查詢表 sql = """ select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY """ result = conn.execute(sql) for row in result: print("-" * 50) # 輸出50個(gè)-,作為分界限 print("%⑴0s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對(duì)齊 print("%⑴0s %s" % ("name", row[1])) print("%⑴0s %s" % ("age", row[2])) print("%⑴0s %s" % ("address", row[3])) print("%⑴0s %.2f" % ("salary", row[4])) # or # print('{:10s} {:.2f}'.format("salary", row[4])) except sqlite3.Error as e: print("sqlite3 Error:", e) traceback.print_exc()

2.連接mysql

2.1使用mysqldb庫中的_mysql

#! /usr/bin/env python2.7 # coding=utf⑻ # Created by xiaosanyu at 16/5/30 # mysqldb 只支持python2.7 # http://mysql-python.sourceforge.net/ import MySQLdb from contextlib import closing import traceback try: # 獲得1個(gè)數(shù)據(jù)庫連接 with closing(MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', port=3306,charset='utf8')) as conn: print("connect database successfully") with closing(conn.cursor()) as cur: # 刪除表 cur.execute("DROP TABLE IF EXISTS COMPANY") # 創(chuàng)建表 sql = """ CREATE TABLE IF NOT EXISTS COMPANY (ID INTEGER PRIMARY KEY NOT NULL auto_increment, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); """ cur.execute(sql) print("create table successfully") # 添加數(shù)據(jù) # 在1個(gè)conn.execute里面里面履行多個(gè)sql語句是非法的 cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )", [('Paul', 32, 'California', 20000.00), ('Allen', 25, 'Texas', 15000.00), ('Teddy', 23, 'Norway', 20000.00), ('Mark', 25, 'Rich-Mond ', 65000.00), ('David', 27, 'Texas', 85000.00), ('Kim', 22, 'South-Hall', 45000.00), ('James', 24, 'Houston', 10000.00)]) # 提交,否則重新運(yùn)行程序時(shí),表中無數(shù)據(jù) conn.commit() print("insert successfully") # 查詢表 sql = """ select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY """ cur.execute(sql) for row in cur.fetchall(): print("-" * 50) # 輸出50個(gè)-,作為分界限 print("%⑴0s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對(duì)齊 print("%⑴0s %s" % ("name", row[1])) print("%⑴0s %s" % ("age", row[2])) print("%⑴0s %s" % ("address", row[3])) print("%⑴0s %s" % ("salary", row[4])) except MySQLdb.Error as e: print("Mysql Error:", e) traceback.print_exc() # 打印毛病棧信息

2.2 使用MySQLdb

#! /usr/bin/env python2.7 # coding=utf⑻ # Created by xiaosanyu at 16/5/30 # mysqldb 只支持python2.7 # http://mysql-python.sourceforge.net/ import MySQLdb from contextlib import closing import traceback try: # 獲得1個(gè)數(shù)據(jù)庫連接 with closing(MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', port=3306,charset='utf8')) as conn: print("connect database successfully") with closing(conn.cursor()) as cur: # 刪除表 cur.execute("DROP TABLE IF EXISTS COMPANY") # 創(chuàng)建表 sql = """ CREATE TABLE IF NOT EXISTS COMPANY (ID INTEGER PRIMARY KEY NOT NULL auto_increment, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); """ cur.execute(sql) print("create table successfully") # 添加數(shù)據(jù) # 在1個(gè)conn.execute里面里面履行多個(gè)sql語句是非法的 cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )", [('Paul', 32, 'California', 20000.00), ('Allen', 25, 'Texas', 15000.00), ('Teddy', 23, 'Norway', 20000.00), ('Mark', 25, 'Rich-Mond ', 65000.00), ('David', 27, 'Texas', 85000.00), ('Kim', 22, 'South-Hall', 45000.00), ('James', 24, 'Houston', 10000.00)]) # 提交,否則重新運(yùn)行程序時(shí),表中無數(shù)據(jù) conn.commit() print("insert successfully") # 查詢表 sql = """ select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY """ cur.execute(sql) for row in cur.fetchall(): print("-" * 50) # 輸出50個(gè)-,作為分界限 print("%⑴0s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對(duì)齊 print("%⑴0s %s" % ("name", row[1])) print("%⑴0s %s" % ("age", row[2])) print("%⑴0s %s" % ("address", row[3])) print("%⑴0s %s" % ("salary", row[4])) except MySQLdb.Error as e: print("Mysql Error:", e) traceback.print_exc() # 打印毛病棧信息

2.3使用pymysql

2.1和2.2節(jié)使用MySQLdb，不支持Python3.x
pymysql對(duì)Python2.x和Python3.x的支持都比較好

# Created by xiaosanyu at 16/5/30 # coding=utf⑻ # https://github.com/PyMySQL/PyMySQL/ import pymysql from contextlib import closing import traceback try: # 獲得1個(gè)數(shù)據(jù)庫連接,with關(guān)鍵字 表示退出時(shí),conn自動(dòng)關(guān)閉 # with 嵌套上1層的with 要使用closing() with closing(pymysql.connect(host='localhost', user='root', passwd='root', db='test', port=3306, charset='utf8')) as conn: print("connect database successfully") # 獲得游標(biāo),with關(guān)鍵字 表示退出時(shí),cur自動(dòng)關(guān)閉 with conn.cursor() as cur: # 刪除表 cur.execute("DROP TABLE IF EXISTS COMPANY") # 創(chuàng)建表 sql = """ CREATE TABLE IF NOT EXISTS COMPANY (ID INTEGER PRIMARY KEY NOT NULL auto_increment, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); """ cur.execute(sql) print("create table successfully") # 添加數(shù)據(jù) # 在1個(gè)conn.execute里面里面履行多個(gè)sql語句是非法的 cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )", [('Paul', 32, 'California', 20000.00), ('Allen', 25, 'Texas', 15000.00), ('Teddy', 23, 'Norway', 20000.00), ('Mark', 25, 'Rich-Mond ', 65000.00), ('David', 27, 'Texas', 85000.00), ('Kim', 22, 'South-Hall', 45000.00), ('James', 24, 'Houston', 10000.00)]) # 提交,否則重新運(yùn)行程序時(shí),表中無數(shù)據(jù) conn.commit() print("insert successfully") # 查詢表 sql = """ select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY """ cur.execute(sql) for row in cur.fetchall(): print("-" * 50) # 輸出50個(gè)-,作為分界限 print("%⑴0s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對(duì)齊 print("%⑴0s %s" % ("name", row[1])) print("%⑴0s %s" % ("age", row[2])) print("%⑴0s %s" % ("address", row[3])) print("%⑴0s %s" % ("salary", row[4])) except pymysql.Error as e: print("Mysql Error:", e) traceback.print_exc()

3.連接mssql

# Created by xiaosanyu at 16/5/30 # http://www.pymssql.org/en/latest/ import pymssql from contextlib import closing try: # 先要保證數(shù)據(jù)庫中有test數(shù)據(jù)庫 # 獲得1個(gè)數(shù)據(jù)庫連接,with關(guān)鍵字 表示退出時(shí),conn自動(dòng)關(guān)閉 # with 嵌套上1層的with 要使用closing() with closing(pymssql.connect(host='192.168.100.114', user='sa', password='sa12345', database='test', port=1433, charset='utf8')) as conn: print("connect database successfully") # 獲得游標(biāo),with關(guān)鍵字 表示退出時(shí),cur自動(dòng)關(guān)閉 with conn.cursor() as cur: # 刪除表 cur.execute( '''if exists (select 1 from sys.objects where name='COMPANY' and type='U') drop table COMPANY''') # 創(chuàng)建表 sql = """ CREATE TABLE COMPANY (ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL , NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); """ cur.execute(sql) print("create table successfully") # 添加數(shù)據(jù) # 在1個(gè)conn.execute里面里面履行多個(gè)sql語句是非法的 cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )", [('Paul', 32, 'California', 20000.00), ('Allen', 25, 'Texas', 15000.00), ('Teddy', 23, 'Norway', 20000.00), ('Mark', 25, 'Rich-Mond', 65000.00), ('David', 27, 'Texas', 85000.00), ('Kim', 22, 'South-Hall', 45000.00), ('James', 24, 'Houston', 10000.00)]) # 提交,否則重新運(yùn)行程序時(shí),表中無數(shù)據(jù) conn.commit() print("insert successfully") # 查詢表 sql = """ select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY """ cur.execute(sql) for row in cur.fetchall(): print("-" * 50) # 輸出50個(gè)-,作為分界限 print("%⑴0s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對(duì)齊 print("%⑴0s %s" % ("name", row[1])) print("%⑴0s %s" % ("age", row[2])) print("%⑴0s %s" % ("address", row[3])) print("%⑴0s %s" % ("salary", row[4])) except pymssql.Error as e: print("mssql Error:", e) # traceback.print_exc()

4.連接MongoDB

# Created by xiaosanyu at 16/5/30 # https://docs.mongodb.com/ecosystem/drivers/python/ # https://pypi.python.org/pypi/pymongo/ import pymongo from pymongo.mongo_client import MongoClient import pymongo.errors import traceback try: # 連接到 mongodb 服務(wù) mongoClient = MongoClient('localhost', 27017) # 連接到數(shù)據(jù)庫 mongoDatabase = mongoClient.test print("connect database successfully") # 獲得集合 mongoCollection = mongoDatabase.COMPANY # 移除所有數(shù)據(jù) mongoCollection.remove() # 添加數(shù)據(jù) mongoCollection.insert_many([{"Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"}, {"Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"}, {"Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"}, {"Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"}, {"Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"}, {"Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"}, {"Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}, ]) #獲得集合中的值 for row in mongoCollection.find(): print("-" * 50) # 輸出50個(gè)-,作為分界限 print("%-10s %s" % ("_id", row['_id'])) # 字段名固定10位寬度,并且左對(duì)齊 print("%-10s %s" % ("name", row['Name'])) print("%-10s %s" % ("age", row['Age'])) print("%-10s %s" % ("address", row['Address'])) print("%-10s %s" % ("salary", row['Salary'])) print('\n\n\n') # 使id自增 mongoCollection.remove() # 創(chuàng)建計(jì)數(shù)表 mongoDatabase.counters.save({"_id": "people_id", "sequence_value": 0}) # 創(chuàng)建存儲(chǔ)進(jìn)程 mongoDatabase.system_js.getSequenceValue = '''function getSequenceValue(sequenceName){ var sequenceDocument = db.counters.findAndModify({ query: {_id: sequenceName}, update: {$inc:{sequence_value: 1}}, new:true }); return sequenceDocument.sequence_value; }''' mongoCollection.insert_many( [{"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}, ]) for row in mongoCollection.find(): print("-" * 50) # 輸出50個(gè)-,作為分界限 print("%-10s %s" % ("_id", int(row['_id']))) # 字段名固定10位寬度,并且左對(duì)齊 print("%-10s %s" % ("name", row['Name'])) print("%-10s %s" % ("age", row['Age'])) print("%-10s %s" % ("address", row['Address'])) print("%-10s %s" % ("salary", row['Salary'])) except pymongo.errors.PyMongoError as e: print("mongo Error:", e) traceback.print_exc()

5.連接Redis

5.1使用redis

# coding=utf⑻ # Created by xiaosanyu at 16/5/31 # https://pypi.python.org/pypi/redis/2.10.5 # http://redis-py.readthedocs.io/en/latest/# import redis r = redis.Redis(host='localhost', port=6379, db=0, password="12345") print("connect", r.ping()) # 看信息 info = r.info() # or 查看部份信息 # info = r.info("Server") # 輸出信息 items = info.items() for i, (key, value) in zip(range(len(items)), items): print("item %s----%s:%s" % (i, key, value)) # 刪除鍵和對(duì)應(yīng)的值 r.delete("company") # 可以1次性push1條或多條數(shù)據(jù) r.rpush("company", {"id": 1, "Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"}, {"id": 2, "Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"}, {"id": 3, "Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"}) r.rpush("company", {"id": 4, "Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"}) r.rpush("company", {"id": 5, "Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"}) r.rpush("company", {"id": 6, "Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"}) r.rpush("company", {"id": 7, "Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}) # eval用來將dict格式的字符串轉(zhuǎn)換成dict for row in map(lambda x: eval(x), r.lrange("company", 0, r.llen("company"))): print("-" * 50) # 輸出50個(gè)-,作為分界限 print("%-10s %s" % ("_id", row['id'])) # 字段名固定10位寬度,并且左對(duì)齊 print("%-10s %s" % ("name", row['Name'])) print("%-10s %s" % ("age", row['Age'])) print("%-10s %s" % ("address", row['Address'])) print("%-10s %s" % ("salary", row['Salary'])) # 關(guān)閉當(dāng)前連接 r.shutdown()

5.2使用pyredis

# Created by xiaosanyu at 16/5/30 # http://pyredis.readthedocs.io/en/latest/ import pyredis r = pyredis.Client(host='localhost', port=6379, database=0, password="12345") print("connect", r.ping().decode("utf⑻")) # 看信息 # info = r.execute("info").decode() # or 查看部份信息 info = r.execute("info", "Server").decode() # 輸出信息 print(info) # 刪除鍵和對(duì)應(yīng)的值 r.delete("company") # 可以1次性push1條或多條數(shù)據(jù) r.rpush("company", '''{"id": 1, "Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"}''', '''{"id": 2, "Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"}''', '''{"id": 3, "Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"}''') r.rpush("company", '''{"id": 4, "Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"}''') r.rpush("company", '''{"id": 5, "Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"}''') r.rpush("company", '''{"id": 6, "Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"}''') r.rpush("company", '''{"id": 7, "Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}''') # eval用來將dict格式的字符串轉(zhuǎn)換成dict for row in map(lambda x: eval(x), r.lrange("company", 0, r.llen("company"))): print("-" * 50) # 輸出50個(gè)-,作為分界限 print("%⑴0s %s" % ("_id", row['id'])) # 字段名固定10位寬度,并且左對(duì)齊 print("%⑴0s %s" % ("name", row['Name'])) print("%⑴0s %s" % ("age", row['Age'])) print("%⑴0s %s" % ("address", row['Address'])) print("%⑴0s %s" % ("salary", row['Salary'])) # 關(guān)閉當(dāng)前連接 r.close()

代碼下載：python_connect_database

歡迎交換溝通~

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)
程序員人生
------分隔線----------------------------
分享到:
------分隔線----------------------------
為碼而活
積分：4237
15粉絲
7關(guān)注
欄目熱點(diǎn)
關(guān)閉
程序員人生
主站蜘蛛池模板： 国产精品网站 夜色 | 免费观看wwwwwww | 夜夜未满十八勿进的爽爽影院 | 欧美成成人免费 | 亚洲成a人片在线v观看 | 亚洲欧美日韩精品中文乱码 | 午夜毛片在线观看 | 亚洲理论a中文字幕在线 | 福利国产片 | 国产欧美日韩免费一区二区 | 激情文学激情图片 | 午夜精品久久久 | 中文字幕一区二区三区四区 | 国产成人一区二区三区免费观看 | 日本在线资源 | 色老头福影院韩国激情影院 | 图片专区亚洲 欧美 另类 | 伊人影视 | 中日韩一区二区三区 | 精品免费久久久久久久 | 亚洲精品一区二区三区 | 台湾成人性视频免费播放 | www.日本精品 | 免费在线观看成年人视频 | 欧美18-19sex性 | 欧美18一19sex性瑜伽hd | 成人毛片免费视频播放 | 宇都宫紫苑乳在线观看 | 伊人久久大香线蕉综合网站 | 黄色一级今 | 亚洲 校园 春色 另类 激情 | 欧美一级欧美一级高清 | www.黄色免费网站 | 欧美一级欧美一级毛片 | 欧美18在线| 亚洲成在人线久久综合 | 91手机看片国产福利精品 | 日本a∨网站 | 国产精久久一区二区三区 | 欧美高清在线不卡免费观看 | 中文字幕 国产 |