def make_cache():
# Check to see if we have a connection pool open already
meta_key = url + table_name
def make_meta():
if sa_version == '0.3':
if url.startswith('mysql') and not sa_opts:
sa_opts['poolclass'] = pool.QueuePool
engine = sa.create_engine(url, **sa_opts)
meta = sa.BoundMetaData(engine)
else:
# SQLAlchemy pops the url, this ensures it sticks around
# later
sa_opts['sa.url'] = url
engine = sa.engine_from_config(sa_opts, 'sa.')
meta = sa.MetaData()
meta.bind = engine
return meta
meta = DatabaseNamespaceManager.metadatas.get(meta_key, make_meta)
# Create the table object and cache it now
cache = sa.Table(
table_name, meta,
sa.Column('id', types.Integer, primary_key=True),
sa.Column('namespace', types.String(255), nullable=False),
sa.Column('accessed', types.DateTime, nullable=False),
sa.Column('created', types.DateTime, nullable=False),
sa.Column('data', types.BLOB(), nullable=False),
sa.UniqueConstraint('namespace'))
cache.create(checkfirst=True)
return cache
def _type_affinity_fixture(self):
return [
("LONGTEXT", sqltypes.TEXT()),
("TINYINT", sqltypes.INTEGER()),
("MEDIUMINT", sqltypes.INTEGER()),
("INT2", sqltypes.INTEGER()),
("UNSIGNED BIG INT", sqltypes.INTEGER()),
("INT8", sqltypes.INTEGER()),
("CHARACTER(20)", sqltypes.TEXT()),
("CLOB", sqltypes.TEXT()),
("CLOBBER", sqltypes.TEXT()),
("VARYING CHARACTER(70)", sqltypes.TEXT()),
("NATIVE CHARACTER(70)", sqltypes.TEXT()),
("BLOB", sqltypes.BLOB()),
("BLOBBER", sqltypes.NullType()),
("DOUBLE PRECISION", sqltypes.REAL()),
("FLOATY", sqltypes.REAL()),
("NOTHING WE KNOW", sqltypes.NUMERIC()),
]
def binary(length, **kwargs):
return _vary(types.BLOB(length), binary_map.copy(), kwargs, length)
#差分を書き込みの例
pd.concat([df,
basedf]).drop_duplicates(keep=False).to_sql('raw-data',
con,
index=False,
if_exists='append')
#空のデータベース書き込み例
df2 = pd.DataFrame(index=range(5), columns=['Picture'])
df2.index.name = 'id'
df2.reset_index().to_sql('pic',
con,
index=False,
if_exists='replace',
dtype={
'id': types.BIGINT(),
'Picture': types.BLOB(),
})
#１箇所を選択して更新
connection = engine.raw_connection()
cursor = connection.cursor()
df.plot().get_figure().savefig('sample.png')
with open('sample.png', 'rb') as image:
imager = image.read()
query = "UPDATE pic SET Picture = %s WHERE id = %s"
args = (imager, 2)
cursor.execute(query, args)
connection.commit()