python数据库

[ttfy1234]

我想把文件存入数据库
打开文件->取出数据到obj->insert into t(file) values(obj)
这样存文本的没问题,存其他的文件类型,比如PDF文件,立马就报错了,
我一直以为是字符编码问题,可怎么encode,decode都还是不行

请问存文件需要怎么操作的,读文件到变量->SQL语句到数据库,这个流程没错吧!
请问这里面有什么需要注意的??

[iambic]

报的是什么错？

[ttfy1234]

IDE: pythonWin

>>> Unhandled exception while debugging...
Traceback (most recent call last):
File "E:\UE\mis.py", line 230, in cmdInput
self.conn.Execute(sqlcmd)
File "<COMObject ADODB.Connection>", line 3, in Execute
File "D:\program\ActivePython\Lib\site-packages\win32com\client\dynamic.py", line 258, in _ApplyTypes_
result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
com_error: (-2147352567, '\xb7\xa2\xc9\xfa\xd2\xe2\xcd\xe2\xa1\xa3', (0, 'Microsoft JET Database Engine', "\xd7\xd6\xb7\xfb\xb4\xae\xb5\xc4\xd3\xef\xb7\xa8\xb4\xed\xce\xf3 \xd4\xda\xb2\xe9\xd1\xaf\xb1\xed\xb4\xef\xca\xbd ''%PDF-1.2\n%\xc7\xec\x8f\xa2\n8 0 obj\n<</Length 9 0 R/Filter /FlateDecode>>\nstream\nx\x9c\xd5ZK\x8f\xe36\x12\x06r\xec_\xe1\xe3\x04Xk$?r?\x92\xde?H0;\x13o.;\xc1B#\xb3\xbb?K\x1eIv\xb7s\xd8\xdf\xbeE\xb1\xf8p[m??' \xd6\xd0\xa1\xa3", None, 5003000, -2147217900), None)
[Dbg]>>>

[ttfy1234]

fin = open(filename, "rb")
strobj = fin.read()
fin.flush()
fin.close()


conn = win32com.client.Dispatch(r'ADODB.Connection')
DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=E:/UE/mis.mdb;'
conn.Open(DSN)
sqlcmd = "insert into t1(data) values('%s')"%strobj
conn.Execute(sqlcmd)
conn.Close()

这个是用的 MS Access数据库,另外用MySql也会报错误...

[iambic]

Python Cookbook

Recipe 7.10. Storing a BLOB in a MySQL Database
Credit: Luther Blissett

Problem
You need to store a binary large object (BLOB) in a MySQL database.

Solution
The MySQLdb module does not support full-fledged placeholders, but you can make do with the module's escape_string function:

import MySQLdb, cPickle
# Connect to a DB, e.g., the test DB on your localhost, and get a cursor
connection = MySQLdb.connect(db="test")
cursor = connection.cursor( )
# Make a new table for experimentation
cursor.execute("CREATE TABLE justatest (name TEXT, ablob BLOB)")
try:
    # Prepare some BLOBs to insert in the table
    names = 'aramis', 'athos', 'porthos'
    data = {  }
    for name in names:
        datum = list(name)
        datum.sort( )
        data[name] = cPickle.dumps(datum, 2)
    # Perform the insertions
    sql = "INSERT INTO justatest VALUES(%s, %s)"
    for name in names:
        cursor.execute(sql, (name, MySQLdb.escape_string(data[name])) )
    # Recover the data so you can check back
    sql = "SELECT name, ablob FROM justatest ORDER BY name"
    cursor.execute(sql)
    for name, blob in cursor.fetchall( ):
        print name, cPickle.loads(blob), cPickle.loads(data[name])
finally:
    # Done. Remove the table and close the connection.
    cursor.execute("DROP TABLE justatest")
    connection.close( )

Discussion
MySQL supports binary data (BLOBs and variations thereof), but you should be careful when communicating such data via SQL. Specifically, when you use a normal INSERT SQL statement and need to have binary strings among the VALUES you're inserting, you have to escape some characters in the binary string according to MySQL's own rules. Fortunately, you don't have to figure out those rules for yourself: MySQL supplies a function that does the needed escaping, and MySQLdb exposes it to your Python programs as the escape_string function.

This recipe shows a typical case: the BLOBs you're inserting come from cPickle.dumps, so they may represent almost arbitrary Python objects (although, in this case, we're just using them for a few lists of characters). The recipe is purely demonstrative and works by creating a table and dropping it at the end (using a try/finally statement to ensure that finalization is performed even if the program should terminate because of an uncaught exception). With recent versions of MySQL and MySQLdb, you don't even need to call the escape_string function anymore, so you can change the relevant statement to the simpler:

cursor.execute(sql, (name, data[name]))


See Also
Recipe 7.11 and Recipe 7.12 for PostgreSQL-oriented and SQLite-oriented solutions to the same problem; the MySQL home page (http://www.mysql.org); the Python/MySQL interface module (http://sourceforge.net/projects/mysql-python).

[iambic]

在线版本：
Recipe 7.10. Storing a BLOB in a MySQL Database

[ttfy1234]

Thanks very much, It is exactly what i want.

I'll test it later.

How about MS Access, is there similar solutions ?

[ChumpKlutz]

对应的数据库字段类型要为大二进制

把文件读到变量里再insert

楼上的代码可以参考

[ttfy1234]

有人能帮忙解决这问题吗?

在MYSQL里我设置为BLOB类型也不行的, 在MSACCESS里我设置为备注类型,
用recod.item[].value = strobj这种形式可以存到ACCESS数据库里,

我现在要用SQL语句解决...