In [1]:
from db_interface import Person, Contribution, Campaign, get_contribution
from sqlalchemy import Column, Integer, String, Date, create_engine, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref, joinedload
2014-06-23 19:32:41,305 INFO sqlalchemy.engine.base.Engine SELECT user_name()
INFO:sqlalchemy.engine.base.Engine:SELECT user_name()
2014-06-23 19:32:41,305 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
2014-06-23 19:32:41,377 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
INFO:sqlalchemy.engine.base.Engine:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2014-06-23 19:32:41,377 INFO sqlalchemy.engine.base.Engine (u'pilotfish',)
INFO:sqlalchemy.engine.base.Engine:(u'pilotfish',)
2014-06-23 19:32:41,648 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-23 19:32:41,648 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
2014-06-23 19:32:41,720 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2014-06-23 19:32:41,721 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
In [2]:
engine=create_engine('mssql+pyodbc://pilotfish:setinstone@pilotfishdb.c5zdfsvfmy5u.us-west-2.rds.amazonaws.com:1433/FishBase', echo=True)
engine.connect()
Base = declarative_base()
Session = sessionmaker(bind = engine)
session = Session()
2014-06-23 19:32:42,296 INFO sqlalchemy.engine.base.Engine SELECT user_name()
INFO:sqlalchemy.engine.base.Engine:SELECT user_name()
2014-06-23 19:32:42,296 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
2014-06-23 19:32:42,476 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
INFO:sqlalchemy.engine.base.Engine:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2014-06-23 19:32:42,477 INFO sqlalchemy.engine.base.Engine (u'pilotfish',)
INFO:sqlalchemy.engine.base.Engine:(u'pilotfish',)
2014-06-23 19:32:42,628 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-06-23 19:32:42,628 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
2014-06-23 19:32:42,709 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2014-06-23 19:32:42,709 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
In [19]:
if(session):
session.close()
session = Session()
In [4]:
contributions = session.query(Contribution).options(joinedload(Contribution.Contributor), joinedload(Contribution.ContributionTarget))
In [20]:
for con in contributions.filter(Contribution.ContributorID == 10).all():
print con.Contributor.FirstName
2014-06-23 20:19:57,871 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2014-06-23 20:19:57,874 INFO sqlalchemy.engine.base.Engine SELECT [Contributions].[ContributionID] AS [Contributions_ContributionID], [Contributions].[ContributorID] AS [Contributions_ContributorID], [Contributions].[CampaignName] AS [Contributions_CampaignName], [Contributions].[Contribution] AS [Contributions_Contribution], [Persons_1].[PersonID] AS [Persons_1_PersonID], [Persons_1].[FirstName] AS [Persons_1_FirstName], [Persons_1].[LastName] AS [Persons_1_LastName], [Persons_1].[Password] AS [Persons_1_Password], [Persons_1].[Department] AS [Persons_1_Department], [Persons_1].[PhoneNumber] AS [Persons_1_PhoneNumber], [Persons_1].[Email] AS [Persons_1_Email], [Campaigns_1].[CampaignTitle] AS [Campaigns_1_CampaignTitle], [Campaigns_1].[ShortDesc] AS [Campaigns_1_ShortDesc], [Campaigns_1].[DatePosted] AS [Campaigns_1_DatePosted], [Campaigns_1].[Creator] AS [Campaigns_1_Creator]
FROM [Contributions] LEFT OUTER JOIN [Persons] AS [Persons_1] ON [Contributions].[ContributorID] = [Persons_1].[PersonID] LEFT OUTER JOIN [Campaigns] AS [Campaigns_1] ON [Contributions].[CampaignName] = [Campaigns_1].[CampaignTitle]
WHERE [Contributions].[ContributorID] = ?
INFO:sqlalchemy.engine.base.Engine:SELECT [Contributions].[ContributionID] AS [Contributions_ContributionID], [Contributions].[ContributorID] AS [Contributions_ContributorID], [Contributions].[CampaignName] AS [Contributions_CampaignName], [Contributions].[Contribution] AS [Contributions_Contribution], [Persons_1].[PersonID] AS [Persons_1_PersonID], [Persons_1].[FirstName] AS [Persons_1_FirstName], [Persons_1].[LastName] AS [Persons_1_LastName], [Persons_1].[Password] AS [Persons_1_Password], [Persons_1].[Department] AS [Persons_1_Department], [Persons_1].[PhoneNumber] AS [Persons_1_PhoneNumber], [Persons_1].[Email] AS [Persons_1_Email], [Campaigns_1].[CampaignTitle] AS [Campaigns_1_CampaignTitle], [Campaigns_1].[ShortDesc] AS [Campaigns_1_ShortDesc], [Campaigns_1].[DatePosted] AS [Campaigns_1_DatePosted], [Campaigns_1].[Creator] AS [Campaigns_1_Creator]
FROM [Contributions] LEFT OUTER JOIN [Persons] AS [Persons_1] ON [Contributions].[ContributorID] = [Persons_1].[PersonID] LEFT OUTER JOIN [Campaigns] AS [Campaigns_1] ON [Contributions].[CampaignName] = [Campaigns_1].[CampaignTitle]
WHERE [Contributions].[ContributorID] = ?
2014-06-23 20:19:57,875 INFO sqlalchemy.engine.base.Engine (10,)
INFO:sqlalchemy.engine.base.Engine:(10,)
Mandeep
Mandeep
Mandeep
In [36]:
from sqlalchemy import func
query = session.query(Contribution, func.sum(Contribution.Contribution)).options(joinedload(Contribution.ContributionTarget)).group_by(Contribution).all()
2014-06-23 21:18:02,023 INFO sqlalchemy.engine.base.Engine SELECT [Contributions].[ContributionID] AS [Contributions_ContributionID], [Contributions].[ContributorID] AS [Contributions_ContributorID], [Contributions].[CampaignName] AS [Contributions_CampaignName], [Contributions].[Contribution] AS [Contributions_Contribution], sum([Contributions].[CampaignName] = [Campaigns].[CampaignTitle]) AS sum_1, [Campaigns_1].[CampaignTitle] AS [Campaigns_1_CampaignTitle], [Campaigns_1].[ShortDesc] AS [Campaigns_1_ShortDesc], [Campaigns_1].[DatePosted] AS [Campaigns_1_DatePosted], [Campaigns_1].[Creator] AS [Campaigns_1_Creator]
FROM [Campaigns], [Contributions] LEFT OUTER JOIN [Campaigns] AS [Campaigns_1] ON [Contributions].[CampaignName] = [Campaigns_1].[CampaignTitle] GROUP BY [Contributions].[ContributionID], [Contributions].[ContributorID], [Contributions].[CampaignName], [Contributions].[Contribution]
INFO:sqlalchemy.engine.base.Engine:SELECT [Contributions].[ContributionID] AS [Contributions_ContributionID], [Contributions].[ContributorID] AS [Contributions_ContributorID], [Contributions].[CampaignName] AS [Contributions_CampaignName], [Contributions].[Contribution] AS [Contributions_Contribution], sum([Contributions].[CampaignName] = [Campaigns].[CampaignTitle]) AS sum_1, [Campaigns_1].[CampaignTitle] AS [Campaigns_1_CampaignTitle], [Campaigns_1].[ShortDesc] AS [Campaigns_1_ShortDesc], [Campaigns_1].[DatePosted] AS [Campaigns_1_DatePosted], [Campaigns_1].[Creator] AS [Campaigns_1_Creator]
FROM [Campaigns], [Contributions] LEFT OUTER JOIN [Campaigns] AS [Campaigns_1] ON [Contributions].[CampaignName] = [Campaigns_1].[CampaignTitle] GROUP BY [Contributions].[ContributionID], [Contributions].[ContributorID], [Contributions].[CampaignName], [Contributions].[Contribution]
2014-06-23 21:18:02,023 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
---------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
<ipython-input-36-387a972c4fbf> in <module>()
1 from sqlalchemy import func
----> 2 query = session.query(Contribution, func.sum(Contribution.ContributionTarget)).options(joinedload(Contribution.ContributionTarget)).group_by(Contribution).all()
C:\Python27\lib\site-packages\sqlalchemy\orm\query.pyc in all(self)
2290
2291 """
-> 2292 return list(self)
2293
2294 @_generative(_no_clauseelement_condition)
C:\Python27\lib\site-packages\sqlalchemy\orm\query.pyc in __iter__(self)
2402 if self._autoflush and not self._populate_existing:
2403 self.session._autoflush()
-> 2404 return self._execute_and_instances(context)
2405
2406 def _connection_from_session(self, **kw):
C:\Python27\lib\site-packages\sqlalchemy\orm\query.pyc in _execute_and_instances(self, querycontext)
2417 close_with_result=True)
2418
-> 2419 result = conn.execute(querycontext.statement, self._params)
2420 return loading.instances(self, result, querycontext)
2421
C:\Python27\lib\site-packages\sqlalchemy\engine\base.pyc in execute(self, object, *multiparams, **params)
718 type(object))
719 else:
--> 720 return meth(self, multiparams, params)
721
722 def _execute_function(self, func, multiparams, params):
C:\Python27\lib\site-packages\sqlalchemy\sql\elements.pyc in _execute_on_connection(self, connection, multiparams, params)
315
316 def _execute_on_connection(self, connection, multiparams, params):
--> 317 return connection._execute_clauseelement(self, multiparams, params)
318
319 def unique_params(self, *optionaldict, **kwargs):
C:\Python27\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_clauseelement(self, elem, multiparams, params)
815 compiled_sql,
816 distilled_params,
--> 817 compiled_sql, distilled_params
818 )
819 if self._has_events or self.engine._has_events:
C:\Python27\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
945 parameters,
946 cursor,
--> 947 context)
948
949 if self._has_events or self.engine._has_events:
C:\Python27\lib\site-packages\sqlalchemy\engine\base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
1106 self.dialect.dbapi.Error,
1107 connection_invalidated=self._is_disconnect),
-> 1108 exc_info
1109 )
1110
C:\Python27\lib\site-packages\sqlalchemy\util\compat.pyc in raise_from_cause(exception, exc_info)
183 exc_info = sys.exc_info()
184 exc_type, exc_value, exc_tb = exc_info
--> 185 reraise(type(exception), exception, tb=exc_tb)
186
187 if py3k:
C:\Python27\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
938 statement,
939 parameters,
--> 940 context)
941 except Exception as e:
942 self._handle_dbapi_exception(
C:\Python27\lib\site-packages\sqlalchemy\engine\default.pyc in do_execute(self, cursor, statement, parameters, context)
433
434 def do_execute(self, cursor, statement, parameters, context=None):
--> 435 cursor.execute(statement, parameters)
436
437 def do_execute_no_params(self, cursor, statement, context=None):
ProgrammingError: (ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'. (102) (SQLExecDirectW)") u'SELECT [Contributions].[ContributionID] AS [Contributions_ContributionID], [Contributions].[ContributorID] AS [Contributions_ContributorID], [Contributions].[CampaignName] AS [Contributions_CampaignName], [Contributions].[Contribution] AS [Contributions_Contribution], sum([Contributions].[CampaignName] = [Campaigns].[CampaignTitle]) AS sum_1, [Campaigns_1].[CampaignTitle] AS [Campaigns_1_CampaignTitle], [Campaigns_1].[ShortDesc] AS [Campaigns_1_ShortDesc], [Campaigns_1].[DatePosted] AS [Campaigns_1_DatePosted], [Campaigns_1].[Creator] AS [Campaigns_1_Creator] \nFROM [Campaigns], [Contributions] LEFT OUTER JOIN [Campaigns] AS [Campaigns_1] ON [Contributions].[CampaignName] = [Campaigns_1].[CampaignTitle] GROUP BY [Contributions].[ContributionID], [Contributions].[ContributorID], [Contributions].[CampaignName], [Contributions].[Contribution]' ()
In [34]:
for instance in query:
print instance.Contribution
<db_interface.Contribution object at 0x00000000051035F8>
<db_interface.Contribution object at 0x000000000503A4E0>
<db_interface.Contribution object at 0x00000000050D9EF0>
<db_interface.Contribution object at 0x00000000050E8E48>
<db_interface.Contribution object at 0x000000000503A860>
<db_interface.Contribution object at 0x000000000511E2E8>
<db_interface.Contribution object at 0x00000000050D9E48>
<db_interface.Contribution object at 0x000000000503A550>
<db_interface.Contribution object at 0x00000000050D9080>
<db_interface.Contribution object at 0x00000000050D9630>
<db_interface.Contribution object at 0x000000000503AB38>
<db_interface.Contribution object at 0x00000000050D9E80>
<db_interface.Contribution object at 0x000000000503AF28>
<db_interface.Contribution object at 0x00000000050D90F0>
<db_interface.Contribution object at 0x00000000050D9940>
<db_interface.Contribution object at 0x000000000511E550>
<db_interface.Contribution object at 0x00000000050D9908>
<db_interface.Contribution object at 0x000000000503AFD0>
<db_interface.Contribution object at 0x00000000050D9470>
<db_interface.Contribution object at 0x00000000050D97B8>
<db_interface.Contribution object at 0x000000000503A400>
<db_interface.Contribution object at 0x000000000503A080>
Content source: nugtrio/pilot_fish
Similar notebooks: