0

my code is:

engine = sqlalchemy.create_engine("credentials")
connection = engine.connect()

day = 180601
query = "select * from Data where Name like '%{0}%'".format(str(day))
df = pd.DataFrame(connection.execute(query).fetchall())

but this doesn't work. when i type:

query =  "select * from Data where Name like '%180601%'"

then repeat the same process, this works fine although they produce same queries. i want to change the days in a for loop.

Any ideas would be appreciated. Thanks in advance!

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
tepeal
  • 1
  • 1
  • If you put the contents of the query all in the same line, does it work? I say this because in the first "%" you have a line break... – João Martins Jul 24 '18 at 14:01
  • I've not really used python (and when i did it was about a year ago as an introduction), but, if I recall correctly, don't you need to triple quote (`"""`) a multiline string in Python? – Thom A Jul 24 '18 at 14:01
  • hi Joao, actually they are in the same line in my file. – tepeal Jul 24 '18 at 14:03
  • @tepeal then make sure your post looks exactly like what your file does. I've edited your post to remove the line-break, if your saying it's not actually there in your code. – Thom A Jul 24 '18 at 14:11
  • @Larnu thanks. i am quite new here. – tepeal Jul 24 '18 at 14:15
  • Please note that this is a **bad idea** in general. You should not use string formatting for passing values to queries. If you make a habit out of it, you'll find yourself at the wrong end of an SQL injection some day. Usually using proper placeholders is also much easier, since the driver handles quoting etc. for you. – Ilja Everilä Jul 24 '18 at 14:39
  • Here's an example of how you should be passing the search string: https://stackoverflow.com/questions/39488380/pandas-read-sql-with-parameters-and-wildcard-operator – Ilja Everilä Jul 24 '18 at 14:46

1 Answers1

0

I discovered there is no problem with the python itself. The problem is sql cannot get top 100 of the rows without order by clause. I think this is another bug but adding "order by col1" at the end of query solved the problem.

Thanks for your time.

tepeal
  • 1
  • 1
  • @tepeal this all appears very unusual to me. For the 'order by' issue: what do you see when you remove the ORDER BY CLAUSE? Is there a python exception or do you you just get an empty result set returned? Could you please initialize the engine with echo=True and compare the produced sql statements? – Pablo Henkowski Jul 24 '18 at 19:04
  • @PabloHenkowski i try to get around 200k rows from a 5m row sized table. Without order by clause, sql tries to execute the query but it takes very long time and i interrupt. I think sql can't decide which top 100 rows to show. :) When i add order by clause, it immediately returns the result. This sounds weird to me also but this is the case. – tepeal Jul 24 '18 at 20:19