date:20161223

[sqlalchemy] Re: Possible to bulk update with different values?

2016-12-23 Thread Brian Clark

Got it, thanks!

On Friday, December 23, 2016 at 12:11:12 PM UTC-8, Brian Clark wrote:
>
> Is there an update equivalent of this insert statement?
>
> inserts = [{"name": "jon", "age": 20"}, {"name": "ashley", "age": 22"}]
> session.execute(
> People.__table__.insert().values(
> inserts
> )
> )
>
> I have this right now but it's still slower than I'd like because it's 
> using executemany, hoping to have it be one big query
>
> updated_people = [{"b_id": 1, "b_name": "jon", "b_age": 21"}, {"b_id": 2, 
> "b_name": "ashley", "b_age": 25"}]
> stmt = People.__table__.update().\
>   where(People.id == bindparam('b_id')).\
>   values(name=bindparam('b_name'), age=bindparam('b_age'))
> session.execute(stmt, updated_people)
>
> Thanks!
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Speed issue with bulk inserts

2016-12-23 Thread Jonathan Vanasco

Does this issue consistently repeat within a transaction block?  Does it 
still happen if you reverse the tests?  

I've run into similar issues in the past, and the problem was often from 
postgresql checking indexes -- the first test would stall because indexes 
needed to be read into memory, then the second test would fly.  If I waited 
more than 5 seconds (or outside of a transaction), postgres would lose the 
indexes from memory.  Triggers are likely too, but index issues are often 
more common.

If that's the case, what worked for me:
* increasing the cache allocation for postgres
* using partial/function indexes on some columns, and adjusted the query to 
hint against them (otherwise the planner won't use it)
* upgrading to postgres 9.6 (searching is a lot better thanks to a few 
improvements and a new parallel search feature)

Something that also worked for me in debugging this sort of stuff was using 
query-logging in postgres to grab the actual processed query, then running 
"explain analyze" on it and seeing where the slowdown happened.  the only 
way to clear out the postgres index cache is to stop/start the server , so 
it can be a pain to get every condition timed right.  the results from 
"explain analyze" often vary wildly from running the query normally with 
timing on.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Possible to bulk update with different values?

2016-12-23 Thread Jonathan Vanasco


If you really need to do something like that though, you can always string 
together raw sql + bindparams, and pipe it all through an `execute` into 
the dbcursor.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Speed issue with bulk inserts

2016-12-23 Thread Mike Bayer

Does this table have triggers of some kind on it ?  I've been asking on the
psycopg2 list about this as this is not the first time this has come up.

On Dec 23, 2016 8:46 AM, "mike bayer"  wrote:

> those are two different kinds of INSERT statements.   To compare to Core
> you need to run like this:
>
>
>  engine.execute(
>TrialLocations.__table__.insert(),
>trial_location_core_inserts
>  )
>
> that will run executemany() on the psycopg2 side, which internally will
> run 223 INSERT statements.   On a normal setup this should run like
> lightning, 223 is extremely low.   You may have some unusual network
> overhead.   In the old days we'd say the table needs to be vacuumed but I
> had the impression that by the 9.x series this is mostly automatic, though
> maybe try running vacuuming on that table (https://www.postgresql.org/do
> cs/9.1/static/sql-vacuum.html)
>
>
>
> On 12/23/2016 03:25 AM, Brian Clark wrote:
>
>> So I'm having an issue with a very slow insert, I'm inserting 223 items
>> and it takes 20+ seconds to execute. Any advice on what I'm doing wrong
>> and why it would be so slow? Using Postgresql 9.4.8
>>
>> The line of code
>>
>> LOG_OUTPUT('==PRE BULK==', True)
>> db_session.bulk_save_objects(trial_location_inserts)
>> LOG_OUTPUT('==POST BULK==', True)
>>
>> And here's the log from sqlalchemy echo
>>
>> 2016-12-23 07:37:52.570: ==PRE BULK==
>> 2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine INSERT
>> INTO trial_locations (status, trial_id, location_id, active) VALUES
>> (%(status)s, %(trial_id)s, %(location_id)s, %(active)s)
>> 2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine
>> ({'status': u'Completed', 'active': True, 'location_id': 733,
>> 'trial_id': 126625}, {'status': u'Completed', 'active': True,
>> 'location_id': 716, 'trial_id': 126625}, {'status': u'Completed',
>> 'active': True, 'location_id': 1033, 'trial_id': 126625}, {'status':
>> u'Completed', 'active': True, 'location_id': 1548, 'trial_id': 126625},
>> {'status': u'Completed', 'active': True, 'location_id': 1283,
>> 'trial_id': 126625}, {'status': u'Completed', 'active': True,
>> 'location_id': 1556, 'trial_id': 126625}, {'status': u'Completed',
>> 'active': True, 'location_id': 4271, 'trial_id': 126625}, {'status':
>> u'Completed', 'active': True, 'location_id': 1567, 'trial_id': 126625}
>>  ... displaying 10 of 223 total bound parameter sets ...  {'status':
>> u'Completed', 'active': True, 'location_id': 1528, 'trial_id': 126625},
>> {'status': u'Completed', 'active': True, 'location_id': 1529,
>> 'trial_id': 126625})
>> 2016-12-23 07:38:14.270: ==POST BULK==
>>
>>
>> Also for comparison I rewrote it in Sqlalchemy core
>>
>>
>> LOG_OUTPUT('==PRE BULK==', True)
>> engine.execute(
>>   TrialLocations.__table__.insert().values(
>> trial_location_core_inserts
>>   )
>> )
>> # db_session.bulk_save_objects(trial_location_inserts)
>> LOG_OUTPUT('==POST BULK==', True)
>>
>> and it ran in 0.028 seconds
>>
>> 2016-12-23 08:11:26.097: ==PRE BULK==
>> ...
>> 2016-12-23 08:11:27.025: ==POST BULK==
>>
>> I'd like to keep it in session for the sake of this being the end part
>> of a transaction and if the whole thing fails I want it all to rollback.
>> Am I doing something wrong? I don't need the data afterwards it just
>> needs to be saved
>>
>> Help appreciated!
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google
>> Groups "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send
>> an email to sqlalchemy+unsubscr...@googlegroups.com
>> .
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> .
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more

Re: [sqlalchemy] Re: Possible to bulk update with different values?

2016-12-23 Thread mike bayer


that pattern is not supported in SQLAlchemy Core.




On 12/23/2016 03:21 PM, Brian Clark wrote:

Seems doable in raw SQL (using postgresql btw)

http://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql

On Friday, December 23, 2016 at 12:11:12 PM UTC-8, Brian Clark wrote:

Is there an update equivalent of this insert statement?

inserts = [{"name": "jon", "age": 20"}, {"name": "ashley", "age": 22"}]
session.execute(
People.__table__.insert().values(
inserts
)
)

I have this right now but it's still slower than I'd like because
it's using executemany, hoping to have it be one big query

updated_people = [{"b_id": 1, "b_name": "jon", "b_age": 21"},
{"b_id": 2, "b_name": "ashley", "b_age": 25"}]
stmt = People.__table__.update().\
  where(People.id == bindparam('b_id')).\
  values(name=bindparam('b_name'),
age=bindparam('b_age'))
session.execute(stmt, updated_people)

Thanks!

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: MySQL ForeignKey indexes being removed

2016-12-23 Thread mike bayer

MyISAM does not actually record foreign key definitions.   Autogenerate 
is not compatible with foreign keys that are declared in the model yet 
are not actually present in the database due to MyISAM.   To keep these 
FK definitions in Python but work around the autogenerate issue, create 
an include_object rule that skips foreign_key_constraint detection:


http://alembic.zzzcomputing.com/en/latest/api/runtime.html?highlight=include_object#alembic.runtime.environment.EnvironmentContext.configure.params.include_object

On 12/22/2016 06:08 PM, Adam Patt wrote:

I'm using mysql 5.1.73 and alembic 0.8.6 and even when the model has not
changed.  Below I have the schema migration which is getting auto
generated as well as the models defs. This is using MyISAM and when I
look at the Foreign Keys tab in MySQL Workbench, it says foreign keys
can only be used in certain engines. I only recently added the Foreign
Key and migrations worked exactly as expected before this change.

What am I doing wrong?

### generated schema migration

def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_index('fk_command_task_id_task', table_name='command')
op.create_foreign_key(op.f('fk_command_task_id_task'), 'command',
'task', ['task_id'], ['id'])
op.drop_index('fk_task_job_id_job', table_name='task')
op.create_foreign_key(op.f('fk_task_job_id_job'), 'task', 'job',
['job_id'], ['id'])
### end Alembic commands ###


def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(op.f('fk_task_job_id_job'), 'task',
type_='foreignkey')
op.create_index('fk_task_job_id_job', 'task', ['job_id'], unique=False)
op.drop_constraint(op.f('fk_command_task_id_task'), 'command',
type_='foreignkey')
op.create_index('fk_command_task_id_task', 'command', ['task_id'],
unique=False)
### end Alembic commands ###

### object definitions

Base = declarative_base()

# make it so constraints have a name so that backward migrations work
correctly
Base.metadata = MetaData(naming_convention={
"ix": 'ix_%(column_0_label)s',
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(constraint_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s"
  })


class SnapBase(object):
"""
base for all SNAP sqlalchemy objects
does the following things:
- adds a default id field to all objects
- automatically generate a table name based on the class name.
 Turns ClassName to class_name
- creates a nice human readable version of any object
- utility method to get the list of fields available
"""
@declared_attr
def __tablename__(cls):
"""
turn things like MyTable into my_table in the database
"""
tablename = ''
for c in cls.__name__:
if c.isupper() and tablename:
tablename += '_'
tablename += c.lower()
return tablename

def _field_names(self):
return [str(c).split('.')[1] for c in self.__table__.columns]

def __repr__(self):
display = ""
for column in self.__table__.columns:
name = str(column).split('.')[1]
if display:
display += ', '
value = getattr(self, name)
column_type = str(column.type)
if (column_type.startswith('VARCHAR') or
column_type.startswith('CHAR') or column_type in ['DATETIME', 'DATE'])
and value is not None:
val = "%s='%s'"
else:
val = "%s=%s"
display += val % (name, value)
return '%s(%s)' % (self.__class__.__name__, display)

id = Column(Integer, primary_key=True)


class Job(SnapBase, Base):
user = Column(String(50), nullable=False)
status = Column(String(10), default='running', nullable=False)  #
running/complete
created = Column(DateTime, default=datetime.datetime.utcnow,
nullable=False)
updated = Column(DateTime, default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow, nullable=False)

tasks = relationship('Task', backref='job')


class Task(SnapBase, Base):
serial = Column(String(50), nullable=False)
name = Column(String(50), nullable=False)
user = Column(String(50), nullable=False)
status = Column(String(10), default='running', nullable=False)  #
running/complete
result = Column(String(50))
message = Column(Text)
created = Column(DateTime, default=datetime.datetime.utcnow,
nullable=False)
updated = Column(DateTime, default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow, nullable=False)

job_id = Column(Integer, ForeignKey('job.id'))
commands = relationship('Command', backref='task')

class Command(SnapBase, Base):
serial = Column(String(50), nullable=False)
agent = Column(String(20), nullable=False)  # snap/oob/cobbler/chef

Re: [sqlalchemy] Possible to bulk update with different values?

2016-12-23 Thread mike bayer



Here's Postgresql's UPDATE syntax:

https://www.postgresql.org/docs/9.5/static/sql-update.html

as you can see the WHERE clause has only one option, so it's one row at 
a time.


On 12/23/2016 03:11 PM, Brian Clark wrote:

Is there an update equivalent of this insert statement?

inserts = [{"name": "jon", "age": 20"}, {"name": "ashley", "age": 22"}]
session.execute(
People.__table__.insert().values(
inserts
)
)

I have this right now but it's still slower than I'd like because it's
using executemany, hoping to have it be one big query

updated_people = [{"b_id": 1, "b_name": "jon", "b_age": 21"}, {"b_id":
2, "b_name": "ashley", "b_age": 25"}]
stmt = People.__table__.update().\
  where(People.id == bindparam('b_id')).\
  values(name=bindparam('b_name'), age=bindparam('b_age'))
session.execute(stmt, updated_people)

Thanks!

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

MySQL ForeignKey indexes being removed

2016-12-23 Thread Adam Patt

I'm using mysql 5.1.73 and alembic 0.8.6 and even when the model has not 
changed.  Below I have the schema migration which is getting auto generated 
as well as the models defs. This is using MyISAM and when I look at the 
Foreign Keys tab in MySQL Workbench, it says foreign keys can only be used 
in certain engines. I only recently added the Foreign Key and migrations 
worked exactly as expected before this change.

What am I doing wrong?

### generated schema migration 

def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_index('fk_command_task_id_task', table_name='command')
op.create_foreign_key(op.f('fk_command_task_id_task'), 'command', 
'task', ['task_id'], ['id'])
op.drop_index('fk_task_job_id_job', table_name='task')
op.create_foreign_key(op.f('fk_task_job_id_job'), 'task', 'job', 
['job_id'], ['id'])
### end Alembic commands ###


def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(op.f('fk_task_job_id_job'), 'task', 
type_='foreignkey')
op.create_index('fk_task_job_id_job', 'task', ['job_id'], unique=False)
op.drop_constraint(op.f('fk_command_task_id_task'), 'command', 
type_='foreignkey')
op.create_index('fk_command_task_id_task', 'command', ['task_id'], 
unique=False)
### end Alembic commands ###

### object definitions

Base = declarative_base()

# make it so constraints have a name so that backward migrations work 
correctly
Base.metadata = MetaData(naming_convention={
"ix": 'ix_%(column_0_label)s',
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(constraint_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s"
  })


class SnapBase(object):
"""
base for all SNAP sqlalchemy objects
does the following things:
- adds a default id field to all objects
- automatically generate a table name based on the class name. 
 Turns ClassName to class_name
- creates a nice human readable version of any object
- utility method to get the list of fields available
"""
@declared_attr
def __tablename__(cls):
"""
turn things like MyTable into my_table in the database
"""
tablename = ''
for c in cls.__name__:
if c.isupper() and tablename:
tablename += '_'
tablename += c.lower()
return tablename

def _field_names(self):
return [str(c).split('.')[1] for c in self.__table__.columns]

def __repr__(self):
display = ""
for column in self.__table__.columns:
name = str(column).split('.')[1]
if display:
display += ', '
value = getattr(self, name)
column_type = str(column.type)
if (column_type.startswith('VARCHAR') or 
column_type.startswith('CHAR') or column_type in ['DATETIME', 'DATE']) and 
value is not None:
val = "%s='%s'"
else:
val = "%s=%s"
display += val % (name, value)
return '%s(%s)' % (self.__class__.__name__, display)

id = Column(Integer, primary_key=True)


class Job(SnapBase, Base):
user = Column(String(50), nullable=False)
status = Column(String(10), default='running', nullable=False)  # 
running/complete
created = Column(DateTime, default=datetime.datetime.utcnow, 
nullable=False)
updated = Column(DateTime, default=datetime.datetime.utcnow, 
onupdate=datetime.datetime.utcnow, nullable=False)

tasks = relationship('Task', backref='job')


class Task(SnapBase, Base):
serial = Column(String(50), nullable=False)
name = Column(String(50), nullable=False)
user = Column(String(50), nullable=False)
status = Column(String(10), default='running', nullable=False)  # 
running/complete
result = Column(String(50))
message = Column(Text)
created = Column(DateTime, default=datetime.datetime.utcnow, 
nullable=False)
updated = Column(DateTime, default=datetime.datetime.utcnow, 
onupdate=datetime.datetime.utcnow, nullable=False)

job_id = Column(Integer, ForeignKey('job.id'))
commands = relationship('Command', backref='task')

class Command(SnapBase, Base):
serial = Column(String(50), nullable=False)
agent = Column(String(20), nullable=False)  # snap/oob/cobbler/chef
silo = Column(String(20), nullable=False)
command = Column(String(30), nullable=False)
delay = Column(Integer, default=0, nullable=False)
retries = Column(Integer, default=0, nullable=False)
timeout = Column(Integer, nullable=False)
expire = Column(DateTime, nullable=False)
parameters = Column(JSONEncodedDict(2000), default={}, nullable=False) 
 # json
status = Column(String(10), default='pending', nullable=False)  # 
pending, sent, success/error
result = Column(String(50))
message = Column(Text)
user

[sqlalchemy] Re: Possible to bulk update with different values?

2016-12-23 Thread Brian Clark

Seems doable in raw SQL (using postgresql btw)

http://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql

On Friday, December 23, 2016 at 12:11:12 PM UTC-8, Brian Clark wrote:
>
> Is there an update equivalent of this insert statement?
>
> inserts = [{"name": "jon", "age": 20"}, {"name": "ashley", "age": 22"}]
> session.execute(
> People.__table__.insert().values(
> inserts
> )
> )
>
> I have this right now but it's still slower than I'd like because it's 
> using executemany, hoping to have it be one big query
>
> updated_people = [{"b_id": 1, "b_name": "jon", "b_age": 21"}, {"b_id": 2, 
> "b_name": "ashley", "b_age": 25"}]
> stmt = People.__table__.update().\
>   where(People.id == bindparam('b_id')).\
>   values(name=bindparam('b_name'), age=bindparam('b_age'))
> session.execute(stmt, updated_people)
>
> Thanks!
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Possible to bulk update with different values?

2016-12-23 Thread Brian Clark

Is there an update equivalent of this insert statement?

inserts = [{"name": "jon", "age": 20"}, {"name": "ashley", "age": 22"}]
session.execute(
People.__table__.insert().values(
inserts
)
)

I have this right now but it's still slower than I'd like because it's 
using executemany, hoping to have it be one big query

updated_people = [{"b_id": 1, "b_name": "jon", "b_age": 21"}, {"b_id": 2, 
"b_name": "ashley", "b_age": 25"}]
stmt = People.__table__.update().\
  where(People.id == bindparam('b_id')).\
  values(name=bindparam('b_name'), age=bindparam('b_age'))
session.execute(stmt, updated_people)

Thanks!

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Speed issue with bulk inserts

2016-12-23 Thread mike bayer

those are two different kinds of INSERT statements.   To compare to Core 
you need to run like this:



 engine.execute(
   TrialLocations.__table__.insert(),
   trial_location_core_inserts
 )

that will run executemany() on the psycopg2 side, which internally will 
run 223 INSERT statements.   On a normal setup this should run like 
lightning, 223 is extremely low.   You may have some unusual network 
overhead.   In the old days we'd say the table needs to be vacuumed but 
I had the impression that by the 9.x series this is mostly automatic, 
though maybe try running vacuuming on that table 
(https://www.postgresql.org/docs/9.1/static/sql-vacuum.html)




On 12/23/2016 03:25 AM, Brian Clark wrote:

So I'm having an issue with a very slow insert, I'm inserting 223 items
and it takes 20+ seconds to execute. Any advice on what I'm doing wrong
and why it would be so slow? Using Postgresql 9.4.8

The line of code

LOG_OUTPUT('==PRE BULK==', True)
db_session.bulk_save_objects(trial_location_inserts)
LOG_OUTPUT('==POST BULK==', True)

And here's the log from sqlalchemy echo

2016-12-23 07:37:52.570: ==PRE BULK==
2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine INSERT
INTO trial_locations (status, trial_id, location_id, active) VALUES
(%(status)s, %(trial_id)s, %(location_id)s, %(active)s)
2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine
({'status': u'Completed', 'active': True, 'location_id': 733,
'trial_id': 126625}, {'status': u'Completed', 'active': True,
'location_id': 716, 'trial_id': 126625}, {'status': u'Completed',
'active': True, 'location_id': 1033, 'trial_id': 126625}, {'status':
u'Completed', 'active': True, 'location_id': 1548, 'trial_id': 126625},
{'status': u'Completed', 'active': True, 'location_id': 1283,
'trial_id': 126625}, {'status': u'Completed', 'active': True,
'location_id': 1556, 'trial_id': 126625}, {'status': u'Completed',
'active': True, 'location_id': 4271, 'trial_id': 126625}, {'status':
u'Completed', 'active': True, 'location_id': 1567, 'trial_id': 126625}
 ... displaying 10 of 223 total bound parameter sets ...  {'status':
u'Completed', 'active': True, 'location_id': 1528, 'trial_id': 126625},
{'status': u'Completed', 'active': True, 'location_id': 1529,
'trial_id': 126625})
2016-12-23 07:38:14.270: ==POST BULK==


Also for comparison I rewrote it in Sqlalchemy core


LOG_OUTPUT('==PRE BULK==', True)
engine.execute(
  TrialLocations.__table__.insert().values(
trial_location_core_inserts
  )
)
# db_session.bulk_save_objects(trial_location_inserts)
LOG_OUTPUT('==POST BULK==', True)

and it ran in 0.028 seconds

2016-12-23 08:11:26.097: ==PRE BULK==
...
2016-12-23 08:11:27.025: ==POST BULK==

I'd like to keep it in session for the sake of this being the end part
of a transaction and if the whole thing fails I want it all to rollback.
Am I doing something wrong? I don't need the data afterwards it just
needs to be saved

Help appreciated!

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Speed issue with bulk inserts

2016-12-23 Thread Brian Clark

And here's the objects

for key, unique_new_location in unique_locations_hash.iteritems():
trial_location_inserts.append(TrialLocations(trial_id = 
current_trial.id, location_id = unique_new_location['location_id'], active 
= True, status = unique_new_location['status']))


On Friday, December 23, 2016 at 12:25:40 AM UTC-8, Brian Clark wrote:
>
> So I'm having an issue with a very slow insert, I'm inserting 223 items 
> and it takes 20+ seconds to execute. Any advice on what I'm doing wrong and 
> why it would be so slow? Using Postgresql 9.4.8
>
> The line of code
>
> LOG_OUTPUT('==PRE BULK==', True)
> db_session.bulk_save_objects(trial_location_inserts)
> LOG_OUTPUT('==POST BULK==', True)
>
> And here's the log from sqlalchemy echo
>
> 2016-12-23 07:37:52.570: ==PRE BULK==
> 2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine INSERT INTO 
> trial_locations (status, trial_id, location_id, active) VALUES (%(status)s, 
> %(trial_id)s, %(location_id)s, %(active)s)
> 2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine ({'status': 
> u'Completed', 'active': True, 'location_id': 733, 'trial_id': 126625}, 
> {'status': u'Completed', 'active': True, 'location_id': 716, 'trial_id': 
> 126625}, {'status': u'Completed', 'active': True, 'location_id': 1033, 
> 'trial_id': 126625}, {'status': u'Completed', 'active': True, 
> 'location_id': 1548, 'trial_id': 126625}, {'status': u'Completed', 
> 'active': True, 'location_id': 1283, 'trial_id': 126625}, {'status': 
> u'Completed', 'active': True, 'location_id': 1556, 'trial_id': 126625}, 
> {'status': u'Completed', 'active': True, 'location_id': 4271, 'trial_id': 
> 126625}, {'status': u'Completed', 'active': True, 'location_id': 1567, 
> 'trial_id': 126625}  ... displaying 10 of 223 total bound parameter sets 
> ...  {'status': u'Completed', 'active': True, 'location_id': 1528, 
> 'trial_id': 126625}, {'status': u'Completed', 'active': True, 
> 'location_id': 1529, 'trial_id': 126625})
> 2016-12-23 07:38:14.270: ==POST BULK==
>
>
> Also for comparison I rewrote it in Sqlalchemy core 
>   
>
> LOG_OUTPUT('==PRE BULK==', True)
> engine.execute(
>   TrialLocations.__table__.insert().values(
> trial_location_core_inserts
>   )
> )
> # db_session.bulk_save_objects(trial_location_inserts)
> LOG_OUTPUT('==POST BULK==', True)
>
> and it ran in 0.028 seconds
>
> 2016-12-23 08:11:26.097: ==PRE BULK==
> ...
> 2016-12-23 08:11:27.025: ==POST BULK==
>
> I'd like to keep it in session for the sake of this being the end part of 
> a transaction and if the whole thing fails I want it all to rollback. Am I 
> doing something wrong? I don't need the data afterwards it just needs to be 
> saved
>
> Help appreciated!
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Speed issue with bulk inserts

2016-12-23 Thread Brian Clark

So I'm having an issue with a very slow insert, I'm inserting 223 items and 
it takes 20+ seconds to execute. Any advice on what I'm doing wrong and why 
it would be so slow? Using Postgresql 9.4.8

The line of code

LOG_OUTPUT('==PRE BULK==', True)
db_session.bulk_save_objects(trial_location_inserts)
LOG_OUTPUT('==POST BULK==', True)

And here's the log from sqlalchemy echo

2016-12-23 07:37:52.570: ==PRE BULK==
2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine INSERT INTO 
trial_locations (status, trial_id, location_id, active) VALUES (%(status)s, 
%(trial_id)s, %(location_id)s, %(active)s)
2016-12-22 23:37:52,572 INFO sqlalchemy.engine.base.Engine ({'status': 
u'Completed', 'active': True, 'location_id': 733, 'trial_id': 126625}, 
{'status': u'Completed', 'active': True, 'location_id': 716, 'trial_id': 
126625}, {'status': u'Completed', 'active': True, 'location_id': 1033, 
'trial_id': 126625}, {'status': u'Completed', 'active': True, 
'location_id': 1548, 'trial_id': 126625}, {'status': u'Completed', 
'active': True, 'location_id': 1283, 'trial_id': 126625}, {'status': 
u'Completed', 'active': True, 'location_id': 1556, 'trial_id': 126625}, 
{'status': u'Completed', 'active': True, 'location_id': 4271, 'trial_id': 
126625}, {'status': u'Completed', 'active': True, 'location_id': 1567, 
'trial_id': 126625}  ... displaying 10 of 223 total bound parameter sets 
...  {'status': u'Completed', 'active': True, 'location_id': 1528, 
'trial_id': 126625}, {'status': u'Completed', 'active': True, 
'location_id': 1529, 'trial_id': 126625})
2016-12-23 07:38:14.270: ==POST BULK==


Also for comparison I rewrote it in Sqlalchemy core 
  

LOG_OUTPUT('==PRE BULK==', True)
engine.execute(
  TrialLocations.__table__.insert().values(
trial_location_core_inserts
  )
)
# db_session.bulk_save_objects(trial_location_inserts)
LOG_OUTPUT('==POST BULK==', True)

and it ran in 0.028 seconds

2016-12-23 08:11:26.097: ==PRE BULK==
...
2016-12-23 08:11:27.025: ==POST BULK==

I'd like to keep it in session for the sake of this being the end part of a 
transaction and if the whole thing fails I want it all to rollback. Am I 
doing something wrong? I don't need the data afterwards it just needs to be 
saved

Help appreciated!

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Possible to bulk update with different values?

Re: [sqlalchemy] Speed issue with bulk inserts

Re: [sqlalchemy] Re: Possible to bulk update with different values?

Re: [sqlalchemy] Speed issue with bulk inserts

Re: [sqlalchemy] Re: Possible to bulk update with different values?

Re: MySQL ForeignKey indexes being removed

Re: [sqlalchemy] Possible to bulk update with different values?

MySQL ForeignKey indexes being removed

[sqlalchemy] Re: Possible to bulk update with different values?

[sqlalchemy] Possible to bulk update with different values?

Re: [sqlalchemy] Speed issue with bulk inserts

[sqlalchemy] Re: Speed issue with bulk inserts

[sqlalchemy] Speed issue with bulk inserts

13 matches

Site Navigation

Mail list logo

Footer information