[sqlalchemy] Re: Advice: Best practice for working with an existing database
On Monday, March 18, 2013 3:25:36 PM UTC-6, Peter Herndon wrote: > > Hi all, > > I'm new to SQLAlchemy, and looking for some advice on how to approach > working with an existing database. > My preferred approach is to use http://code.google.com/p/sqlautocode/ and generate a module describing the schema. Then I tweak/cleanup/reformat the schema.py to my liking. For example I may use "key" argument in the Column definition to change the name or comment out add_date, add_user columns. Since the databases I work with are fairly stable it is an acceptable tradeoff. Although sqlautocode can produce the Declarative format I tend to stick with a separate schema.py and model.py defining classical mapping. I have been using SQLAlchemy since before Declarative was available (started with 0.1 series) and the classical mapping feels more natural. Also if I ever need to re-genrate schema.py my model/mapping is not affected. Waldemar -- 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 http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Alembic 0.5.0 Released
Hey gang - Alembic 0.5.0 is now available. We've accumulated a significant bunch of bug fixes and new features, quite a few of which were contributed via pull requests. Thanks to all involved to get this release packed with a lot of new stuff. With version 0.5, we've dropped support for SQLAlchemy 0.6 and you should at least be on a recent 0.7 series (>= 0.7.3) for this version. Full list of changes can be viewed at: https://alembic.readthedocs.org/en/latest/changelog.html#change-0.5.0 . Download Alembic 0.5.0 at: https://pypi.python.org/pypi/alembic/ - mike -- 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 http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] mysql and python datetime
I am trying to insert records to a mysql database via python using the
sqlalchemy package.
I have columns that are datetime type in mysql that already have data of
the following format:
2013-04-03 00:05:00-05:00'
Note this is produced by the pytz module in python. I had no problem
loading 600,000 rows with datetime stamps with the exact same format
through the mysql console using the load table inline command.
This tells my that mysql is capable of accepting the format shown above.
This is as per mysql's documentation:
The DATETIME type is used for values that contain both date and time parts.
MySQL
retrieves and displays DATETIME values in '-MM-DD HH:MM:SS' format. The
supported
range is '1000-01-01 00:00:00' to '-12-31 23:59:59'.
The thing is the pytz module provides information on the time zone, which I
need. I actually have 4 columns (EST, UTC, EST/EDT and CST/CDT) so all have
their time zone information embedded in the datetime stamp.
Note I am not using a custom INSERT query. This is the default way
sqlalchemy performs an insert many:
The function that performs the insert looks like so:
def insert_data(data_2_insert, table_name):
# Connect to database using SQLAlchemy's create_engine()
engine = create_engine('mysql://blah:blah@localhost/db_name')
# Metadata is a Table catalog.
metadata = MetaData()
my_table = Table(table_name, metadata, autoload=True, autoload_with=engine)
column_names = tuple(c.name for c in my_table.c)
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
ins = my_table.insert()
conn = engine.connect()
conn.execute(ins, final_data)
conn.close()
Traceback (most recent call last):
File "script.py", line 191, in
main()
File "script.py", line 39, in main
insert_data(file_csv, table_name)
File "script.py", line 58, in insert_data
conn.execute(ins, final_data)
File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line
824, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line
874, in _execute_clauseelement
return self.__execute_context(context)
File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line
894, in __execute_context
self._cursor_executemany(context.cursor, context.statement,
context.parameters, context=context)
File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line
960, in _cursor_executemany
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line
931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) (1292, "Incorrect
datetime value: '2013-04-03 00:05:00-05:00' for column 'rtc_date_est' at row 1")
python
mysql
insert
sqlalchemy
share
|edit
|close|delete
|flag
edited just now
asked 15 mins ago
algotr8der
406211
add comment
question eligible for bounty in 2 days
Know someone who can answer? Share a
linkto this question via
email,
Google+,
Twitter,
or
Facebook.
Would you like to have responses to your questions sent to you via
email?
tagged
python × 176908
mysql × 156059
insert × 4685
sqlalchemy × 2670
asked
*today*
viewed
*15 times*
[sqlalchemy] ORM Join with Group By
I was wondering whether someone would be able to help me out with a
SQLAlchemy query (not sure whether this is the correct term)
Assume a database with two tables; sessions and events. Sessions has a
primary key id and some additional information. Events has a primary key
which consists of session_id (foreign key to Sessions) and event_id
which is using a sequence. This table contains an additional column
which contains the value I am interested in.
SESSIONS
- SESSION_ID [PK]
- DATE
EVENTS
- SESSION_ID [PK + FK]
- EVENT_ID [PK]
- CODE
I assume the corresponding classes in Python/SQLAlchemy are self
explanitory...
Each session has multiple events, what I am interested in is the code of
the last event per session (event with highest event id).
I managed to come up with the SQL which will do this:
SELECT
SSN.SESSION_ID,
SSN.DATE
FROM
SESSIONS SSN
LEFT JOIN
(SELECT
EVT.SESSION_ID,
EVT.CODE
FROM
EVENTS EVT
INNER JOIN
(SELECT
SESSION_ID,
MAX(EVENT_ID) AS EVENT_ID
FROM
EVENTS
GROUP BY
SESSION_ID) LAST_EVENT
ON
AEL.SESSION_ID = LAST_EVENT.SESSION_ID AND
AEL.EVENT_ID = LAST_EVENT.EVENT_ID) EVENT
ON
EVENT.SESSION_ID = SSN.SESSION_ID
ORDER BY
SSN.SESSION_ID;
Hence initially getting the max event_id, which is then joined with the
events table to extract the code, which in the end is join with the
sessions table.
I tried to transform this query to SQLAlchemy, but am getting stuck.
Looking at the various examples as found in the documentation I gathered
that I should start with the creation of a sub-query:
stmt = oracle.query(Event.session_id,
func.max(Event.id).label("last_event")).group_by(Event.session_id).subquery()
But I can not figure out how to use this sub-query in the two remaining
joins...
Could anyone point me in the right directions?
Thijs
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] implementing one-to-many relationship?
the requery is due to the default expire_on_commit of session.commit():
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#committing. Feel free
to disable this feature if you don't need it.
as far as one-to-many, I don't see the use of relationship() here, you'd likely
find it easier to use rather than assigning primary key identities to foreign
key attributes directly:
http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#building-a-relationship
On Apr 3, 2013, at 2:49 PM, James Hartley wrote:
> I have implemented a (simplified) one-to-many relationship which works, but I
> suspect I am reimplementing functionality in a suboptimal fashion which is
> already done by SQLAlchemy. The following short example:
>
> 8<---
> #!/usr/bin/env python
>
> import datetime
> from sqlalchemy import create_engine, Column, ForeignKey
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker, relationship, backref
> from sqlalchemy.schema import UniqueConstraint
> from sqlalchemy.dialects.sqlite import INTEGER, TEXT, DATETIME
>
> Base = declarative_base()
> Session = sessionmaker()
> engine = create_engine('sqlite:///test.db', echo=True)
>
> class Subordinate(Base):
> __tablename__ = 'subordinate'
>
> id = Column(INTEGER, primary_key=True)
> name = Column(TEXT, unique=True, nullable=False)
> discovered = Column(DATETIME, nullable=False)
> discontinued = Column(DATETIME, nullable=True)
>
> def __init__(self, name):
> """constructor"""
> self.name = name
> self.discovered = datetime.datetime.now()
>
> def __repr__(self):
> """string representation overload"""
> return '' % (self.id, self.discovered,
> self.discontinued)
>
> class Record(Base):
> __tablename__ = 'record'
>
> id = Column(INTEGER, primary_key=True)
> subordinate_id = Column(INTEGER, ForeignKey('subordinate.id'),
> nullable=False)
> timestamp = Column(DATETIME, nullable=False)
> UniqueConstraint('subordinate_id', 'timestamp', name='occurrence')
>
> def __init__(self, subordinate):
> """constructor"""
> self.subordinate_id = subordinate.id
> self.timestamp = datetime.datetime.now()
>
> def __repr__(self):
> """string representation overload"""
> return '' % (self.id, self.subordinate_id,
> self.timestamp)
>
> if __name__ == '__main__':
> Session.configure(bind=engine)
> session = Session()
>
> Base.metadata.create_all(engine)
>
> d = {'subordinates':{}, 'records':{}}
>
> lst = []
> for p in ('abc', 'ijk', 'xyz'):
> d['subordinates'][p] = Subordinate(p)
> lst.append(d['subordinates'][p])
> session.add_all(lst)
> session.commit()
>
> lst = []
> for p in ('abc', 'ijk', 'xyz'):
> d['records'][p] = Record(d['subordinates'][p])
> lst.append(d['records'][p])
> session.add_all(lst)
> session.commit()
> 8<---
> I am finding it curious in the following output that once the subordinate
> tuples are committed, SQLAlchemy is querying the database once again to
> retrieve the primary keys of the second table. Am I performing too much work
> in client code?
>
> Any insight shared would be appreciated.
>
> 8<---
> 2013-04-03 13:35:38,291 INFO sqlalchemy.engine.base.Engine ()
> 2013-04-03 13:35:38,293 INFO sqlalchemy.engine.base.Engine COMMIT
> 2013-04-03 13:35:38,297 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
> 2013-04-03 13:35:38,299 INFO sqlalchemy.engine.base.Engine INSERT INTO
> subordinate (name, discovered, discontinued) VALUES (?, ?, ?)
> 2013-04-03 13:35:38,300 INFO sqlalchemy.engine.base.Engine ('abc',
> '2013-04-03 13:35:38.296111', None)
> 2013-04-03 13:35:38,301 INFO sqlalchemy.engine.base.Engine INSERT INTO
> subordinate (name, discovered, discontinued) VALUES (?, ?, ?)
> 2013-04-03 13:35:38,301 INFO sqlalchemy.engine.base.Engine ('ijk',
> '2013-04-03 13:35:38.296223', None)
> 2013-04-03 13:35:38,302 INFO sqlalchemy.engine.base.Engine INSERT INTO
> subordinate (name, discovered, discontinued) VALUES (?, ?, ?)
> 2013-04-03 13:35:38,302 INFO sqlalchemy.engine.base.Engine ('xyz',
> '2013-04-03 13:35:38.296309', None)
> 2013-04-03 13:35:38,303 INFO sqlalchemy.engine.base.Engine COMMIT
> 2013-04-03 13:35:38,305 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
> 2013-04-03 13:35:38,306 INFO sqlalchemy.engine.base.Engine SELECT
> subordinate.id AS subordinate_id, subordinate.name AS subordinate_name,
> subordinate.discovered AS subordinate_discovered, subordinate.discontinued AS
> subordinate_discontinued
> FROM subordinate
> WHERE subordinate.id = ?
> 2013-04-03 13:35:38,306 INFO sqlalchemy.engine.base.Engine (1,)
> 2013-04-03 13:35:38,309 INFO sqlalchemy.engine.base.Engine SELECT
> subordinate.id AS subordinate_id, subordinate.name AS subordinate_name,
> subordinate.discovered
Re: [sqlalchemy] float values digits truncated
the MySQL dialects within SQLAlchemy don't touch floats at all, so this is
probably a product of the DBAPI in use. Key information here would include
specific MySQL version, storage engine, and DBAPI name / version - Python and
OS version may be important as well.
On Apr 4, 2013, at 1:57 PM, scientificSteve wrote:
> Hello,
>
> I have got a problem with inserting large float values into a mysql database
> table.
> When inserting a new EventDb using a session with large values and 6 digits,
> the float value is truncated to 5 digits. See the output of the database
> engine:
> 2013-04-04 19:54:38,245 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
> 2013-04-04 19:54:38,246 INFO sqlalchemy.engine.base.Engine INSERT INTO
> unit_test_event (ev_catalog_id, start_time, end_time, public_id,
> pref_origin_id, pref_magnitude_id, pref_focmec_id, ev_type,
> ev_type_certainty, description, comment, tags, agency_id, agency_uri, author,
> author_uri, creation_time, version) VALUES (%s, %s, %s, %s, %s, %s, %s, %s,
> %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
> 2013-04-04 19:54:38,246 INFO sqlalchemy.engine.base.Engine (None,
> 946684800.0, 946688400.0, None, None, None, None, None, None, None, None,
> None, None, None, None, None, 1365098078.232819, None)
> 2013-04-04 19:54:38,247 INFO sqlalchemy.engine.base.Engine COMMIT
>
> I have tried to enter the large values manually into the database using the
> commandline mysql client and the values haven't been truncated.
>
> Any help is appreciated.
>Stefan.
>
> P.S.:
> Here's my table definition using ORM:
>
> class EventDb(base):
> __tablename__ = 'event'
> __table_args__ = (
> UniqueConstraint('public_id'),
> {'mysql_engine': 'InnoDB'}
> )
>
> id = Column(Integer(10), primary_key = True, autoincrement = True)
> ev_catalog_id = Column(Integer(10),
> ForeignKey('event_catalog.id',
> onupdate = 'cascade',
> ondelete = 'set null'),
> nullable = True)
> start_time = Column(Float(53), nullable = False)
> end_time = Column(Float(53), nullable = False)
> public_id = Column(String(255), nullable = True)
> pref_origin_id = Column(Integer(10), nullable = True)
> pref_magnitude_id = Column(Integer(10), nullable = True)
> pref_focmec_id = Column(Integer(10), nullable = True)
> ev_type = Column(Integer(10), nullable = True)
> ev_type_certainty = Column(String(50), nullable = True)
> description = Column(Text, nullable = True)
> comment = Column(Text, nullable = True)
> tags = Column(String(255), nullable = True)
> agency_id = Column(String(64), nullable = True)
> agency_uri = Column(String(255), nullable = True)
> author = Column(String(255), nullable = True)
> author_uri = Column(String(255), nullable = True)
> creation_time = Column(Float(53), nullable = True)
> version = Column(String(30), nullable = True)
>
>
> def __init__(self, start_time, end_time, public_id, pref_origin_id,
> pref_magnitude_id, pref_focmec_id, ev_type,
> ev_type_certainty,
> agency_id, agency_uri, author, author_uri,
> creation_time, version):
> self.start_time = start_time
> self.end_time = end_time
> self.public_id = public_id
> self.pref_origin_id = pref_origin_id
> self.pref_magnitude_id = pref_magnitude_id
> self.pref_focmec_id = pref_focmec_id
> self.ev_type = ev_type
> self.ev_type_certainty = ev_type_certainty
> self.agency_id = agency_id
> self.agency_uri = agency_uri
> self.author = author
> self.author_uri = author_uri
> self.creation_time = creation_time
> self.version = version
>
>
>
> --
> 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 http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] how to prepare "get by primary key" query?
On Apr 4, 2013, at 2:03 PM, Roman Yakovenko wrote: > > In my case the performance boost was 30%. I have very few queries, but pretty > big loops, so compiling them in advance(outside of loop) gave me a > significant gain. > OK well we have the architecture in place to cache SQL expression constructs against their compiled form in the ORM on a per-mapper basis, using an LRU cache. The SQL emitted by get() is in most cases constant so this is a feature that can be added without too much difficulty. -- 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 http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] how to prepare "get by primary key" query?
On Wed, Apr 3, 2013 at 5:47 PM, Michael Bayer wrote: > > the Python DBAPI doesn't have an explicit concept of a "prepared > statement" - the idea of preparing a statement involves that a statement > handle is established on the database server, which can then be reused. > The closest the DBAPI has is the "executemany()" call, which gives the > DBAPI itself the option of using a prepared statement behind the scenes; > but this option is not appropriate for SELECT statements since > executemany() doesn't support the return of results. > This is what I was looking for. It looks like pysqlite does it internally. > On the Python SQL construction side, SQLAlchemy deals with expression > constructs that are converted to strings given a database dialect as well > as compilation options. The amount of time in Python spent for this > compilation is relatively small and has been optimized for many years to be > as fast as possible. Nevertheless, in some cases we do try to squeeze more > performance out by caching the compiled form of these queries; the ORM in > particular will cache the compiled form of insert/update/delete statements > that are used by the unit of work. > > Right now, the string form of SELECT queries generated by the ORM are not > automatically cached. It's only worthwhile to try to cache queries that > are "fixed", such as the get() query we're referring to here as well as > some of the queries used by lazyloading of relationships. The overhead of > this compilation however is fairly minimal; reddit.com uses SQLAlchemy > expression constructs for all database queries as well, and they serve well > over two billion page views a month without any caching of the expression > string. > In my case the performance boost was 30%. I have very few queries, but pretty big loops, so compiling them in advance(outside of loop) gave me a significant gain. > There's a recipe to make use of the compiled_cache in conjunction with the > Query object right now, which is at > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/BakedQuery . > Thank you! -- 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 http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] float values digits truncated
Hello,
I have got a problem with inserting large float values into a mysql
database table.
When inserting a new EventDb using a session with large values and 6
digits, the float value is truncated to 5 digits. See the output of the
database engine:
2013-04-04 19:54:38,245 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-04-04 19:54:38,246 INFO sqlalchemy.engine.base.Engine INSERT INTO
unit_test_event (ev_catalog_id, start_time, end_time, public_id,
pref_origin_id, pref_magnitude_id, pref_focmec_id, ev_type,
ev_type_certainty, description, comment, tags, agency_id, agency_uri,
author, author_uri, creation_time, version) VALUES (%s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
2013-04-04 19:54:38,246 INFO sqlalchemy.engine.base.Engine (None,
946684800.0, 946688400.0, None, None, None, None, None, None, None, None,
None, None, None, None, None, 1365098078.232819, None)
2013-04-04 19:54:38,247 INFO sqlalchemy.engine.base.Engine COMMIT
I have tried to enter the large values manually into the database using the
commandline mysql client and the values haven't been truncated.
Any help is appreciated.
Stefan.
P.S.:
Here's my table definition using ORM:
class EventDb(base):
__tablename__ = 'event'
__table_args__ = (
UniqueConstraint('public_id'),
{'mysql_engine': 'InnoDB'}
)
id = Column(Integer(10), primary_key = True, autoincrement = True)
ev_catalog_id = Column(Integer(10),
ForeignKey('event_catalog.id',
onupdate = 'cascade',
ondelete = 'set null'),
nullable = True)
start_time = Column(Float(53), nullable = False)
end_time = Column(Float(53), nullable = False)
public_id = Column(String(255), nullable = True)
pref_origin_id = Column(Integer(10), nullable = True)
pref_magnitude_id = Column(Integer(10), nullable = True)
pref_focmec_id = Column(Integer(10), nullable = True)
ev_type = Column(Integer(10), nullable = True)
ev_type_certainty = Column(String(50), nullable = True)
description = Column(Text, nullable = True)
comment = Column(Text, nullable = True)
tags = Column(String(255), nullable = True)
agency_id = Column(String(64), nullable = True)
agency_uri = Column(String(255), nullable = True)
author = Column(String(255), nullable = True)
author_uri = Column(String(255), nullable = True)
creation_time = Column(Float(53), nullable = True)
version = Column(String(30), nullable = True)
def __init__(self, start_time, end_time, public_id, pref_origin_id,
pref_magnitude_id, pref_focmec_id, ev_type,
ev_type_certainty,
agency_id, agency_uri, author, author_uri,
creation_time, version):
self.start_time = start_time
self.end_time = end_time
self.public_id = public_id
self.pref_origin_id = pref_origin_id
self.pref_magnitude_id = pref_magnitude_id
self.pref_focmec_id = pref_focmec_id
self.ev_type = ev_type
self.ev_type_certainty = ev_type_certainty
self.agency_id = agency_id
self.agency_uri = agency_uri
self.author = author
self.author_uri = author_uri
self.creation_time = creation_time
self.version = version
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
