python与各种数据库建立连接的实例代码

安装数据库需要的包

1. pip install redis
2. pip install psycopg2
3. sqlite3内置
4. pip install pymongo
5. pip install pymssql
6. pip install PyMySQL
7. pip install cx_Oracle

1.连接sqlite数据库

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Create table
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
conn.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close() 

 

2、连接mysql数据库

import pymysql 
conn=pymysql.connect(host="127.0.0.1",user="xxx",passwd="xxx",db="world")
db=conn.cursor()
db.execute("select count(*) from country")
#res=db.fetchone()
r=db.fetchall()
print(r)
#print(res)
input()

3、连接mssql数据库

import pymssql
conn = pymssql.connect(host='127.0.0.1', user='sa', password='xxx', database='xxx') #建立数据库连接
cur = conn.cursor() #建立句柄
sqltabnum=""" 
SELECT  [TABLE_NAME]  FROM test t
 t.TABLE_TYPE='BASE TABLE' 
order by [TABLE_NAME]
""" #查询数据库中的所有表除去系统表和类型为基础表的。
cur.execute(sqltabnum)
a=cur.fetchall() #

4、连接oracle数据库

import  cx_Oracle
conn = cx_Oracle.connect('xxx','xxx','127.0.0.1/xxx') #连接数据库
cur = conn.cursor()
sql = "select 'test' from dual"
cur.execute(sql)
data = cur.fetchall()

5、连接postgresql数据库

import psycopg2
conn=psycopg2.connect(database='xx',user='sa',password='xxx',host='127.0.0.1')
db=conn.cursor()
db.execute("select * from test)
#res=db.fetchone()
#for i in db.description:
#   print(i.name,sep=" ",end=" ")
r=db.fetchmany(2)
for i in r:
    print(i)

6、连接redis数据库

import redis   # 导入redis 模块

r = redis.Redis(host='localhost', port=6379, decode_responses=True)  
r.set('name', 'runoob')  # 设置 name 对应的值
print(r['name'])
print(r.get('name'))  # 取出键 name 对应的值
print(type(r.get('name')))  # 查看类型

7、连接mongdb数据库

import pymongo
 
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
 
mylist = [
  { "name": "Taobao", "alexa": "100", "url": "https://www.taobao.com" },
  { "name": "QQ", "alexa": "101", "url": "https://www.qq.com" },
  { "name": "Facebook", "alexa": "10", "url": "https://www.facebook.com" },
  { "name": "知乎", "alexa": "103", "url": "https://www.zhihu.com" },
  { "name": "Github", "alexa": "109", "url": "https://www.github.com" }
]
 
x = mycol.insert_many(mylist)
 
# 输出插入的所有文档对应的 _id 值
#print(x.inserted_ids)

 
mylist = [
  { "_id": 1, "name": "RUNOOB", "cn_name": "菜鸟教程"},
  { "_id": 2, "name": "Google", "address": "Google 搜索"},
  { "_id": 3, "name": "Facebook", "address": "脸书"},
  { "_id": 4, "name": "Taobao", "address": "淘宝"},
  { "_id": 5, "name": "Zhihu", "address": "知乎"}
]
 
#x = mycol.insert_many(mylist)
 
# 输出插入的所有文档对应的 _id 值
#print(x.inserted_ids)
y = mycol.find()
for i in y:
    print(i['name'])