中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

python 連接各類主流數據庫的實例代碼

發布時間：2020-10-13 21:24:52 來源：腳本之家 閱讀：196 作者：sanxiaochengyu 欄目：開發技術

本篇博文主要介紹Python連接各種數據庫的方法及簡單使用

包括關系數據庫：sqlite,mysql,mssql

非關系數據庫：MongoDB,Redis

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

1.連接sqlite

# coding=utf-8
# http://www.runoob.com/sqlite/sqlite-python.html
import sqlite3
import traceback

try:
 # 如果表不存在,就創建
 with sqlite3.connect('test.db') as conn:

  print("Opened database successfully")

  # 刪除表
  conn.execute("DROP TABLE IF EXISTS COMPANY")

  # 創建表
  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")

  # 添加數據
  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 )")

  # 提交,否則重新運行程序時,表中無數據
  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個-,作為分界線
   print("%-10s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對齊
   print("%-10s %s" % ("name", row[1]))
   print("%-10s %s" % ("age", row[2]))
   print("%-10s %s" % ("address", row[3]))
   print("%-10s %.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-8
# Created by xiaosanyu at 16/5/30

# mysqldb 只支持python2.7
# http://mysql-python.sourceforge.net/

import MySQLdb
from contextlib import closing
import traceback

try:
 # 獲取一個數據庫連接
 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")
   # 創建表
   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")

   # 添加數據
   # 在一個conn.execute里面里面執行多個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)])

   # 提交,否則重新運行程序時,表中無數據
   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個-,作為分界線
    print("%-10s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對齊
    print("%-10s %s" % ("name", row[1]))
    print("%-10s %s" % ("age", row[2]))
    print("%-10s %s" % ("address", row[3]))
    print("%-10s %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-8
# Created by xiaosanyu at 16/5/30

# mysqldb 只支持python2.7
# http://mysql-python.sourceforge.net/

import MySQLdb
from contextlib import closing
import traceback

try:
 # 獲取一個數據庫連接
 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")
   # 創建表
   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")

   # 添加數據
   # 在一個conn.execute里面里面執行多個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)])

   # 提交,否則重新運行程序時,表中無數據
   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個-,作為分界線
    print("%-10s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對齊
    print("%-10s %s" % ("name", row[1]))
    print("%-10s %s" % ("age", row[2]))
    print("%-10s %s" % ("address", row[3]))
    print("%-10s %s" % ("salary", row[4]))

except MySQLdb.Error as e:
 print("Mysql Error:", e)
 traceback.print_exc() # 打印錯誤棧信息

2.3使用pymysql

2.1和2.2節使用MySQLdb，不支持Python3.x

pymysql對Python2.x和Python3.x的支持都比較好

# Created by xiaosanyu at 16/5/30
# coding=utf-8

# https://github.com/PyMySQL/PyMySQL/
import pymysql
from contextlib import closing
import traceback

try:
 # 獲取一個數據庫連接,with關鍵字 表示退出時,conn自動關閉
 # with 嵌套上一層的with 要使用closing()
 with closing(pymysql.connect(host='localhost', user='root', passwd='root', db='test', port=3306,
         charset='utf8')) as conn:

  print("connect database successfully")

  # 獲取游標,with關鍵字 表示退出時,cur自動關閉
  with conn.cursor() as cur:
   # 刪除表
   cur.execute("DROP TABLE IF EXISTS COMPANY")
   # 創建表
   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")

   # 添加數據
   # 在一個conn.execute里面里面執行多個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)])

   # 提交,否則重新運行程序時,表中無數據
   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個-,作為分界線
    print("%-10s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對齊
    print("%-10s %s" % ("name", row[1]))
    print("%-10s %s" % ("age", row[2]))
    print("%-10s %s" % ("address", row[3]))
    print("%-10s %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:
 # 先要保證數據庫中有test數據庫
 # 獲取一個數據庫連接,with關鍵字 表示退出時,conn自動關閉
 # with 嵌套上一層的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")

  # 獲取游標,with關鍵字 表示退出時,cur自動關閉
  with conn.cursor() as cur:
   # 刪除表
   cur.execute(
     '''if exists (select 1 from sys.objects where name='COMPANY' and type='U') drop table COMPANY''')
   # 創建表
   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")

   # 添加數據
   # 在一個conn.execute里面里面執行多個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)])

   # 提交,否則重新運行程序時,表中無數據
   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個-,作為分界線
    print("%-10s %s" % ("id", row[0])) # 字段名固定10位寬度,并且左對齊
    print("%-10s %s" % ("name", row[1]))
    print("%-10s %s" % ("age", row[2]))
    print("%-10s %s" % ("address", row[3]))
    print("%-10s %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 服務
 mongoClient = MongoClient('localhost', 27017)
 # 連接到數據庫
 mongoDatabase = mongoClient.test
 print("connect database successfully")

 # 獲取集合
 mongoCollection = mongoDatabase.COMPANY

 # 移除所有數據
 mongoCollection.remove()

 # 添加數據
 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個-,作為分界線
  print("%-10s %s" % ("_id", row['_id'])) # 字段名固定10位寬度,并且左對齊
  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()
 # 創建計數表
 mongoDatabase.counters.save({"_id": "people_id", "sequence_value": 0})
 # 創建存儲過程
 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個-,作為分界線
  print("%-10s %s" % ("_id", int(row['_id']))) # 字段名固定10位寬度,并且左對齊
  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-8
# 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 enumerate(items):
 print("item %s----%s:%s" % (i, key, value))

# 刪除鍵和對應的值
r.delete("company")

# 可以一次性push一條或多條數據
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格式的字符串轉換成dict
for row in map(lambda x: eval(x), r.lrange("company", 0, r.llen("company"))):
 print("-" * 50) # 輸出50個-,作為分界線
 print("%-10s %s" % ("_id", row['id'])) # 字段名固定10位寬度,并且左對齊
 print("%-10s %s" % ("name", row['Name']))
 print("%-10s %s" % ("age", row['Age']))
 print("%-10s %s" % ("address", row['Address']))
 print("%-10s %s" % ("salary", row['Salary']))

# 關閉當前連接
# r.shutdown() #這個是關閉redis服務端

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-8"))

# 看信息

# info = r.execute("info").decode()
# or 查看部分信息
info = r.execute("info", "Server").decode()

# 輸出信息
print(info)

# 刪除鍵和對應的值
r.delete("company")

# 可以一次性push一條或多條數據
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格式的字符串轉換成dict
for row in map(lambda x: eval(x), r.lrange("company", 0, r.llen("company"))):
 print("-" * 50) # 輸出50個-,作為分界線
 print("%-10s %s" % ("_id", row['id'])) # 字段名固定10位寬度,并且左對齊
 print("%-10s %s" % ("name", row['Name']))
 print("%-10s %s" % ("age", row['Age']))
 print("%-10s %s" % ("address", row['Address']))
 print("%-10s %s" % ("salary", row['Salary']))

# 關閉當前連接
r.close()

代碼下載：python_connect_database

以上這篇python 連接各類主流數據庫的實例代碼就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持億速云。

向AI問一下細節

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

AI
助
手
滦平县| 宁武县| 临邑县| 富平县| 白水县| 上思县| 呼玛县| 湘西| 舞钢市| 介休市| 双柏县| 休宁县| 高平市| 宜黄县| 措美县| 徐汇区| 平原县| 澄城县| 岚皋县| 方正县| 峡江县| 鱼台县| 林周县| 香格里拉县| 莱西市| 金湖县| 财经| 宽城| 康定县| 荣成市| 房山区| 镇坪县| 新野县| 天津市| 永川市| 平潭县| 麟游县| 舒城县| 务川| 嵊泗县| 基隆市|