蟒蛇插入和检索二进制数据到mysql [英] python inserting and retrieving binary data into mysql

问题描述

我使用的是MySQLdb的包与MySQL进行交互。我无法得到适当的类型转换。

I'm using the MySQLdb package for interacting with MySQL. I'm having trouble getting the proper type conversions.

我使用的是16字节的二进制UUID作为表的主键，并有MEDIUMBLOB控股zlib的COM pressed json的信息。

I am using a 16-byte binary uuid as a primary key for the table and have a mediumblob holding zlib compressed json information.

我用下面的模式：

CREATE TABLE repositories (
    added_id int auto_increment not null,
    id binary(16) not null,
    data mediumblob not null,
    create_date int not null,
    update_date int not null,
    PRIMARY KEY (added_id),
    UNIQUE(id)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ENGINE=InnoDB;

然后我用下面的code在表中创建一个新行：

Then I create a new row in the table using the following code:

data = zlib.compress(json.dumps({'hello':'how are you :D'})
row_id = uuid.uuid(4).hex
added_id = cursor.execute('
    INSERT INTO repositories (id, data, create_date, update_date) 
    VALUES (%s, %s, %s, %s)',
    binascii.a2b_hex(row_id), 
    data, 
    time.time(), 
    time.time()
)

然后检索数据我用类似的查询：

Then to retrieve data I use a similar query:

query = cursor.execute('SELECT added_id, id, data, create_date, update_date ' \
    'FROM repositories WHERE id = %s',
    binascii.a2b_hex(row_id)
)

然后查询返回一个空的结果。

Then the query returns an empty result.

任何帮助将是AP preciated。另外，顺便说一句，是它更好地保存Unix纪元日期为整数或TIMESTAMP？

Any help would be appreciated. Also, as an aside, is it better to store unix epoch dates as integers or TIMESTAMP?

注意：我没有插入数据的问题，只是想从数据库中检索。当我通过的mysql检查行存在。

NOTE: I am not having problems inserting the data, just trying to retrieve it from the database. The row exists when I check via mysqlclient.

非常感谢！@

推荐答案

一个建议：你应该能够调用 uuid.uuid4（）个字节来获取原始。
字节。至于时间戳，如果要执行时间/日期操作
在SQL它往往容易对付真正的TIMESTAMP类型。

One tip: you should be able to call uuid.uuid4().bytes to get the raw bytes. As for timestamps, if you want to perform time/date manipulation in SQL it's often easier to deal with real TIMESTAMP types.

我创建了一个测试表，试图重现您所看到的：

I created a test table to try to reproduce what you're seeing:

CREATE TABLE xyz (
    added_id INT AUTO_INCREMENT NOT NULL,
    id BINARY(16) NOT NULL,
    PRIMARY KEY (added_id),
    UNIQUE (id)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ENGINE=InnoDB;

我的脚本是能够插入和使用二进制字段作为查询的行
键没有问题。也许你是不正确读取/遍历
结果光标回来了？

My script is able to insert and query for the rows using the binary field as a key without problem. Perhaps you are incorrectly fetching / iterating over the results returned by the cursor?

import binascii
import MySQLdb
import uuid

conn = MySQLdb.connect(host='localhost')

key = uuid.uuid4()
print 'inserting', repr(key.bytes)
r = conn.cursor()
r.execute('INSERT INTO xyz (id) VALUES (%s)', key.bytes)
conn.commit()

print 'selecting', repr(key.bytes)
r.execute('SELECT added_id, id FROM xyz WHERE id = %s', key.bytes)
for row in r.fetchall():
    print row[0], binascii.b2a_hex(row[1])

输出：

% python qu.py    
inserting '\x96\xc5\xa4\xc3Z+L\xf0\x86\x1e\x05\xebt\xf7\\\xd5'
selecting '\x96\xc5\xa4\xc3Z+L\xf0\x86\x1e\x05\xebt\xf7\\\xd5'
1 96c5a4c35a2b4cf0861e05eb74f75cd5
% python qu.py
inserting '\xac\xc9,jn\xb2O@\xbb\xa27h\xcd<B\xda'
selecting '\xac\xc9,jn\xb2O@\xbb\xa27h\xcd<B\xda'
2 acc92c6a6eb24f40bba23768cd3c42da

这篇关于蟒蛇插入和检索二进制数据到mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！