Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?
Im working on getting off bitbucket but maybe it's an ad blocker? try a
private window or something
On Mon, Oct 8, 2018 at 6:36 PM Lukasz Szybalski wrote:
>
>
> On Mon, Oct 8, 2018 at 9:56 AM Mike Bayer
> wrote:
>
>>
>>
>> On Mon, Oct 8, 2018 at 10:28 AM Lukasz Szybalski
>> wrote:
>>
>>>
>>> I have a query in sqlalchemy like below where I lookup contract# in
>>> mssql. How do I enforce the varchar instead of nvarchar? I tried
>>> converting
>>> my field to "str(mycurrent)" but that didn't do anything. Is there some
>>> other spot to force VARCHAR to be sent?
>>>
>>
>>
>> this just came up in
>> https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case,
>> in that case they are using typed parameters:
>>
>> String(50, convert_unicode='force')
>>
>> if you're dealing w/ the raw string like that try this:
>>
>> execute(text("select ... where foo =
>> :mycurrent").bindparams(bindparam("mycurrent",
>> type_=String(convert_unicode='force')))
>>
>> let me know if that works b.c. this has to be in the docs
>>
>
>>
>> you need to use the text() construct as I illustrated earlier:
>>
>> session.execute(
>> text("select ... where foo =
>> :mycurrent").bindparams(bindparam("mycurrent",
>> type_=String(convert_unicode='force')),
>> params={"mycurrent": "value"}
>> )
>>
>>
>>
>>
>>
>>
>>>
>>>
You can find that the query that uses NVARCHAR does an index scan has
30,909 logical reads on the dbo.P table. It also uses 890 ms of CPU and
has a total elapsed time of 938 ms.
The query that uses VARCHAR does an index seek and has 7 logical reads
on the dbo.P table. It uses 0 ms of CPU and has a total elapsed time of 11
ms.
p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P
with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
where p.current=:mycurrent and D.srtype_id
=5",params={'mycurrent':str(mycurrent)}).fetchall()
[image: Inline image 1]
> SUPER FAST..now.!!!
> (I wonder what would be a side effect if this was done by default? aka
> user varchar)
>
> For Reference: I'll I split into two lines because for some reason I was
> making some mistake and had to google my way out of it.
>
> Version1 (easier to understand and test)
>
> *from sqlalchemy import text, bindparam,String*
> # ":mycontract" and "mycontract" parts bound by the bindparams to create
> a statemetn query.
> *# item in bold is what is required to get it to convert from nvarchar to
> varchar*
> stmt= text("select P.contract_id from dbo.P P with(NOLOCK) where
> currentstatus_id in (1,2) and current_cuntract=:mycontract").
> *bindparams(bindparam("mycontract",type_=String(50,convert_unicode='force')))*
> #We now execute the statement from above and bind it to a variable I'm
> passing row.mycontract from our forloop.
> p=session.execute(stmt,params={'mycontract':row.mycontract}).fetchall()
>
> This causes the value to be varchar instead of nvarchar which fixes mssql :
> "NVARCHAR cause performance downgrade because NVARCHAR literals do not
> use VARCHAR indexes. When we add convert_unicode='force' all our
> N'something' turns into 'something' and gains performance from indexes."
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case
>
> **Can you post above as comment to bitbucket ticket. I can't register it
> says "are you sure you are not a robot" with no other option ;) lol . I
> guess they don't get many new accounts lol
>
> Thank you sqlalchemy!
> Lucas
> --
> http://lucasmanual.com/
Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?
On Mon, Oct 8, 2018 at 9:56 AM Mike Bayer wrote:
>
>
> On Mon, Oct 8, 2018 at 10:28 AM Lukasz Szybalski
> wrote:
>
>>
>> I have a query in sqlalchemy like below where I lookup contract# in
>> mssql. How do I enforce the varchar instead of nvarchar? I tried
>> converting
>> my field to "str(mycurrent)" but that didn't do anything. Is there some
>> other spot to force VARCHAR to be sent?
>>
>
>
> this just came up in
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case,
> in that case they are using typed parameters:
>
> String(50, convert_unicode='force')
>
> if you're dealing w/ the raw string like that try this:
>
> execute(text("select ... where foo =
> :mycurrent").bindparams(bindparam("mycurrent",
> type_=String(convert_unicode='force')))
>
> let me know if that works b.c. this has to be in the docs
>
>
> you need to use the text() construct as I illustrated earlier:
>
> session.execute(
> text("select ... where foo =
> :mycurrent").bindparams(bindparam("mycurrent",
> type_=String(convert_unicode='force')),
> params={"mycurrent": "value"}
> )
>
>
>
>
>
>
>>
>>
>>> You can find that the query that uses NVARCHAR does an index scan has
>>> 30,909 logical reads on the dbo.P table. It also uses 890 ms of CPU and
>>> has a total elapsed time of 938 ms.
>>>
>>> The query that uses VARCHAR does an index seek and has 7 logical reads
>>> on the dbo.P table. It uses 0 ms of CPU and has a total elapsed time of 11
>>> ms.
>>>
>>>
>>> p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P
>>> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
>>> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
>>> where p.current=:mycurrent and D.srtype_id
>>> =5",params={'mycurrent':str(mycurrent)}).fetchall()
>>>
>>>
>>>
>>>
>>>
>>> [image: Inline image 1]
>>>
>>>
>>>
>>>
SUPER FAST..now.!!!
(I wonder what would be a side effect if this was done by default? aka user
varchar)
For Reference: I'll I split into two lines because for some reason I was
making some mistake and had to google my way out of it.
Version1 (easier to understand and test)
*from sqlalchemy import text, bindparam,String*
# ":mycontract" and "mycontract" parts bound by the bindparams to create a
statemetn query.
*# item in bold is what is required to get it to convert from nvarchar to
varchar*
stmt= text("select P.contract_id from dbo.P P with(NOLOCK) where
currentstatus_id in (1,2) and current_cuntract=:mycontract").
*bindparams(bindparam("mycontract",type_=String(50,convert_unicode='force')))*
#We now execute the statement from above and bind it to a variable I'm
passing row.mycontract from our forloop.
p=session.execute(stmt,params={'mycontract':row.mycontract}).fetchall()
This causes the value to be varchar instead of nvarchar which fixes mssql :
"NVARCHAR cause performance downgrade because NVARCHAR literals do not use
VARCHAR indexes. When we add convert_unicode='force' all our N'something'
turns into 'something' and gains performance from indexes."
https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case
**Can you post above as comment to bitbucket ticket. I can't register it
says "are you sure you are not a robot" with no other option ;) lol . I
guess they don't get many new accounts lol
Thank you sqlalchemy!
Lucas
--
http://lucasmanual.com/
Re: Alembic op.alter_column deletes all rows in the database table in error
INFO [alembic.runtime.migration] Running upgrade 22923a2e396c ->
d4aceba22da2, daily_smart_meter_readings rename customer fk.
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE
daily_smart_meter_readings RENAME customer_id TO customer_pk
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE INDEX
ix_daily_smart_meter_readings_customer_pk ON daily_smart_meter_readings (
customer_pk)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
DROP INDEX one_customer_reading_at_meter_type_meter_point_id_register_uc
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE UNIQUE INDEX
one_customer_reading_at_meter_type_meter_point_id_register_uc ON
daily_smart_meter_readings (customer_pk, reading_at, meter_type,
meter_point_id, register_tier) WHERE register_tier IS NOT NULL
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
DROP INDEX one_customer_reading_at_meter_type_meter_point_id_tier_uc
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE UNIQUE INDEX
one_customer_reading_at_meter_type_meter_point_id_tier_uc ON
daily_smart_meter_readings (customer_pk, reading_at, meter_type,
meter_point_id) WHERE register_tier IS NULL
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
DROP INDEX ix_daily_smart_meter_readings_customer_id
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE
daily_smart_meter_readings DROP CONSTRAINT
daily_smart_meter_readings_customer_id_fkey
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE
daily_smart_meter_readings ADD FOREIGN KEY(customer_pk) REFERENCES
customers (id)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num
='d4aceba22da2' WHERE alembic_version.version_num = '22923a2e396c'
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] COMMIT
INFO [sqlalchemy.engine.base.Engine] BEGIN (implicit)
On Monday, October 8, 2018 at 3:59:07 PM UTC+1, Mike Bayer wrote:
>
> can you provide SQLAlchemy statement output please, set "level = INFO"
> under [logger_sqlalchemy] in alembic.ini
>
> op.alter_column() does not emit DELETE and nothing in Alembic emits
> the DELETE statement anywhere except upon the alembic_version table
> itself.
>
>
> On Mon, Oct 8, 2018 at 10:31 AM Richard wrote:
> >
> > I'm using postgres version 10, alembic 1.0.0, sqlalchemy 1.2.12. I'm
> aware ALTER COLUMN in SQL doesn't delete rows, but op.alter_column is doing
> that.
> >
> > My customer & daily reading models look like:
> >
> >
> > class Customer(DeclarativeBase):
> > __tablename__ = 'customers'
> > id = Column(Integer, primary_key=True)
> > electricity_readings = relationship(
> > 'ElectricityMeterReading', cascade='all,delete-orphan',
> > backref=backref('customer', cascade='all')
> > )
> > gas_readings = relationship(
> > 'GasMeterReading', cascade='all,delete-orphan',
> > backref=backref('customer', cascade='all')
> > )
> > daily_smart_meter_readings = relationship(
> > 'DailyMeterReading',
> > cascade='all,delete-orphan',
> > backref=backref('customer', cascade='all')
> > )
> >
> > class DailyMeterReading(DeclarativeBase):
> > __tablename__ = 'daily_smart_meter_readings'
> > id = Column(Integer, primary_key=True)
> > customer_pk = Column(
> > Integer, ForeignKey('customers.id'), nullable=False, index=True
> > )
> > reading = Column(Float, nullable=False)
> > reading_at = Column(UtcDateTime, nullable=False, index=True)
> >
> >
> >
> >
> >
> >
> >
> > On Monday, October 8, 2018 at 3:26:11 PM UTC+1, Mike Bayer wrote:
> >>
> >> Hi there -
> >>
> >> I have no idea what you are seeing.an actual ALTER COLUMN
> >> operation does not delete rows. Of course, if you are using SQLite
> >> and batch mode, that might affect things, but you have not specified
> >> this. Please specify complete information including log output,
> >> stack traces, database in use, sample schema, etc.
> >>
> >> On Mon, Oct 8, 2018 at 9:36 AM Richard wrote:
> >> >
> >> >
> >> > Note that if I do the same op.alter_column on another table which has
> a customer FK, it works fine and does not delete all the rows.
> >> >
> >> > On Monday, October 8, 2018 at 1:50:38 PM UTC+1, Richard wrote:
> >> >>
> >> >> I have an alembic migration which renames a FK column on a table
> from 'customer_id' to 'customer_pk'.
> >> >>
> >> >> I used to have more in the migration file but narrowed it down to
> this code causing all the rows to be deleted.
> >> >>
> >> >> def upgrade():
> >> >> op.alter_column(
> >> >> 'daily_smart_meter_readings', column_name='customer_id',
> >> >> new_column_name='customer_pk',
> >> >> )
> >> >>
> >> >>
> >> >> I'm using
Re: Alembic op.alter_column deletes all rows in the database table in error
can you provide SQLAlchemy statement output please, set "level = INFO"
under [logger_sqlalchemy] in alembic.ini
op.alter_column() does not emit DELETE and nothing in Alembic emits
the DELETE statement anywhere except upon the alembic_version table
itself.
On Mon, Oct 8, 2018 at 10:31 AM Richard wrote:
>
> I'm using postgres version 10, alembic 1.0.0, sqlalchemy 1.2.12. I'm aware
> ALTER COLUMN in SQL doesn't delete rows, but op.alter_column is doing that.
>
> My customer & daily reading models look like:
>
>
> class Customer(DeclarativeBase):
> __tablename__ = 'customers'
> id = Column(Integer, primary_key=True)
> electricity_readings = relationship(
> 'ElectricityMeterReading', cascade='all,delete-orphan',
> backref=backref('customer', cascade='all')
> )
> gas_readings = relationship(
> 'GasMeterReading', cascade='all,delete-orphan',
> backref=backref('customer', cascade='all')
> )
> daily_smart_meter_readings = relationship(
> 'DailyMeterReading',
> cascade='all,delete-orphan',
> backref=backref('customer', cascade='all')
> )
>
> class DailyMeterReading(DeclarativeBase):
> __tablename__ = 'daily_smart_meter_readings'
> id = Column(Integer, primary_key=True)
> customer_pk = Column(
> Integer, ForeignKey('customers.id'), nullable=False, index=True
> )
> reading = Column(Float, nullable=False)
> reading_at = Column(UtcDateTime, nullable=False, index=True)
>
>
>
>
>
>
>
> On Monday, October 8, 2018 at 3:26:11 PM UTC+1, Mike Bayer wrote:
>>
>> Hi there -
>>
>> I have no idea what you are seeing.an actual ALTER COLUMN
>> operation does not delete rows. Of course, if you are using SQLite
>> and batch mode, that might affect things, but you have not specified
>> this. Please specify complete information including log output,
>> stack traces, database in use, sample schema, etc.
>>
>> On Mon, Oct 8, 2018 at 9:36 AM Richard wrote:
>> >
>> >
>> > Note that if I do the same op.alter_column on another table which has a
>> > customer FK, it works fine and does not delete all the rows.
>> >
>> > On Monday, October 8, 2018 at 1:50:38 PM UTC+1, Richard wrote:
>> >>
>> >> I have an alembic migration which renames a FK column on a table from
>> >> 'customer_id' to 'customer_pk'.
>> >>
>> >> I used to have more in the migration file but narrowed it down to this
>> >> code causing all the rows to be deleted.
>> >>
>> >> def upgrade():
>> >> op.alter_column(
>> >> 'daily_smart_meter_readings', column_name='customer_id',
>> >> new_column_name='customer_pk',
>> >> )
>> >>
>> >>
>> >> I'm using alembic==1.0.0 and Python 3.6.4.
>> >>
>> >> Is there something wrong with the above code or is this a bug in the
>> >> library?
>> >>
>> >> Thanks
>> >
>> > --
>> > You received this message because you are subscribed to the Google Groups
>> > "sqlalchemy-alembic" group.
>> > To unsubscribe from this group and stop receiving emails from it, send an
>> > email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>> > For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?
On Mon, Oct 8, 2018 at 10:28 AM Lukasz Szybalski
wrote:
>
> I have a query in sqlalchemy like below where I lookup contract# in mssql.
> How do I enforce the varchar instead of nvarchar? I tried converting my
> field to "str(mycurrent)" but that didn't do anything. Is there some other
> spot to force VARCHAR to be sent?
>
this just came up in
https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case,
in that case they are using typed parameters:
String(50, convert_unicode='force')
if you're dealing w/ the raw string like that try this:
execute(text("select ... where foo =
:mycurrent").bindparams(bindparam("mycurrent",
type_=String(convert_unicode='force')))
let me know if that works b.c. this has to be in the docs
>>>
>>>
>>>
>>>
> Hello Mike.
>
> Do you have a minute to guide me on the syntax. I can't figure this out,
> and I need to fix this performance problem. If I can't figure it out I'll
> just create stored procedures to replace the query.
>
>
> Here is the code again:
>
>
>
> # 'm struggling with the syntax here for the bind param. How do I convert
> from session.execut(stmt,params) to your bindparams? I can't just cast the
> variable I'm passing to correct format?
>
you need to use the text() construct as I illustrated earlier:
session.execute(
text("select ... where foo =
:mycurrent").bindparams(bindparam("mycurrent",
type_=String(convert_unicode='force')),
params={"mycurrent": "value"}
)
# Keep in mind that the value is either some sqlalchemy object.columname or
> some 'other system' variable from a loop aka (for row in First_query:
> mycontract=row.contract_no
>
> #I need to go from to :
>
>>
>> From
>> p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P with(nolock)
>> inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner join dbo.D D
>> with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num where
>> p.contract=:mycontract and D.srtype_id
>> =5",params={'mycontract':str(mycontract)}).fetchall()
>>
>> To:
>> from sqlalchmy import bindparams
>> ##mycurrent=someobject.contract_no
>> mycurrent='ABC123'
>>
>> I tried these syntax but it doesn;t work:
>
> #Incorrect Syntax#1?
>
>>
>> (bad) p=session.execute(text("select PZ.p_id,PZ.pimage_num from dbo.P
>> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
>> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
>> where p.contract=:mycontract and D.srtype_id
>> =5").bindparam('mycontract'),params={'mycontract':str(mycontract)}).fetchall()
>> AttributeError: 'TextClause' object has no attribute 'bindparam'
>>
>> #Incorrect Syntax#2?
>
>> (bad) p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P
>> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
>> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
>> where p.contract=:mycontract and D.srtype_id
>> =5").bindparam('mycontract').fetchall()
>> A value is required for bind parameter 'mycontract'
>>
>>
>> #Incorrect Syntax #3?
> This in the link
>
> indicated.
> Page says that a "String(50).with_variant(String(50,
> convert_unicode='force'), 'mssql')" has worked for Jan K. but it doesn't
> really say how does somebody converts their variable into correct type
> string:
>
>
>> like:
>> mycontract=cast(mycontract, String(convert_unicode='force'))
>>
>>
>> #Incorrect Syntax #4?
>
>> Since I'm passing params right after my statement should the bindparam be
>> in params section?
>> something along:
>> params={'mycontract':sqlalchemy.string(mycontract).convert_unicode='force')})
>>
>> The easiest way would be to convert mycontract into proper
>> String(convert_unicode='force') and pass mycontract as in my original query?
>>
>>
>>
>
>
>> You can find that the query that uses NVARCHAR does an index scan has
>> 30,909 logical reads on the dbo.P table. It also uses 890 ms of CPU and
>> has a total elapsed time of 938 ms.
>>
>> The query that uses VARCHAR does an index seek and has 7 logical reads on
>> the dbo.P table. It uses 0 ms of CPU and has a total elapsed time of 11 ms.
>>
>>
>> p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P
>> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
>> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
>> where p.current=:mycurrent and D.srtype_id
>> =5",params={'mycurrent':str(mycurrent)}).fetchall()
>>
>>
>>
>>
>>
>> [image: Inline image 1]
>>
>>
>>
>>
>
>
> --
> 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
Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?
> I have a query in sqlalchemy like below where I lookup contract# in mssql.
How do I enforce the varchar instead of nvarchar? I tried converting my
field to "str(mycurrent)" but that didn't do anything. Is there some other
spot to force VARCHAR to be sent?
>>>
>>>
>>> this just came up in
>>> https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case,
>>>
>>> in that case they are using typed parameters:
>>>
>>> String(50, convert_unicode='force')
>>>
>>> if you're dealing w/ the raw string like that try this:
>>>
>>> execute(text("select ... where foo =
>>> :mycurrent").bindparams(bindparam("mycurrent",
>>> type_=String(convert_unicode='force')))
>>>
>>> let me know if that works b.c. this has to be in the docs
>>>
>>
>>
>>
>>
Hello Mike.
Do you have a minute to guide me on the syntax. I can't figure this out,
and I need to fix this performance problem. If I can't figure it out I'll
just create stored procedures to replace the query.
Here is the code again:
# 'm struggling with the syntax here for the bind param. How do I convert
from session.execut(stmt,params) to your bindparams? I can't just cast the
variable I'm passing to correct format?
# Keep in mind that the value is either some sqlalchemy object.columname or
some 'other system' variable from a loop aka (for row in First_query:
mycontract=row.contract_no
#I need to go from to :
>
> From
> p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P with(nolock)
> inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner join dbo.D D
> with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num where
> p.contract=:mycontract and D.srtype_id
> =5",params={'mycontract':str(mycontract)}).fetchall()
>
> To:
> from sqlalchmy import bindparams
> ##mycurrent=someobject.contract_no
> mycurrent='ABC123'
>
> I tried these syntax but it doesn;t work:
#Incorrect Syntax#1?
>
> (bad) p=session.execute(text("select PZ.p_id,PZ.pimage_num from dbo.P
> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
> where p.contract=:mycontract and D.srtype_id
> =5").bindparam('mycontract'),params={'mycontract':str(mycontract)}).fetchall()
> AttributeError: 'TextClause' object has no attribute 'bindparam'
>
> #Incorrect Syntax#2?
> (bad) p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P
> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
> where p.contract=:mycontract and D.srtype_id
> =5").bindparam('mycontract').fetchall()
> A value is required for bind parameter 'mycontract'
>
>
> #Incorrect Syntax #3?
This in the link
indicated.
Page says that a "String(50).with_variant(String(50,
convert_unicode='force'), 'mssql')" has worked for Jan K. but it doesn't
really say how does somebody converts their variable into correct type
string:
> like:
> mycontract=cast(mycontract, String(convert_unicode='force'))
>
>
> #Incorrect Syntax #4?
> Since I'm passing params right after my statement should the bindparam be
> in params section?
> something along:
> params={'mycontract':sqlalchemy.string(mycontract).convert_unicode='force')})
>
> The easiest way would be to convert mycontract into proper
> String(convert_unicode='force') and pass mycontract as in my original query?
>
>
>
> You can find that the query that uses NVARCHAR does an index scan has
> 30,909 logical reads on the dbo.P table. It also uses 890 ms of CPU and
> has a total elapsed time of 938 ms.
>
> The query that uses VARCHAR does an index seek and has 7 logical reads on
> the dbo.P table. It uses 0 ms of CPU and has a total elapsed time of 11 ms.
>
>
> p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P
> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
> where p.current=:mycurrent and D.srtype_id
> =5",params={'mycurrent':str(mycurrent)}).fetchall()
>
>
>
>
>
> [image: Inline image 1]
>
>
>
>
--
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: Alembic op.alter_column deletes all rows in the database table in error
Hi there - I have no idea what you are seeing.an actual ALTER COLUMN operation does not delete rows. Of course, if you are using SQLite and batch mode, that might affect things, but you have not specified this. Please specify complete information including log output, stack traces, database in use, sample schema, etc. On Mon, Oct 8, 2018 at 9:36 AM Richard wrote: > > > Note that if I do the same op.alter_column on another table which has a > customer FK, it works fine and does not delete all the rows. > > On Monday, October 8, 2018 at 1:50:38 PM UTC+1, Richard wrote: >> >> I have an alembic migration which renames a FK column on a table from >> 'customer_id' to 'customer_pk'. >> >> I used to have more in the migration file but narrowed it down to this code >> causing all the rows to be deleted. >> >> def upgrade(): >> op.alter_column( >> 'daily_smart_meter_readings', column_name='customer_id', >> new_column_name='customer_pk', >> ) >> >> >> I'm using alembic==1.0.0 and Python 3.6.4. >> >> Is there something wrong with the above code or is this a bug in the library? >> >> Thanks > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy-alembic" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy-alembic+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Controlling table dependency for flushing
On Sun, Oct 7, 2018 at 7:11 PM Alex Rothberg wrote:
>
> Okay so I investigated / thought about this further. The issue is that while
> I do have a relationship between the various models, some of the
> relationships are viewonly since I have overlapping fks.
>
> For example I have a model Employee, which has fks: department_id, title_id,
> and fund_id. The related models are Department (fk department_id), Title (fk
> department_id and title_id) , Fund (fk fund_id) and FundTitle (fk
> department_id, title_id and fund_id). I have set FundTitle to viewonly. When
> updating / creating an Employee, I do create and add a new FundTitle to the
> session, however I don't assign it to the employee as the relationship is
> viewonly. If I don't flush before making the assignment, the final flush /
> commit attempts to update / create the employee before creating the FundTitle.
let's work with source code that is runnable (e.g. MCVE). Below is
the model that it seems you are describing, and then there's a
demonstration of assembly of all those components using relationships,
a single flush and it all goes in in the correct order, all FKs are
nullable=False.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
title_id = Column(ForeignKey('title.id'), nullable=False)
department_id = Column(ForeignKey('department.id'), nullable=False)
fund_id = Column(ForeignKey('fund.id'), nullable=False)
department = relationship("Department")
title = relationship("Title")
fund = relationship("Fund")
class Title(Base):
__tablename__ = 'title'
id = Column(Integer, primary_key=True)
department_id = Column(ForeignKey('department.id'), nullable=False)
department = relationship("Department")
class Department(Base):
__tablename__ = 'department'
id = Column(Integer, primary_key=True)
class Fund(Base):
__tablename__ = 'fund'
id = Column(Integer, primary_key=True)
title_id = Column(ForeignKey('title.id'), nullable=False)
department_id = Column(ForeignKey('department.id'), nullable=False)
department = relationship("Department")
title = relationship("Title")
class FundTitle(Base):
__tablename__ = 'fund_title'
id = Column(Integer, primary_key=True)
title_id = Column(ForeignKey('title.id'), nullable=False)
department_id = Column(ForeignKey('department.id'), nullable=False)
fund_id = Column(ForeignKey('fund.id'), nullable=False)
department = relationship("Department")
title = relationship("Title")
fund = relationship("Fund")
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)
s = Session(e)
d1 = Department()
t1 = Title(department=d1)
f1 = Fund(department=d1, title=t1)
ft1 = FundTitle(title=t1, department=d1, fund=f1)
e1 = Employee(title=t1, department=d1, fund=f1)
s.add_all([d1, t1, f1, ft1, e1])
s.commit()
the INSERTs can be ordered naturally here and the unit of work will do
that for you if you use relationship:
BEGIN (implicit)
2018-10-08 10:18:38,750 INFO sqlalchemy.engine.base.Engine INSERT INTO
department DEFAULT VALUES RETURNING department.id
2018-10-08 10:18:38,750 INFO sqlalchemy.engine.base.Engine {}
2018-10-08 10:18:38,753 INFO sqlalchemy.engine.base.Engine INSERT INTO
title (department_id) VALUES (%(department_id)s) RETURNING title.id
2018-10-08 10:18:38,753 INFO sqlalchemy.engine.base.Engine {'department_id': 1}
2018-10-08 10:18:38,757 INFO sqlalchemy.engine.base.Engine INSERT INTO
fund (title_id, department_id) VALUES (%(title_id)s,
%(department_id)s) RETURNING fund.id
2018-10-08 10:18:38,757 INFO sqlalchemy.engine.base.Engine
{'title_id': 1, 'department_id': 1}
2018-10-08 10:18:38,760 INFO sqlalchemy.engine.base.Engine INSERT INTO
employee (title_id, department_id, fund_id) VALUES (%(title_id)s,
%(department_id)s, %(fund_id)s) RETURNING employee.id
2018-10-08 10:18:38,761 INFO sqlalchemy.engine.base.Engine
{'title_id': 1, 'department_id': 1, 'fund_id': 1}
2018-10-08 10:18:38,763 INFO sqlalchemy.engine.base.Engine INSERT INTO
fund_title (title_id, department_id, fund_id) VALUES (%(title_id)s,
%(department_id)s, %(fund_id)s) RETURNING fund_title.id
2018-10-08 10:18:38,764 INFO sqlalchemy.engine.base.Engine
{'title_id': 1, 'department_id': 1, 'fund_id': 1}
2018-10-08 10:18:38,766 INFO sqlalchemy.engine.base.Engine COMMIT
>
> On Tuesday, September 18, 2018 at 9:02:30 AM UTC-4, Mike Bayer wrote:
>>
>> if there are no dependencies between two particular objects of
>> different classes, say A and B, then there is no deterministic
>> ordering between them. For objects of the same class, they are
>> inserted in the order in which they were added to the Session.
>>
>> the correct way to solve this problem in SQLAlchemy is to use
>> relationship() fully. I know you've stated that
Re: Alembic op.alter_column deletes all rows in the database table in error
Note that if I do the same op.alter_column on another table which has a customer FK, it works fine and does not delete all the rows. On Monday, October 8, 2018 at 1:50:38 PM UTC+1, Richard wrote: > > I have an alembic migration which renames a FK column on a table from > 'customer_id' to 'customer_pk'. > > I used to have more in the migration file but narrowed it down to this > code causing all the rows to be deleted. > > def upgrade(): > op.alter_column( > 'daily_smart_meter_readings', column_name='customer_id', > new_column_name='customer_pk', > ) > > > I'm using alembic==1.0.0 and Python 3.6.4. > > Is there something wrong with the above code or is this a bug in the > library? > > Thanks > -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Alembic op.alter_column deletes all rows in the database table in error
I have an alembic migration which renames a FK column on a table from 'customer_id' to 'customer_pk'. I used to have more in the migration file but narrowed it down to this code causing all the rows to be deleted. def upgrade(): op.alter_column( 'daily_smart_meter_readings', column_name='customer_id', new_column_name='customer_pk', ) I'm using alembic==1.0.0 and Python 3.6.4. Is there something wrong with the above code or is this a bug in the library? Thanks -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
