On Fri, Aug 2, 2019, at 2:09 AM, Padam Sethia wrote:
>
> Hey Guys !
> So as the title says it all , I'm try to do the above , but I'm getting SQL
> Syntax error when I'm using db.Index to create a unique index . I've tried
> all the solutions but nothing seems to work.
> P.S : I'm using Flask-SQLalchemy with MySQL.
> Here's my table
> `class FinGoods(db.Model):
id = db.Column(db.Integer , primary_key = True)
product_category = db.relationship('FinCat' ,cascade="all,delete",
secondary='cat_goods' ,
backref='cat_goods' , lazy = 'joined')
fabric_combo = db.relationship('FabComb' ,cascade="all,delete",
secondary='comb_goods' ,
backref='comb_goods' , lazy = 'joined')
print_tech = db.relationship('PrintTech' ,cascade="all,delete",
secondary='tech_goods' ,
backref='print_goods' , lazy = 'joined')
design = db.relationship('FinDes' ,cascade="all,delete",
secondary='des_goods' ,
backref='des_goods' , lazy = 'joined')
uom = db.relationship('Uom' ,cascade="all,delete", secondary='uom_goods' ,
backref='uom_goods' ,
lazy = 'joined')
alt_name = db.Column(db.String(200))
gen_name = db.Column(db.String(100))
def get_gen_name(self):
product_category = self.product_category
fabric_combo = self.fabric_combo
print_tech = self.print_tech
design = self.design
uom = self.uom
display_name = "{} / {} / {} / {}".format(product_category[0].cat,
fabric_combo[0].comb,
print_tech[0].tech, design[0].des)
return display_name
db.Table('cat_goods',
db.Column('cat_id' , db.Integer , db.ForeignKey('fin_cat.id' ,
ondelete='SET NULL' )),
db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' ,
ondelete='SET NULL'))
)
db.Table('comb_goods',
db.Column('comb_id' , db.Integer , db.ForeignKey('fab_comb.id' ,
ondelete='SET NULL' )),
db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' ,
ondelete='SET NULL'))
)
db.Table('tech_goods',
db.Column('tech_id' , db.Integer , db.ForeignKey('print_tech.id' ,
ondelete='SET NULL' )),
db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' ,
ondelete='SET NULL'))
)
db.Table('des_goods',
db.Column('des_id' , db.Integer , db.ForeignKey('fin_des.id' ,
ondelete='SET NULL' )),
db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' ,
ondelete='SET NULL'))
)
db.Table('uom_goods',
db.Column('uom_id' , db.Integer , db.ForeignKey('uom.id' , ondelete='SET
NULL' )),
db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' ,
ondelete='SET NULL'))
)
`
> Here's the code for the unique Index :
> `db.Index('fin_goods_unq', cat_goods.cat_id,
> fin_goods.fabric_combo,fin_goods.print_tech , fin_goods.design,
> fin_goods.uom, unique=True)`
you're using relationship attributes in your Index, which is not allowed. a SQL
INDEX is against database columns, so you'd need to create an Index that has
only column attrfibutes inside of it. But also, an INDEX is only against one
table at a time, so it's not valid to have columns from "cat_goods" and
"fin_goods" at the same time.
What you want to do is design your database schema from SQL, e.g. as a series
of approximate CREATE TABLE and CREATE INDEX statements first. Then model your
ORM models around that.
> ``
> But it's throwing a SQL Syntax error , which I'm unable to wrap my head
> around.
> `sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You
> have an error in your SQL syntax; check the manual that corresponds to your
> MySQL server version for the right syntax to use near '= raw_fab_id AND id =
> cat_id, id = raw_fab_id AND id = comb_id, id = raw_fab_id ' at line 1")
[SQL: CREATE UNIQUE INDEX raw_goods_unq ON raw_fab_main (id = raw_fab_id AND id
= cat_id, id = raw_fab_id AND id = comb_id, id = raw_fab_id AND id = const_id,
id = raw_fab_id AND id = proc_id, id = raw_fab_id AND id = width_id, id =
raw_fab_id AND id = dye_id)]
(Background on this error at: http://sqlalche.me/e/f405)
`
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/28cf95ee-8cf8-48b5-a120-0ffcad64368d%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/28cf95ee-8cf8-48b5-a120-0ffcad64368d%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/cfd009b8-c082-401e-8539-bf7c597d922a%40www.fastmail.com.
