[sqlalchemy] Problems with inserting data
Hi.
I've got autoloadable table:
---
class ObjectList(object):
pass
t_object_list = sa.Table('object_list', meta.metadata, autoload=True,
autoload_with=engine)
orm.mapper(ObjectList, t_object_list)
And when I'm trying insert data:
ol = ObjectList()
ol.type = data['type'],
ol.name = data['name'],
ol.address = data['address'],
ol.gps_lat = data['gps_lat'],
ol.gps_lng = data['gps_lng'],
ol._class = data['class'],
ol.has_parking = data['has_parking'],
ol.has_canteen = data['has_canteen'],
ol.has_cafe = data['has_cafe'],
ol.has_restaurant = data['has_restaurant'],
ol.has_fitness = data['has_fitness'],
ol.has_beauty_salon = data['has_beauty_salon'],
ol.has_tire_shop = data['has_tire_shop'],
ol.has_car_washer = data['has_car_washer'],
ol.has_bank = data['has_bank'],
ol.has_minibank = data['has_minibank'],
ol.has_payment_terminal = data['has_payment_terminal'],
ol.has_another = data['has_another']
meta.Session.add(ol)
meta.Session.commit()
I've got error:
ProgrammingError: (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 \'), (\'1\',), (\'1\',),
(\'1\',), (\'1\',), (\'A\',), (\'0\',), (\'0\',), (\'0\',),
(\'0\' at line 1') 'INSERT INTO object_list (type, name, address,
gps_lat, gps_lng, _class, has_parking, has_canteen, has_cafe,
has_restaurant, has_fitness, has_beauty_salon, has_tire_shop,
has_car_washer, has_bank, has_minibank, has_payment_terminal,
has_another) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s)' (('office',), ('1',)) ... and a total of 18
bound parameter sets
Where is problems ?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Problems with inserting data
at:
ol.type = data['type'],
print type(data['type'])
returns str
print ol.__dict__
returns:
{u'has_tire_shop': (False,), u'gps_lat': ('2',), '_sa_instance_state':
sqlalchemy.orm.state.InstanceState object at 0x7fcc6002b0d0,
u'name': ('2',), u'has_fitness': (False,), u'has_another': '',
u'has_cafe': (False,), u'has_restaurant': (False,), u'has_canteen':
(False,), u'has_payment_terminal': (False,), u'has_bank': (False,),
u'has_parking': (False,), u'address': ('2',), u'has_minibank':
(False,), u'has_car_washer': (False,), u'gps_lng': ('2',), u'type':
('office',), u'_class': ('A',), u'has_beauty_salon': (False,)}
On Oct 1, 2:19 am, Michael Bayer mike...@zzzcomputing.com wrote:
dont send tuples as bind parameters for scalar attributes. one or more
members of your data dictionary are tuples.
On Sep 30, 2010, at 6:12 PM, phasma wrote:
If add to sqlalchemy/orm/mapper.py at 1699 line this code:
if isinstance(value, tuple):
value = value[0]
Insert works correctly.
On Sep 30, 9:31 pm, phasma xpa...@gmail.com wrote:
Hi.
I've got autoloadable table:
---
class ObjectList(object):
pass
t_object_list = sa.Table('object_list', meta.metadata, autoload=True,
autoload_with=engine)
orm.mapper(ObjectList, t_object_list)
---
-
And when I'm trying insert data:
---
-
ol = ObjectList()
ol.type = data['type'],
ol.name = data['name'],
ol.address = data['address'],
ol.gps_lat = data['gps_lat'],
ol.gps_lng = data['gps_lng'],
ol._class = data['class'],
ol.has_parking = data['has_parking'],
ol.has_canteen = data['has_canteen'],
ol.has_cafe = data['has_cafe'],
ol.has_restaurant = data['has_restaurant'],
ol.has_fitness = data['has_fitness'],
ol.has_beauty_salon = data['has_beauty_salon'],
ol.has_tire_shop = data['has_tire_shop'],
ol.has_car_washer = data['has_car_washer'],
ol.has_bank = data['has_bank'],
ol.has_minibank = data['has_minibank'],
ol.has_payment_terminal = data['has_payment_terminal'],
ol.has_another = data['has_another']
meta.Session.add(ol)
meta.Session.commit()
I've got error:
ProgrammingError: (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 \'), (\'1\',), (\'1\',),
(\'1\',), (\'1\',), (\'A\',), (\'0\',), (\'0\',), (\'0\',),
(\'0\' at line 1') 'INSERT INTO object_list (type, name, address,
gps_lat, gps_lng, _class, has_parking, has_canteen, has_cafe,
has_restaurant, has_fitness, has_beauty_salon, has_tire_shop,
has_car_washer, has_bank, has_minibank, has_payment_terminal,
has_another) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s)' (('office',), ('1',)) ... and a total of 18
bound parameter sets
Where is problems ?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Problems with inserting data
I feel I need to sleep. Thank you :)
On Oct 1, 2:47 am, Michael Bayer mike...@zzzcomputing.com wrote:
you're doing this:
data = {}
data['type'] = 'some type'
class Foo(object):
pass
ol = Foo()
ol.type = data['type'],
print ol.type
its a tuple, lose the ','
On Sep 30, 2010, at 6:42 PM, phasma wrote:
at:
ol.type = data['type'],
print type(data['type'])
returns str
print ol.__dict__
returns:
{u'has_tire_shop': (False,), u'gps_lat': ('2',), '_sa_instance_state':
sqlalchemy.orm.state.InstanceState object at 0x7fcc6002b0d0,
u'name': ('2',), u'has_fitness': (False,), u'has_another': '',
u'has_cafe': (False,), u'has_restaurant': (False,), u'has_canteen':
(False,), u'has_payment_terminal': (False,), u'has_bank': (False,),
u'has_parking': (False,), u'address': ('2',), u'has_minibank':
(False,), u'has_car_washer': (False,), u'gps_lng': ('2',), u'type':
('office',), u'_class': ('A',), u'has_beauty_salon': (False,)}
On Oct 1, 2:19 am, Michael Bayer mike...@zzzcomputing.com wrote:
dont send tuples as bind parameters for scalar attributes. one or more
members of your data dictionary are tuples.
On Sep 30, 2010, at 6:12 PM, phasma wrote:
If add to sqlalchemy/orm/mapper.py at 1699 line this code:
if isinstance(value, tuple):
value = value[0]
Insert works correctly.
On Sep 30, 9:31 pm, phasma xpa...@gmail.com wrote:
Hi.
I've got autoloadable table:
---
class ObjectList(object):
pass
t_object_list = sa.Table('object_list', meta.metadata, autoload=True,
autoload_with=engine)
orm.mapper(ObjectList, t_object_list)
---
-
And when I'm trying insert data:
---
-
ol = ObjectList()
ol.type = data['type'],
ol.name = data['name'],
ol.address = data['address'],
ol.gps_lat = data['gps_lat'],
ol.gps_lng = data['gps_lng'],
ol._class = data['class'],
ol.has_parking = data['has_parking'],
ol.has_canteen = data['has_canteen'],
ol.has_cafe = data['has_cafe'],
ol.has_restaurant = data['has_restaurant'],
ol.has_fitness = data['has_fitness'],
ol.has_beauty_salon = data['has_beauty_salon'],
ol.has_tire_shop = data['has_tire_shop'],
ol.has_car_washer = data['has_car_washer'],
ol.has_bank = data['has_bank'],
ol.has_minibank = data['has_minibank'],
ol.has_payment_terminal = data['has_payment_terminal'],
ol.has_another = data['has_another']
meta.Session.add(ol)
meta.Session.commit()
I've got error:
ProgrammingError: (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 \'), (\'1\',), (\'1\',),
(\'1\',), (\'1\',), (\'A\',), (\'0\',), (\'0\',), (\'0\',),
(\'0\' at line 1') 'INSERT INTO object_list (type, name, address,
gps_lat, gps_lng, _class, has_parking, has_canteen, has_cafe,
has_restaurant, has_fitness, has_beauty_salon, has_tire_shop,
has_car_washer, has_bank, has_minibank, has_payment_terminal,
has_another) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s)' (('office',), ('1',)) ... and a total of 18
bound parameter sets
Where is problems ?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Fetching last insert id from MySQL.
session imported from Meta ? If use Meta.Session.execute it's returns
RowProxy, which has no lastrowid parameter.
On 16 сен, 02:59, Michael Bayer mike...@zzzcomputing.com wrote:
no its not a column on a row, its on the ResultProxy:
result = session.execute('...')
id = result.lastrowid
http://www.sqlalchemy.org/docs/core/connections.html?highlight=result...
On Sep 15, 2010, at 5:51 PM, phasma wrote:
Lastrowid return: Could not locate column in row for column
'lastrowid'. I try to use transaction:
trans = meta.Session.begin()
try:
meta.Session.execute(INSERT statement)
result = meta.Session.execute(SELECT LAST_INSERT_ID())
trans.commit()
except:
trans.rollback()
raise
Now, I'm testing this, think it helps to stop loosing session between
INSERT and SELECT.
On 15 ÓÅÎ, 21:45, Michael Bayer mike...@zzzcomputing.com wrote:
SELECT LAST_INSERT_ID() is ultimately where the value comes from - the
raw .lastrowid is present on the ResultProxy for those DBAPIs which
support it, so try using that. Perhaps you're getting 0 because the
transaction is going away, in which case .lastrowid should solve that
issue.
On Sep 15, 2010, at 12:45 PM, phasma wrote:
I've got database with auto increment column called `id` and INSERT
query, whom I need to execute without model declaration in project.
meta.Session.execute() returns ResultProxy, but last_inserted_ids()
doesn't work with execute() and SELECT LAST_INSERT_ID() statement
sometimes return 0. Is there any other way to fetch last inserted id ?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Fetching last insert id from MySQL.
I've found a solution. meta.Session.execute returns RowProxy instead of ResultProxy. Example: query = meta.engine.text(INSERT [...] VALUES(:text, :text1)) result = query.execute(text=123, text1=123) print result.lastrowid On 16 сен, 16:18, Chris Withers ch...@simplistix.co.uk wrote: On 16/09/2010 11:49, phasma wrote: session imported from Meta ? If use Meta.Session.execute it's returns RowProxy, which has no lastrowid parameter. Try this: with meta.Session: result = meta.Session.execute(INSERT statement) print result.lastrowid cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting -http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Fetching last insert id from MySQL.
I've got database with auto increment column called `id` and INSERT query, whom I need to execute without model declaration in project. meta.Session.execute() returns ResultProxy, but last_inserted_ids() doesn't work with execute() and SELECT LAST_INSERT_ID() statement sometimes return 0. Is there any other way to fetch last inserted id ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Fetching last insert id from MySQL.
Lastrowid return: Could not locate column in row for column 'lastrowid'. I try to use transaction: trans = meta.Session.begin() try: meta.Session.execute(INSERT statement) result = meta.Session.execute(SELECT LAST_INSERT_ID()) trans.commit() except: trans.rollback() raise Now, I'm testing this, think it helps to stop loosing session between INSERT and SELECT. On 15 сен, 21:45, Michael Bayer mike...@zzzcomputing.com wrote: SELECT LAST_INSERT_ID() is ultimately where the value comes from - the raw .lastrowid is present on the ResultProxy for those DBAPIs which support it, so try using that. Perhaps you're getting 0 because the transaction is going away, in which case .lastrowid should solve that issue. On Sep 15, 2010, at 12:45 PM, phasma wrote: I've got database with auto increment column called `id` and INSERT query, whom I need to execute without model declaration in project. meta.Session.execute() returns ResultProxy, but last_inserted_ids() doesn't work with execute() and SELECT LAST_INSERT_ID() statement sometimes return 0. Is there any other way to fetch last inserted id ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: MySQL encoding
What character set is the db-api driver using? Try: engine.connect().connection.character_set_name() If it's not utf8, you can configure the driver by adding 'charset=utf8' to your database url. I add charset='utf-8' to 'create_engine' function, but before send data(from query) to mako i need decode string from UTF-8 ... How can i do that automatically(decoding) ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MySQL encoding
On 12 янв, 04:07, jason kirtland [EMAIL PROTECTED] wrote: phasma wrote: What character set is the db-api driver using? Try: engine.connect().connection.character_set_name() If it's not utf8, you can configure the driver by adding 'charset=utf8' to your database url. I add charset='utf-8' to 'create_engine' function, but before send data(from query) to mako i need decode string from UTF-8 ... How can i do that automatically(decoding) ? Use the Unicode column type in your table declarations for these columns, or for global behavior you can add convert_unicode=True to your create_engine() arguments (not the url). With this driver you can also get some increased efficiency by having it do the Unicode translation. If all you've added to the engine URL is 'charset' it should already be in Unicode mode- try adding 'use_unicode=1' to the url as well if you're not getting Unicode strings from queries. Full info: http://www.sqlalchemy.org/docs/04/sqlalchemy_databases_mysql.html Cheers, Jason Big thanks ) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] MySQL encoding
Hi! I'm using mysql *** 1. row *** Variable_name: character_set_client Value: utf8 *** 2. row *** Variable_name: character_set_connection Value: utf8 *** 3. row *** Variable_name: character_set_database Value: utf8 *** 4. row *** Variable_name: character_set_filesystem Value: binary *** 5. row *** Variable_name: character_set_results Value: utf8 *** 6. row *** Variable_name: character_set_server Value: utf8 *** 7. row *** Variable_name: character_set_system Value: utf8 *** 8. row *** Variable_name: character_sets_dir Value: /usr/local/share/mysql/charsets/ I have custom query: pub = Session.execute(SELECT a.header, a.name FROM publication as a USE INDEX(ix_newzee_publication_posttime) LEFT JOIN publication_category b ON b.id_publication = a.id WHERE a.status = 1 AND a.posttime BETWEEN '1970-01-01 01:01:01' AND NOW() AND b.id_category = 9 ORDER BY a.posttime DESC LIMIT 10).fetchall() Query return error : type 'exceptions.UnicodeDecodeError': 'ascii' codec can't decode byte 0xd0 in position 21: ordinal not in range(128) Does custom query return data in utf-8? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
