python调用ADO处理2进制字段例子

最新推荐文章于 2021-03-05 18:15:52 发布

o_yale_o

最新推荐文章于 2021-03-05 18:15:52 发布

阅读量810

点赞数

分类专栏：脚本 VBA wxPython Python 文章标签： python ado AppendChunk VARIANT 二进制字段

本文链接：https://blog.youkuaiyun.com/o_yale_o/article/details/43803139

版权

脚本 VBA wxPython Python 专栏收录该内容

2 篇文章

订阅专栏

<span style="font-size:18px;"># coding:utf-8 py文件有汉字要这一行!
# 测试不同数据类型的字段: 读取、赋值

"""
-- 建表
CREATE TABLE [dbo].[tableDataType](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [colInt] [int] NULL,
    [colVarchar] [varchar](50) NULL,
    [colText] [text] NULL,
    [colNumeric] [numeric](18, 0) NULL,
    [colDecimal] [decimal](18, 0) NULL,
    [colReal] [real] NULL,
    [colBit] [bit] NULL,
    [colDate] [date] NULL,
    [colDateTime] [datetime] NULL,
    [colTimeStamp] [timestamp] NULL,
    [colImage] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
"""

from win32com.client import Dispatch, VARIANT, constants
from ADOConstants import *

oConn = Dispatch('ADODB.Connection')
oConn.ConnectionString = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=bs_tjxt20;Data Source=."
oConn.Open()
oConn.CursorLocation = adUseClient # ds.RecordCount 才有效. 否则返回-1!

oCmd = Dispatch('ADODB.Command')
oCmd.ActiveConnection = oConn

ods = Dispatch( 'ADODB.Recordset' )
ods.ActiveConnection = oConn
ods.CursorType = adOpenStatic
ods.LockType = adLockBatchOptimistic

file1 = open( 'd:/pyCpy.rar', 'rb' )
file2 = open( 'd:/Json2Bmp.bmp', 'rb' )

fOut1 = open( 'd:/pyCpy_GetChunk.rar', 'wb' )
fOut2 = open( 'd:/Json2Bmp_GetChunk.bmp', 'wb' )

# 新增
ods.Open( 'select * from tableDataType where 1=0' )
ods.AddNew()
ods.Fields[ 'colInt' ].value     = 1
ods.Fields[ 'colVarchar' ].value = 'Varchar'
ods.Fields[ 'colText' ].value    = 'Test'
ods.Fields[ 'colNumeric' ].value = 1.123
ods.Fields[ 'colDecimal' ].value = 2.234
ods.Fields[ 'colReal' ].value    = 3.345
ods.Fields[ 'colBit' ].value     = 1
ods.Fields[ 'colDate' ].value    = '2015-02-11'
ods.Fields[ 'colDateTime' ].value= '2015-02-11 01:02:03.123'
s = file1.read(); print  type( s ),'s.len=', len( s )
byArr = buffer( s, 0, len( s ) );
v = VARIANT( 0x2000|17, byArr ) # VT_ARRAY    = 0x2000  VT_UI1    = 17 
ret = ods.Fields[ 'colImage' ].AppendChunk( v  )
print 1, ret

ods.AddNew()
ods.Fields[ 'colInt' ].value     = 2
ods.Fields[ 'colVarchar' ].value = 'Varchar'
ods.Fields[ 'colText' ].value    = 'Test'
ods.Fields[ 'colNumeric' ].value = 21.123
ods.Fields[ 'colDecimal' ].value = 22.234
ods.Fields[ 'colReal' ].value    = 23.345
ods.Fields[ 'colBit' ].value     = 0
ods.Fields[ 'colDate' ].value    = '2015-02-11'
ods.Fields[ 'colDateTime' ].value= '2015-02-11 01:02:03.123'
s = file2.read(); print type( s ), 's.len=', len( s )
byArr = buffer( s, 0, len( s ) );
v = VARIANT( 0x2000|17, byArr ) # VT_ARRAY    = 0x2000  VT_UI1    = 17 
ret = ods.Fields[ 'colImage' ].AppendChunk( v )
print 2, ret

ret = ods.UpdateBatch()
print 3, ret

# 读取2进制字段
ods.Close()
ods.Open( 'select * from tableDataType order by id' )
iSize = ods.Fields[ 'colImage' ].ActualSize; print 'size:', iSize
fOut1.write( ods.Fields[ 'colImage' ].GetChunk( iSize ) )
fOut1.close()

ods.MoveNext()
iSize = ods.Fields[ 'colImage' ].ActualSize ; print 'size:', iSize
fOut2.write( ods.Fields[ 'colImage' ].GetChunk( iSize ) )
fOut2.close()</span>