Re: [sqlalchemy] Low performance when reflecting tables via pyodbc+mssql
On 12-02-2013 13:29, shaung wrote: On Tuesday, February 12, 2013 7:18:37 PM UTC+9, Simon King wrote: If you add echo='debug' to your create_engine call, SA will log all calls to the database and rows returned, which might give you an idea of where all the time is being spent. Thanks, Simon. I've looked through the debug log and found the reason. It turns out that the table has several foreign key constraints, and SA is inspecting all of the related tables and all the related tables to the related tables... There were 23 tables involved, which explained the long execution time. So is there anything I can do about this? I'm considering two possibilities: 1. Ignore the constraints to speed up 2. Or cache all the meta data to a disk file so no need to wait when restarting the program Either would be fine for me. Is it possible? I had a smilar problem. I had a ms sql database that another application created and I need to select data from it. There was lots of tables so I tried reflection but it was slow so I decided to use sa declarative method. But declaring all the tables again in python was too much work. I use sqlautocode to generate declerative table classes and use them in my models with some minor modifications. if the db structure does not change too often this will speed up things. -- 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] Best and easy web framwork for sqlalchemy
On 17-05-2012 14:23, Matteo Boscolo wrote: Hi All, I'm looking for a good web framework to show my sqlalchemy information on the web. I google and I found several solution like piramid,django, glashammer,flask but I' do not know exactly witch is the more simple for sqlalchemy. could you give me some advice based on your experience ? regards, Matteo I'm using turbogears and SA is the main reason form me to use tg. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] how to use substring in the where clause?
I have a query contains SUBSTRING in where clause: Select top 100 * from _Doc where SUBSTRING(_Doc.FileNameStr, 22, 1) = '2' --- This query runs on mssql but I need to use that query from sa. I could not find anything like this in SA documentatin. Here is what I tried so far: q_doc = dbsession.query(model.Doc) q_doc = q_doc.filter(model.Doc.DocID = ilk).filter(model.Doc.DocID = son) q_doc = q_doc.filter(model.Doc.FileNameStr[21] == search_str) q_doc = q_doc.order_by(model.Doc.DocID) of course, that gives errors. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] how to use substring in the where clause?
Thank you. it works now. But what is the difference between from sqlalchemy.sql.expression import func and from sqlalchemy import func the second import is what I found out while experimenting with the query and after I saw your reply, I tried your code and both works the same. On 08-03-2012 12:39, Robert Forkel wrote: sqlalchemy.sql.expression.func may work for this: from sqlalchemy.sql.expression import func ... q_doc.filter(func.substring(model.Doc.FileNameStr, 22, 1)==search_str) ... 2012/3/8 Timuçin Kızılayt...@savaskarsitlari.org: I have a query contains SUBSTRING in where clause: Select top 100 * from _Doc where SUBSTRING(_Doc.FileNameStr, 22, 1) = '2' --- This query runs on mssql but I need to use that query from sa. I could not find anything like this in SA documentatin. Here is what I tried so far: q_doc = dbsession.query(model.Doc) q_doc = q_doc.filter(model.Doc.DocID= ilk).filter(model.Doc.DocID= son) q_doc = q_doc.filter(model.Doc.FileNameStr[21] == search_str) q_doc = q_doc.order_by(model.Doc.DocID) of course, that gives errors. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://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 sqlalchemy@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.
Re: [sqlalchemy] best way of connecting to sqlserver from linux using SA?
On 05-03-2012 11:29, Chris Withers wrote:
Hi All,
What's now the best way to connect to a Microsoft SQL Server instance
from a linux box? What's the recommended driver?
cheers,
Chris
I'm using pyodbc and freetds packages to connect to existing mssql
server 2008 running on windows.
here are my configurations:
FreeTDS config:
-
tkizilay@tkizilay-linux:~$ cat /etc/freetds/freetds.conf
[TDS]
Description = FreeTDS Driver for Linux MSSQL on Win32
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
[mssqlserver]
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
host = 192.168.0.10
port = 1433
tds version = 8.0
client charset = UTF-8
---
and this is /etc/odbc.ini:
--
tkizilay@tkizilay-linux:~$ cat /etc/odbc.ini
[MSSQLDB]
Driver = FreeTDS
Description = mssql server
Trace = No
Servername = mssqlserver
convert_unicode=True
client charset = utf8
---
and this is the connection setup in python:
--
engine=create_engine('mssql+pyodbc://SQLUSER:SQLPASSWORD@MSSQLDB?Database=TEST_DB')
DeclarativeBase = declarative_base()
metadata = DeclarativeBase.metadata
metadata.bind = engine
---
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] how to get last record from a resultset
I think, reversing the sort and getting the first record will do. 20-07-2011 16:32, Krishnakant Mane yazmış: Hello all, Subject line says it all. Basically what I want to do is to get last record from a result set. I am dealing with a situation where given a date I need to know the last record pertaining to transaction on a given account. yes, it is an accounting/ book keeping software. So I thought there was some thing like .last() method for a resultset? Or even better do we have some thing like session.query(table).last() The problem is that my logic is in place but I know that performance wise it is very dirty to get the list of all records, just to loop till the end and throw away all the rest of the rows. So plese suggest how can I only get just that one (last) record? Happy hacking. Krishnakant. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Generating Raw SQL statements with parameters included
I'm using SA with turbogears 2.x framework and in development mode when
I turn on all the logging I can see the generated SA queries in the
paste web server console. And one line after the generated query, there
is a line which shows the parameters.
here is a sample output:
22:17:53,695 INFO [sqlalchemy.engine.base.Engine.0x...cad0] SELECT
adres_ilce.id AS adres_ilce_id, adres_ilce.kod AS adres_ilce_kod,
adres_ilce.ad AS adres_ilce_ad, adres_ilce.adres_il_kod AS
adres_ilce_adres_il_kod
FROM adres_ilce
WHERE adres_ilce.adres_il_kod = ? ORDER BY adres_ilce.ad
22:17:53,695 INFO [sqlalchemy.engine.base.Engine.0x...cad0] SELECT
adres_ilce.id AS adres_ilce_id, adres_ilce.kod AS adres_ilce_kod,
adres_ilce.ad AS adres_ilce_ad, adres_ilce.adres_il_kod AS
adres_ilce_adres_il_kod
FROM adres_ilce
WHERE adres_ilce.adres_il_kod = ? ORDER BY adres_ilce.ad
22:17:53,696 INFO [sqlalchemy.engine.base.Engine.0x...cad0] (u'15',)
22:17:53,696 INFO [sqlalchemy.engine.base.Engine.0x...cad0] (u'15',)
22:17:53,725 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Col
('adres_ilce_id', 'adres_ilce_kod', 'adres_ilce_ad',
'adres_ilce_adres_il_kod')
22:17:53,725 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Col
('adres_ilce_id', 'adres_ilce_kod', 'adres_ilce_ad',
'adres_ilce_adres_il_kod')
22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (190,
0, u'- - - - - - - - - - - - - - -', 15)
22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (190,
0, u'- - - - - - - - - - - - - - -', 15)
22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (193,
52, u'ALTINYAYLA(D\u0130RM\u0130L)', 15)
22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (193,
52, u'ALTINYAYLA(D\u0130RM\u0130L)', 15)
22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (192,
51, u'A\u011eLASUN', 15)
22:17:53,726 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (192,
51, u'A\u011eLASUN', 15)
22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (194,
53, u'BUCAK', 15)
22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (194,
53, u'BUCAK', 15)
22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (191,
1, u'BURDUR', 15)
22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (191,
1, u'BURDUR', 15)
22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (197,
56, u'G\xd6LH\u0130SAR', 15)
22:17:53,727 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (197,
56, u'G\xd6LH\u0130SAR', 15)
22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (198,
57, u'KARAMANLI', 15)
22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (198,
57, u'KARAMANLI', 15)
22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (199,
59, u'TEFENN\u0130', 15)
22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (199,
59, u'TEFENN\u0130', 15)
22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (200,
60, u'YE\u015e\u0130LOVA', 15)
22:17:53,728 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (200,
60, u'YE\u015e\u0130LOVA', 15)
22:17:53,729 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (195,
54, u'\xc7AVDIR', 15)
22:17:53,729 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (195,
54, u'\xc7AVDIR', 15)
22:17:53,729 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (196,
55, u'\xc7ELT\u0130K\xc7\u0130', 15)
22:17:53,729 DEBUG [sqlalchemy.engine.base.Engine.0x...cad0] Row (196,
55, u'\xc7ELT\u0130K\xc7\u0130', 15)
-
The line just after the generated query shows the parameters:
(u'15',) is the parameter in my sample log output.
Maybe you have to turn on debug output in your application to show query
result too. I've done something in tg logging config so my logs shows
every sa query and results two times.
11-01-2011 19:12, Harkirat yazmış:
Thank you! I understand the security concerns. I only need this for
testing purposes and production will be all bind-parameter driven.
On Jan 11, 11:15 am, Michael Bayermike...@zzzcomputing.com wrote:
This question comes up from time to time and I'm generally extremely
uncomfortable documenting it, as SQLAlchemy carefully protects its reputation
as being 100% bind-parameter driven and in no way wants to encourage the
rendering of data directly into SQL strings.This is the one real area of
SQL where security is a concern.
However, I've added a wikipage for this use case which has a level of
disclaimer I think will be OK, which you can see
athttp://www.sqlalchemy.org/trac/wiki/UsageRecipes/BindsAsStrings.
On Jan 11, 2011, at 10:14 AM, Harkirat wrote:
Hi All,
When I run this
delete_stmt = appname.delete(appname.c.appid==1)
print delete_stmt
I get output
DELETE FROM appname WHERE appname.appid = ?
Is there any way I can print out raw sql statments with the parameters
included e.g.
DELETE FROM appname WHERE appname.appid = 1
Thanks!
Harkirat
--
[sqlalchemy] SA query help.
Hi, I'm trying to rewrite an application using Turbogears 2.x framework. There is a query in that application, which works fine on ms-sql server: here is the query: - select Firma_ID = Firma.ID, Firma_Kod = Firma.Kod, Firma_Ad = Firma.Unvan, Nakliye_Tutar = Sum(Sv.Nakliye_Tutar), Komisyon_Tutar= Sum(Sv.Komisyon_Tutar) from (Select Sevkiyat.ID, Sevkiyat.Tarih, Sevkiyat.Arac_ID, Sevkiyat.CH_Firma_ID, Siparis.Odeme_Turu, Siparis.Musteri_Fiyati, Siparis.Miktar, Musteri_Tutar = Siparis.Miktar * Siparis.Musteri_Fiyati, Sevkiyat.Nakliye_Bedeli, Sevkiyat.Nak_Miktar, Nakliye_Tutar = case when Siparis.Odeme_Turu = 0 then Sevkiyat.Nak_Miktar * Sevkiyat.Nakliye_Bedeli else 0 end, Komisyon_Tutar = case when Siparis.Odeme_Turu = 1 then (Siparis.Musteri_Fiyati * Siparis.Miktar) - (Sevkiyat.Nak_Miktar * Sevkiyat.Nakliye_Bedeli) else 0 end from Sevkiyat left outer join Siparis on Siparis.ID = Sevkiyat.Siparis_ID Where Sevkiyat.Nakliye_Odendi = 0 and Sevkiyat.CH_Firma_ID 0) as Sv left outer join Firma on Firma.ID = Sv.CH_Firma_ID where Sv.Nakliye_Tutar 0 or Sv.Komisyon_Tutar 0 group by Firma.ID, Firma.Kod, Firma.Unvan order by Firma_Kod --- How can I make this query in python using sqlalchemy? I've declared the tables using declarative_base and getting results from queries but some compleks queries like this, I'm lost. Please tell me some hints and keywords to search on google to find a sulution. -- 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] [Fwd: Re: [TurboGears] strange sql query problem.]
I have a strange problem in sa. I've asked turbogears mailinglist and someone there suggested to post it here too. I've forwarded the post from there but here is an example of the problem. example code: prc_filter=24 q_sometable=DBSession.query(model.Sometable) q_sometable=q_sometable.filter((model.Sometable.qty*model.Sometable.price)==prc_filter) -- I'm trying to filter a query by two colums multiplied. I think this is a common filtering operation. I'm using turbogears 2.1b2 and sqlite. And some other person from TG mailinglist told me that this problem is sqlite only, he had the same problem too and it was solved when he switched to mysql. And this one works. When I send the filter as string like this: f = u'sometable.qty * sometable.price = 24' q_sometable = q_sometable.filter(f) -- Orijinal Mesaj Konu: Re: [TurboGears] strange sql query problem. Tarih: Thu, 22 Jul 2010 12:34:00 +0200 Kimden: Diez B. Roggisch de...@web.de Cevapla: turboge...@googlegroups.com Kime: turboge...@googlegroups.com Tercihler: 4c4813a1.7040...@savaskarsitlari.org 201007221207.48136.de...@web.de 4c481c75.4090...@savaskarsitlari.org On Thursday, July 22, 2010 12:24:53 Timuçin Kızılay wrote: Diez B. Roggisch yazmış: On Thursday, July 22, 2010 11:47:13 Timuçin Kızılay wrote: I'm trying to make a query like this: prc_filter = 24 q_sometable = DBSession.query(model.Sometable) q_sometable = q_sometable.filter((model.Sometable.qty * model.Sometable.price)==prc_filter) This do not give any error and when I look at the paster log I see that it generated a query with where part like this: WHERE sometable.qty * sometable.price = ? 12:41:24,272 INFO [sqlalchemy.engine.base.Engine.0x...ba0c] ['24'] This looks as if the above is *not* what you really do. Because '24' is a *string*, not an integer... Diez No, it's an integer but the log shows it as string. I think the problem is filtering on two columns multiplied. When I filter only one column, it works. q_sometable = q_sometable.filter(model.Sometable.price==prc_filter) this filter works and generated sql shown on the log is still ['24'] but it works. Ah. Strange enough. I think it's a SA bug, it try to put those values into query as parameters. If so, I suggest you take it to the SA-mailinglist. Michael Bayer is incredible responsive. Of course you should try with the latest + greatest SA first. Diez -- You received this message because you are subscribed to the Google Groups TurboGears group. To post to this group, send email to turboge...@googlegroups.com. To unsubscribe from this group, send email to turbogears+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/turbogears?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.
Re: [sqlalchemy] SQL Alchemy for bigger enterprise application
Dan @ Austria yazmış: Hi, i m a developer with some experience in python and django - a framework i realy like. However, i just read the sqlalchemy docu and i think the orm seems to be really professional. Is sqlalchemy the most prominent orm for python? Is it suitable to write larger enterprise applications? Are there any drawbacks to sqlalchemy? Are there any other big orm projects for python, which one should have a look at? Sorry for asking this kind of general questions. But im looking for some decission hints ... Dan I'm a developer working on web based database applications for use in corporate environment. I have some experience on django too. I'm now currently useing Turbogears 2 framework and TG drop sqlobject orm for sa on tg version 2.x. sqlalchemy is one of the biggest reasons for me to use turbogears2 and stop using django. Maybe this'll give you a hint. -- 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] looking for something like sql views.
I'm using sqlalchemy with turbogears framework. I have some tables and some complex queries with lots of joins and filters. is tehere a simple way to make those queries like views and use those views in other more complex queries like they are tables? -- 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.
