[sqlalchemy] bulk insert
Hi, I'm new to this group - here's my question, I've been fighting
with this for a while now
I need to insert a list of lists into a database. It works using a for
loop but this is very slow so I want to optimize it. The method of
bulk inserting in SQLAlchemy is much faster but only seems to take a
list of dictionaries, so I converted the list of lists to a list of
dictionaries using a list comprehension:
values = [{'name': list[0], 'age': list[1], 'email': list[2]} for list
in list_of_lists] (the real list is alot longer)
Unfortunately I get an 'index out of range' error when this runs.
Is there any way of doing this more simply? When using the Python
DBAPI it is as simple as calling executemany(). It seems odd that
there is not a simple method like that in SQLAlchemy.
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] Re: bulk insert
On Apr 24, 2008, at 2:23 AM, Grimsqueaker wrote:
Hi, I'm new to this group - here's my question, I've been fighting
with this for a while now
I need to insert a list of lists into a database. It works using a for
loop but this is very slow so I want to optimize it. The method of
bulk inserting in SQLAlchemy is much faster but only seems to take a
list of dictionaries, so I converted the list of lists to a list of
dictionaries using a list comprehension:
values = [{'name': list[0], 'age': list[1], 'email': list[2]} for list
in list_of_lists] (the real list is alot longer)
Unfortunately I get an 'index out of range' error when this runs.
Is there any way of doing this more simply? When using the Python
DBAPI it is as simple as calling executemany(). It seems odd that
there is not a simple method like that in SQLAlchemy.
This would imply youre using a database that supports positional bind
parameters, so im going to guess MySQL. you can do straight-to-
DBAPI executes if you use a string:
engine.execute(insert into table (col1, col2) values (%s %s),
*list_of_lists)
as far as your index out of range error, the code youre using seems
correct so this would imply that one or more of the lists within your
list of lists is not of length three - but that would fail with a
direct DBAPI usage as well since one bind parameter would be left
unfulfilled.
--~--~-~--~~~---~--~~
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: Reflecting tables, Unicodecolumnames and orm.mapper()
Thank you for your help. I followed your advice and have overwritten
the colums with umlauts with ascii-compatible names:
Table('foo', metadata,
Column(usomeunicodename, key=someasciiname),
autoload=True
)
To test this, i tried a select on my table, which resulted in
another unicode-problem, because SQL-Alchemy is uses the original
columnnames to generate sql code. What am I doing wrong?
File 'c:\\TTL\\ttl\\controllers\\mlfb.py', line 18 in index_mlfb
c.items=model.t_mlfb.select().order_by(column).execute()
File 'c:\\python25\\lib\\site-packages\\sqlalchemy-0.4.5-py2.5.egg\
\sqlalchemy\\sql\\expression.py', line 1057 in execute
return e.execute_clauseelement(self, multiparams, params)
File 'c:\\python25\\lib\\site-packages\\sqlalchemy-0.4.5-py2.5.egg\
\sqlalchemy\\engine\\base.py', line 1219 in execute_clauseelement
return connection.execute_clauseelement(elem, multiparams, params)
File 'c:\\python25\\lib\\site-packages\\sqlalchemy-0.4.5-py2.5.egg\
\sqlalchemy\\engine\\base.py', line 895 in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
File 'c:\\python25\\lib\\site-packages\\sqlalchemy-0.4.5-py2.5.egg\
\sqlalchemy\\engine\\base.py', line 910 in _execute_compiled
return context.result()
File 'c:\\python25\\lib\\site-packages\\sqlalchemy-0.4.5-py2.5.egg\
\sqlalchemy\\engine\\default.py', line 284 in result
return self.get_result_proxy()
File 'c:\\python25\\lib\\site-packages\\sqlalchemy-0.4.5-py2.5.egg\
\sqlalchemy\\engine\\default.py', line 293 in get_result_proxy
return base.ResultProxy(self)
File 'c:\\python25\\lib\\site-packages\\sqlalchemy-0.4.5-py2.5.egg\
\sqlalchemy\\engine\\base.py', line 1402 in __init__
self._init_metadata()
File 'c:\\python25\\lib\\site-packages\\sqlalchemy-0.4.5-py2.5.egg\
\sqlalchemy\\engine\\base.py', line 1433 in _init_metadata
colname = item[0].decode(self.dialect.encoding)
File 'C:\\Python25\\lib\\encodings\\utf_8.py', line 16 in decode
return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: 'utf8' codec can't decode bytes in position 6-7:
invalid data
On 23 Apr., 15:07, Michael Bayer [EMAIL PROTECTED] wrote:
On Apr 23, 2008, at 3:50 AM, Saibot wrote:
mapper.py, line 669, in _compile_property
setattr(self.class_, key, Mapper._CompileOnAttr(self.class_, key))
File c:\python25\lib\site-packages\sqlalchemy-0.4.4-py2.5.egg
\sqlalchemy\orm\
mapper.py, line 539, in __init__
self.existing_prop = getattr(class_, key, None)
UnicodeEncodeError: 'ascii' codec can't encode character u'\xdf' in
position 6:
ordinal not in range(128)
Is there a way to tell orm.mapper() to encode the columnnames into
iso-8859-1 rather than ascii?
this error is specifically your mapper() attempting to assign a class-
bound descriptor to your class using a key name with non ASCII
characters in it. Python doesn't allow this, so you have to assign
key values to your columns, either like this, at the Table level:
Table('foo', metadata,
Column(someunicodename, Integer, key=someasciiname),
autoload=True
)
or like this, at the mapper() level:
mapper(MyClass, mytable, properties={
someasciiname : mytable.c['someunicodename']
})- Zitierten Text ausblenden -
- Zitierten Text anzeigen -
--~--~-~--~~~---~--~~
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: Reflecting tables, Unicodecolumnames and orm.mapper()
On Apr 24, 2008, at 9:45 AM, Saibot wrote:
Thank you for your help. I followed your advice and have overwritten
the colums with umlauts with ascii-compatible names:
Table('foo', metadata,
Column(usomeunicodename, key=someasciiname),
autoload=True
)
OK the next step is to set the encoding on your engine to match the
database encoding. the stacktrace here is specifically a result set
coming back to SQLAlchemy which then tries to decode the names within
cursor.description to Python unicode objects - it defaults to
'utf-8'. So create engine as such:
e = create_engine('dialect://myengine', encoding='iso-8859-1')
--~--~-~--~~~---~--~~
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] How to lock tables in mysql with SqlAlchemy?
Dear All, I would like to lock a table like LOCK TABLES table_name in mysql command. How can I do that with SqlAlchemy. I have defined a class |//|//|class Pointer(Entity): using_options(tablename='Pointer',autosetup=True) id=Field(MSInteger,primary_key=True) ||I need to lock table ||'Pointer'.|// --~--~-~--~~~---~--~~ 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: ODBC Connection is busy error
Hi,
Since our system went live we have been getting more more errors
like this: DBAPIError: (Error) ('HY000', '[HY000] [Microsoft][SQL
Native Client]Connection is busy with results for another command
(0)') u'SELECT ...snip valid SQL string...endsnip
I've seen this error too, in fact some of the unit tests trigger it. The
cause is that a single ODBC connection can only have one query active at
a time, even if you have multiple cursors. I believe this is different
to most other DBAPI drivers.
I have no idea how to fix this in SQLAlchemy, have thought about it a
bit without success. A workaround is to recode your app to it fetches
results right after each query.
Paul
--~--~-~--~~~---~--~~
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: ODBC Connection is busy error
perhaps we could simply reset the pyodbc cursor before issuing a new SQL operation? class MSSQLExecutionContext(default.DefaultExecutionContext): def pre_exec(self): if self.dialect.clear_previous_results: self.cursor.clear_previous_results_somehow_idunnohow() --~--~-~--~~~---~--~~ 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: ODBC Connection is busy error
On Apr 24, 2008, at 1:04 PM, Paul Johnston wrote:
Hi,
Since our system went live we have been getting more more errors
like this: DBAPIError: (Error) ('HY000', '[HY000] [Microsoft][SQL
Native Client]Connection is busy with results for another command
(0)') u'SELECT ...snip valid SQL string...endsnip
I've seen this error too, in fact some of the unit tests trigger it.
The
cause is that a single ODBC connection can only have one query
active at
a time, even if you have multiple cursors. I believe this is different
to most other DBAPI drivers.
I have no idea how to fix this in SQLAlchemy, have thought about it a
bit without success. A workaround is to recode your app to it fetches
results right after each query.
we used to have a feature on the Pool called auto_close_cursors -
this would track all open cursors at the pool level, and when a
connection is re-checked in would close them all. In reality, this
feature was in most cases just hiding opened cursors that should have
been closed at higher levels, and added a lot of latency at a crucial
point in execution. So in modern SQLAlchemy, cursors are closed
automatically when: 1. the statement is a non-row returning statement
like INSERT, UPDATE, etc. 2. all result rows are exhausted from a
result. (When the ORM is used, result rows are always fully exhausted
unless the yield_per Query option is used). So if you are using
straight connection or engine result sets, and are not explicitly
exhausting all rows, call result.close() to explicitly release the
cursor. That will solve the problem.
--~--~-~--~~~---~--~~
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] filter_by mapped class attribute?
I have 2 classes A and B which are mapped each to their own table.
There is a foreign key which defines a one to many relationship from A
to B.
Is it possible to query B, but filter on an attribute of A?
ctx.current.query(B).filter_by(A.c.name.like('%foo%'))
This seems to work, but the query to the DB seems to be missing the
join between the tables for A and B...
Sqlalchemy 0.3 BTW...
thx
Matt
--~--~-~--~~~---~--~~
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] Summer Python Internship - National Renewable Energy Laboratory
Student Intern – Scientific Computing Group 5900 5900-7259 A student internship is available in the National Renewable Energy Laboratory's (NREL) Scientific Computing Group. NREL is the nation's primary laboratory for research, development and deployment of renewable energy and energy efficiency technologies. The intern will be supporting work concerning management of scientific and technical data. Our data group is cutting-edge with respect to capturing rapidly changing scientific metadata and allowing the scientists to relate different kinds of data in a meaningful way. We have an immediate opening for a summer student internship with possible extension to one year in our Golden, Colorado office. The position would be part-time (15 - 25 hours per week) during the school year and/or full time during the summer. DUTIES: Will include working with researchers on techniques to enable the capture and storage of technical data in a scientific setting. Your role in our development team would be to support data harvesting using existing software, and develop new visualization techniques for existing data sets. DESIRED QUALIFICATIONS: Undergraduate or graduate student in computer science or related field, with demonstrated experience in programming, databases and software development. Experience using agile techniques and test-driven development. Demonstrated of Unit Testing. Experience with major dynamic languages like Python, Ruby, or C#. PREFERRED: Demonstrated good writing skills and computer skills, specifically including programming in python and database use. Experience with systems related to management of scientific data. Candidate must be a US citizen. Qualified candidates should e-mail their resume to: Laura Davis NREL, Human Resources Office Reference: Req. #5900-7259 E-Mail: [EMAIL PROTECTED] = feel free to email me with any questions. cheers. -chris --~--~-~--~~~---~--~~ 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: How to lock tables in mysql with SqlAlchemy?
Ting Zhou wrote: Dear All, I would like to lock a table like LOCK TABLES table_name in mysql command. How can I do that with SqlAlchemy. I have defined a class |//|//|class Pointer(Entity): using_options(tablename='Pointer',autosetup=True) id=Field(MSInteger,primary_key=True) ||I need to lock table ||'Pointer'.|// You can lock the tables by executing the SQL directly. I'm not sure what that looks like in Elixir, but in plain SA it'd be something like: conn = engine.connect() conn.execute(LOCK TABLES Pointer WRITE) ... do stuff with conn conn.execute(UNLOCK TABLES) --~--~-~--~~~---~--~~ 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: filter_by mapped class attribute?
ctx.current.query(B).filter_by(A.c.name.like('%foo%'))
added the join manually:
ctx.current.query(B).filter(B.c.xid ==
A.c.xid).filter_by(A.c.name.like('%foo%'))
m
On Apr 24, 2:33 pm, Matt [EMAIL PROTECTED] wrote:
I have 2 classes A and B which are mapped each to their own table.
There is a foreign key which defines a one to many relationship from A
to B.
Is it possible to query B, but filter on an attribute of A?
ctx.current.query(B).filter_by(A.c.name.like('%foo%'))
This seems to work, but the query to the DB seems to be missing the
join between the tables for A and B...
Sqlalchemy 0.3 BTW...
thx
Matt
--~--~-~--~~~---~--~~
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: filter_by mapped class attribute?
On Apr 24, 2008, at 6:53 PM, Matt wrote:
ctx.current.query(B).filter_by(A.c.name.like('%foo%'))
added the join manually:
ctx.current.query(B).filter(B.c.xid ==
A.c.xid).filter_by(A.c.name.like('%foo%'))
in 0.3, also consider
query(B).join('arelation').filter(A.c.name=='foo')
a lot more options for this kind of thing in 0.4 too
--~--~-~--~~~---~--~~
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: ODBC Connection is busy error
Thank you to everybody for your comments on this problem... Michael, re: your suggestion about result.close(), is this something that I could add to mssql.py, or do you think it's something that I would need to add throughout my application everytime I access the db? (It's a big application...) Cheers! On Apr 25, 4:15 am, Michael Bayer [EMAIL PROTECTED] wrote: So if you are using straight connection or engine result sets, and are not explicitly exhausting all rows, call result.close() to explicitly release the cursor. That will solve the problem. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
