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(), }) #1箇所を選択して更新 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()