Solution for Mysql Pipeline for table without AUTO_INCREMENT and withoutUNIQUE
.
Anybody know more elegant solution?
CREATE TABLE `category_description` (
`category_id` int(11) NOT NULL,
`language_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`category_id`,`language_id`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE `category` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) NOT NULL DEFAULT '0',
`top` tinyint(1) NOT NULL,
`status` tinyint(1) NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE `category_to_store` (
`category_id` int(11) NOT NULL,
`store_id` int(11) NOT NULL,
PRIMARY KEY (`category_id`,`store_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
def _conditional_insert(self, tx, item):
tx.execute("""SELECT MAX(category_id) as xx FROM
category_description""")
max = tx.fetchall()[0]['xx'] or 0
tx.execute("""INSERT INTO category_description (category_id, name,
language_id) VALUES (%s, %s, %s)""",(max+1 , item['category'][0], 1))
category_id = tx.lastrowid
On Tuesday, December 10, 2013 9:54:04 PM UTC+2, frogwares wrote:
>
> Hi,eveyone!
> Need suggestions how to catch category_id for category name in mysql pipeline
> if NO AUTO_INCREMENT and NO UNIQUE (see example
>
> table `category_description)
>
> .
> For table `product_to_category` product_id found ok with lastrowid
>
> class MySQLStorePipeline(object):
> def __init__(self):
> self.conn = MySQLdb.connect(user='user', passwd='pass', db='test',
> host='localhost', charset='utf8', use_unicode=True)
> self.cursor = self.conn.cursor()
> try:
> self.cursor.execute("select * from it_category_description where name =
> %s",(item['category']))
> result = self.cursor.fetchone()
> if result:
> log.msg("Item already stored in db: %s" % item, level=log.DEBUG)
> else:
> self.cursor.execute("""ALTER TABLE it_category_description CHANGE
> category_id category_id INT(11) NOT NULL AUTO_INCREMENT""")
> self.cursor.execute("""INSERT INTO it_category_description (name) VALUES
> (%s)""", (item['category']))
> self.conn.commit()
> category_id = self.cursor.lastrowid
> print '***********', self.cursor.lastrowid
>
> --
> -- Example table structure for table `category_description`
> --
>
> CREATE TABLE `category_description` (
> `category_id` int(11) NOT NULL, # NO AUTO_INCREMENT
> `language_id` int(11) NOT NULL,
> `name` varchar(255) NOT NULL, # NOT UNIQUE
> PRIMARY KEY (`category_id`,`language_id`),
> KEY `name` (`name`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
>
> --
> -- Example table structure for table `category`
> --
>
> CREATE TABLE `category` (
> `category_id` int(11) NOT NULL AUTO_INCREMENT,
> `parent_id` int(11) NOT NULL DEFAULT '0',
> `status` tinyint(1) NOT NULL, PRIMARY KEY (`category_id`),
> KEY `parent_id` (`parent_id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
>
> --
> -- Example table structure for table `product`
> --
>
> CREATE TABLE `product` (
> `product_id` int(11) NOT NULL AUTO_INCREMENT,
> `model` varchar(64) NOT NULL,
> `manufacturer_id` int(11) NOT NULL,
> `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
> PRIMARY KEY (`product_id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
>
> --
> -- Example table structure for table `product_to_category`
> --
>
> CREATE TABLE `product_to_category` (
> `product_id` int(11) NOT NULL,
> `category_id` int(11) NOT NULL,
> PRIMARY KEY (`product_id`,`category_id`),
> KEY `product_id` (`product_id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
>
> --
> -- Example table structure for table `product_description`
> --
>
> CREATE TABLE `product_description` (
> `product_id` int(11) NOT NULL,
> `language_id` int(11) NOT NULL,
> `name` varchar(255) NOT NULL,
> PRIMARY KEY (`product_id`,`language_id`),
> KEY `name` (`name`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
>
>
>
>
>
>
>
--
You received this message because you are subscribed to the Google Groups
"scrapy-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/scrapy-users.
For more options, visit https://groups.google.com/groups/opt_out.
