Python 数据库访问

 

import pyodbc

 

# pyodbc connect to mssql server database

def GetSQLServerODBCConnection():

    #return pyodbc.connect('DRIVER={SQL Server};SERVER=%ServerName/IPAddress%;DATABASE=%DBName%;UID=%UserName%;PWD=%Password%')

    return pyodbc.connect('DRIVER={SQL Server};SERVER=Mostlee-PC;DATABASE=TestDB;UID=sa;PWD=Password123')

 

cnxn = Common.GetSQLServerODBCConnection()

cursor = cnxn.cursor()

 

#select statement

accounts = cursor.execute("select distinct AccountID from DeviceCertificates where AccountID IS NOT null")

accountIds = []

for aid in accounts:

    accountIds.append(aid.AccountID)

 

#update statement

cursor.execute("update DeviceCertificates set AccountName = ? where AccountID = ? " % ("NewName",10001))

 

# update multiple groups parameters

updatestmt = "update DeviceCertificates set AccountName = ? where AccountID = ?"

accountInfos = [['Test1','10001'],['Test2','10002'],['Test3','10003'],['Test4','10004']]

#update with all parameters in the list accountInfos

cursor.executemany(updatestmt, accountInfos)

# update with range parameters in the list accountInfos

cursor.executemany(updatestmt, accountInfos[StartIndex:EndIndex])

 

#need to close the connection once no need to use it.

cnxn.close()

 

 

 

#-----------------------------------------------------------------

#Mongo DB access

import pymongo

 

UserName = "TestUser"

Password = "TestPassword"

dbIPaddresses = "172.168.1.100:29017,172.168.1.101:29017,172.168.1.102:29017";

dbName = "MongodbTest"

 

# pymongo connect to mongo database

def GetMongoDBConnection():

   

    dbconnectionstring = 'mongodb://%s:%s@%s/%s%s' % (UserName,Password,IPAddresses,dbName,'?readPreference=secondaryPreferred')

    return pymongo.MongoClient(dbconnectionstring)

 

c = Common.GetMongoDBConnection()

print("Connected to mongodb successfully.")

db = c[dbName]

titles = db["titles"]

#The Number of records will be skipped

skipNum = 100

 

#The target records will return(if there are enough records)

limitNum = 100

 

utcEndtime = datetime.datetime.utcnow() + datetime.timedelta(days=1)  #Current utc datetime

results = titles.find({"_id":{"$regex":titleIdPrefix},"Offers._id":{"$exists":"True"},"Offers.ManuallyExpired":False,"Offers.EndUtc":{"$gt":utcEndTime}},{"_id":'1',"UniversalProgramId":'1',"Offers._id":'1',"Offers.OfferType":'1',"Offers.EndUtc":'1',"ShowType":'1',"Offers.ManuallyExpired":'1'}).skip(skipNum).limit(limitNum)

#or use db.titles.find({query condition},{filter return field, 1 means need to return, 0 means no need to return})

 

for title in results:

    print(title)

 

if c is not None:

    c.close()

 

 

#--------------------------------------------------------------------------------------

#Cassandra DB access

from cassandra.cluster import Cluster

from cassandra.auth import PlainTextAuthProvider

 

# Cassandra db connection sample

def GetCassandraDBCluster():

    return Cluster(['172.168.2.101','172.168.2.102','172.168.2.103','172.168.2.104','172.168.2.105'],port=9042,auth_provider=PlainTextAuthProvider(username='mrsubscriberfunkread',password='38562f273ca141408400f660364efe4c'),compression='snappy',control_connection_timeout=60)

 

cluster = GetCassandraDBCluster()

subscriberdbName = "subscriber"

session = cluster.connect(subscriberdbName)

print("Connect to cassandradb(subscriber) Successfully.")

rows = session.execute('select grouptype,name,id,data from groups')

#Get all offer groups.

for row in rows:

    print(row)

       

#async query

groupData = session.execute_async('select accountid,groups from accounts')

#Get all account id and its groups

for f in groupData.result():

    print(f)

       

if cluster is not None and not cluster.is_shutdown:

    cluster.shutdown()