Re: [Tutor] SQLite Django
Forwarding to list. Please use REplyAll when responding to tutor messages. On 04/08/16 09:19, Trevor H wrote: > Hi Alan, > > Thank you for the reply. I'll try show the graphic as a website. Would > I have to make the data entries as a model in Django? > I'm not a Django expert so don;t know where you would put the code to generate the image. But based on usual MVC practice I'd assume it would be a model somewhere. However the image itself will just be a file on the server and would be stored in the same place as any other images such as logos etc. The key difference is that the image filename will need to be injected into your HTML template somehow and I assume you do that by linking a model variable to a marker in the template. Again I'm no Django expert I've only watched a few YouTube tutorials... > I'm new to this sorry for the newbie question. Newbie questions are what we are here for. Although Django specific issues might be better addressed to the Django list. But we will usually have a pop at them if they are not too Django specific. > I have to link all my data like so to give a plot. Just don't know how. > > SQLi command to join my tables: > To join all tables: > SELECT devices.id AS id, > macs.address AS mac, > oses.name AS os, > browsers.name AS browser > FROM devices > JOIN macs ON devices.mac = macs.id > JOIN oses ON devices.os = oses.id > JOIN browsers ON devices.browser = browsers.id; > Getting the data and generating the plot are two separate issues. You can experiment with the SQL using the SQLIte interpreter till you have a query that delivers what you need. If you don't already have it installed I strongly recommend doing so. Without knowing what you're trying to plot and what your schema looks like I can't really comment on the SQL -- Alan G Author of the Learn to Program web site http://www.alan-g.me.uk/ http://www.amazon.com/author/alan_gauld Follow my photo-blog on Flickr at: http://www.flickr.com/photos/alangauldphotos ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite
On 19/05/16 10:03, Crusier wrote:
> c.execute('''CREATE TABLE stocks
> (code text)''')
>
> # Insert a row of data
> List = ['1', '2', '3', '4', '5', '6', '7',
> '8', '9', '00010', '00011', '00012']
>
> c.executemany('INSERT INTO stocks VALUES (?)', List)
Peter has already given you one answer, I'll repeat it in a slightly
different form. Between the two of us you will hopefully
understand... :-)
the SQL statement
INSERT INTO stocks VALUES (?)
Has a placemarker (?) that execute() or executemany()expect
a sequence to fill. This is more obvious if you had more
than one variable:
INSERT INTO stocks VALUES (?, ?)
Here it would expect a sequence of two values.
But although you only have one value, execute() still
expects a sequence, but one that contains a single value.
You are providing strings which are sequences of 5 characters,
so exec tries to process the 5 characters but has only 1
placemarker so it fails. So you need to pass a sequence
(usually a tuple) of one value like:
('12345',) # note the comma at the end.
execmany() is exactly the same but expects a sequence of
sequences. So you need your list to contain tuples as above
stock_codes = [('12345',), ('23456',), ...]
HTH
--
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite
Crusier wrote:
> Dear Alan,
>
> I have read your web page and try to test thing out on SQLite.
>
> Attached is my code:
>
> import sqlite3
> conn = sqlite3.connect('example1.db')
> c = conn.cursor()
> c.execute('drop table if exists stocks')
> c.execute('''CREATE TABLE stocks
> (code text)''')
>
> # Insert a row of data
> List = ['1', '2', '3', '4', '5', '6', '7',
> '8', '9', '00010', '00011', '00012']
List is a bad name; use something related to the problem domain, e. g
stocks.
>
> c.executemany('INSERT INTO stocks VALUES (?)', List)
>
> # Save (commit) the changes
> conn.commit()
>
> # We can also close the connection if we are done with it.
> # Just be sure any changes have been committed or they will be lost.
> conn.close()
>
> The following error has came out
> sqlite3.ProgrammingError: Incorrect number of bindings supplied. The
> current statement uses 1, and there are 5 supplied.
>
> Please advise.
The List argument is interpreted as a sequence of records and thus what you
meant as a single value, e. g. "1" as a sequence of fields, i. e. every
character counts as a separate value.
To fix the problem you can either change the list to a list of tuples or
lists
List = [['1'], ['2'], ['3'], ...]
or add a zip() call in the line
c.executemany('INSERT INTO stocks VALUES (?)', zip(List))
which has the same effect:
>>> list(zip(["foo", "bar", "baz"]))
[('foo',), ('bar',), ('baz',)]
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite
On Tue, 3 May 2016, Crusier wrote: I am just wondering if there is any good reference which I can learn how to program SQLITE using Python I can not find any book is correlated to Sqlite using Python. "The Definitive Guide to SQLite" is about SQLite, but includes a chapter on both PySQLite and APSW for Python access. One of the book co-authors, Michael Owens, is the original author of PySQLite. ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite
Alan Gauld via Tutor wrote:
> On 13/05/16 21:25, Neil D. Cerutti wrote:
>
>> From your tutorial:
>>
>> query = '''INSERT INTO Address
>> (First,Last,House,Street,District,Town,PostCode,Phone)
>> Values ("%s","%s","%s","%s","%s","%s","%s","%s")''' %\
>> (first, last, house, street, district, town, code, phone)
>>
>> I am not an expert on SQLite, but that doesn't appear to be a wise way
>> to call SQL from Python. Are the double-quotes enough to protect you
>> from malicious data?
>
> No, and if you carry on reading you will find:
>
> --
> A Word about Security
>
> While the code above works and demonstrates how to call SQL from Python
> it does have one significant flaw. Because I used string formatting to
> construct the queries it is possible for a malicious user to enter some
> rogue SQL code as input. This rogue code then gets inserted into the
> query using the format string and is executed, potentially deleting
> vital data. To avoid that, the execute() API call has an extra trick up
> its sleeve
>
> -
I have to say it: giving a newbie a bad idea plus broken example code -- and
then follow up with a warning will hardly ever work out the way you'd hope.
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite
On 13/05/16 21:25, Neil D. Cerutti wrote:
> From your tutorial:
>
> query = '''INSERT INTO Address
> (First,Last,House,Street,District,Town,PostCode,Phone)
> Values ("%s","%s","%s","%s","%s","%s","%s","%s")''' %\
> (first, last, house, street, district, town, code, phone)
>
> I am not an expert on SQLite, but that doesn't appear to be a wise way
> to call SQL from Python. Are the double-quotes enough to protect you
> from malicious data?
No, and if you carry on reading you will find:
--
A Word about Security
While the code above works and demonstrates how to call SQL from Python
it does have one significant flaw. Because I used string formatting to
construct the queries it is possible for a malicious user to enter some
rogue SQL code as input. This rogue code then gets inserted into the
query using the format string and is executed, potentially deleting
vital data. To avoid that, the execute() API call has an extra trick up
its sleeve
-
--
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite
On 5/3/2016 11:40 AM, Alan Gauld via Tutor wrote:
On 03/05/16 10:09, Crusier wrote:
I am just wondering if there is any good reference which I can learn how to
program SQLITE using Python
I can not find any book is correlated to Sqlite using Python.
You can try my tutorial below.
http://www.alan-g.me.uk/tutor/tutdbms.htm
If you want very similar information in book form then
our book 'Python Projects' contains a chapter on databases,
half of which is SQLite based.
If you want a good book on SQLite itself I can recommend:
Using SQLIte by Kreibich.
From your tutorial:
query = '''INSERT INTO Address
(First,Last,House,Street,District,Town,PostCode,Phone)
Values ("%s","%s","%s","%s","%s","%s","%s","%s")''' %\
(first, last, house, street, district, town, code, phone)
I am not an expert on SQLite, but that doesn't appear to be a wise way
to call SQL from Python. Are the double-quotes enough to protect you
from malicious data?
--
Neil Cerutti
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite
On 03/05/16 10:09, Crusier wrote: > I am just wondering if there is any good reference which I can learn how to > program SQLITE using Python > > I can not find any book is correlated to Sqlite using Python. You can try my tutorial below. http://www.alan-g.me.uk/tutor/tutdbms.htm If you want very similar information in book form then our book 'Python Projects' contains a chapter on databases, half of which is SQLite based. If you want a good book on SQLite itself I can recommend: Using SQLIte by Kreibich. hth -- Alan G Author of the Learn to Program web site http://www.alan-g.me.uk/ http://www.amazon.com/author/alan_gauld Follow my photo-blog on Flickr at: http://www.flickr.com/photos/alangauldphotos ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite, Python and SQL injection attacks
On 14/08/15 19:40, boB Stepp wrote: Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method... This is not a Sqlite issue its true of any database. I have to be honest -- I would have fallen into this potential trap Me too, the first time I used a SQL database. But it didn't take long before a more enlightened colleague advised me of my ignorance! :-) I had not read this. It is not clear to me yet how the recommendation avoids this issue. Does the placeholder enforce some sort of type checking so that arbitrary SQL strings will be rejected? Yes, it parses the inputs to detect potential issues, such as rogue semi colons etc. Having seen this example, are there any other security surprises that I need to avoid by adopting certain coding techniques when I am using Python with SQLite? As I say, it's not just SQLite, its any database. And the same is true of handling URLs etc you should always use library parsing and escaping routines to build them. Especially when inserting data from users or received data files. hth -- Alan G Author of the Learn to Program web site http://www.alan-g.me.uk/ http://www.amazon.com/author/alan_gauld Follow my photo-blog on Flickr at: http://www.flickr.com/photos/alangauldphotos ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite, Python and SQL injection attacks
On 8/14/2015 11:40 AM, boB Stepp wrote: I was just looking at the sqlite3 docs at https://docs.python.org/3/library/sqlite3.html?highlight=sqlite#module-sqlite3 and found the following cheery news: Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack ... See http://bobby-tables.com/ for more info. Emile ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite, Python and SQL injection attacks
On 14Aug2015 13:40, boB Stepp robertvst...@gmail.com wrote: I was just looking at the sqlite3 docs at https://docs.python.org/3/library/sqlite3.html?highlight=sqlite#module-sqlite3 and found the following cheery news: Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack ... There followed this recommendation: Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method... I have to be honest -- I would have fallen into this potential trap if I had not read this. It is not clear to me yet how the recommendation avoids this issue. Does the placeholder enforce some sort of type checking so that arbitrary SQL strings will be rejected? Well, better to say that it transcribes the values correctly, possibly with some type checking. You run the same risk constructing shell command lines too, which is why shell=True is generally discourages with subprocess.Popen. So if you have: SELECT FROM tablename WHERE columnvalue = ? and you have it a python string like foo;bah, the SQL API will take care of quoting the string so that the ; is inside the quotes. Likewise if the string contains SQL end of string markers (quotes). And if the value cannot be transcribed the API should raise an exception. IN this way you know that the structure of the query has been preserved correctly. _And_ you do not need to worry about quoting values (or otherwise transcribing them) correctly; that is a solved and debugged problem. You code is simpler and robust. Cheers, Cameron Simpson c...@zip.com.au The Fano Factor, where theory meets reality. ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite question
I think you are missing the fetch call. The cursor only executed your
query, but hasn't fetched any thing out.
On Tue, Jun 11, 2013 at 12:20 PM, Khalid Al-Ghamdi emailkg...@gmail.comwrote:
Hi,
I have a dictionary with keys as employee badges and values as their
names. Both key and value are strings.
I want to read the badges from a sql select and use that to look up the
names in the dictionary. But since the result is a tuple, it doesnt' work.
how can i overcome this?
1. for data in cur.execute('select badge from sched'):
2. r_data()[data]
3.
4.
5. Traceback (most recent call last):
6. File pyshell#19, line 2, in module
7. r_data()[data]
8. KeyError: (80385,)
PS: the r_data () above is just a func that returns the before
mentioned dictionary. Here is r_data().
1. def r_data():
2. d={}
3. with open('data.csv') as f:
4. reader = csv.reader(f)
5. for sn, badge, name, grp, major, track, stage, tc,
subject, course in reader:
6. d[badge]=name
7. return d
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
--
Todd Matsumoto
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite search
Alan Gauld wrote: On 18/01/13 18:11, Roger wrote: At the moment this works to search for everything beginning with A sql = SELECT * FROM plants WHERE genus LIKE 'A%'; cursor.execute(sql); SQLlite supports a form of format string where you put in some magic charactrs then provide arguments which SQLLite will substitute in your SQL statement. You can see examples of that in the database topic in my tutorial: file:///home/alang/Documents/HomePage/tutor/tutdbms.htm Look at the address book example near the end for the 'Find Entry' feature, and definitely read the 'word about security' subheading for the correct way to do it! Just to clarify, the '%' should go in the parameter string, not as part of the query string. The query string should retain the '?' (without any quotations). ~Ramit This email is confidential and subject to important disclaimers and conditions including on offers for the purchase or sale of securities, accuracy and completeness of information, viruses, confidentiality, legal privilege, and legal entity disclaimers, available at http://www.jpmorgan.com/pages/disclosures/email. ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite search
On 18/01/13 18:11, Roger wrote: At the moment this works to search for everything beginning with A sql = SELECT * FROM plants WHERE genus LIKE 'A%'; cursor.execute(sql); SQLlite supports a form of format string where you put in some magic charactrs then provide arguments which SQLLite will substitute in your SQL statement. You can see examples of that in the database topic in my tutorial: file:///home/alang/Documents/HomePage/tutor/tutdbms.htm Look at the address book example near the end for the 'Find Entry' feature, and definitely read the 'word about security' subheading for the correct way to do it! hth -- Alan G Author of the Learn to Program web site http://www.alan-g.me.uk/ ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite search
On 27 January 2013 18:21, Alan Gauld alan.ga...@btinternet.com wrote: On 18/01/13 18:11, Roger wrote: At the moment this works to search for everything beginning with A sql = SELECT * FROM plants WHERE genus LIKE 'A%'; cursor.execute(sql); SQLlite supports a form of format string where you put in some magic charactrs then provide arguments which SQLLite will substitute in your SQL statement. You can see examples of that in the database topic in my tutorial: file:///home/alang/Documents/HomePage/tutor/tutdbms.htm You might have better luck using this link: http://www.alan-g.me.uk/tutor/tutdbms.htm Look at the address book example near the end for the 'Find Entry' feature, and definitely read the 'word about security' subheading for the correct way to do it! Oscar ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite search
file:///home/alang/Documents/HomePage/tutor/tutdbms.htm You might have better luck using this link: http://www.alan-g.me.uk/tutor/tutdbms.htm Oops, thanks for spotting that! I keep two copies of the site open in separate browser tabs and mistakenly used the local file version when copying the link. Silly me! Alan G.___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite search syntax
Roger Shaw wrote: Hello, I am very new to python. Wrote a small program to use on my android phone using pickle/shelve to access data. That worked fine but i realised it would be better to use sqlite as a database to more easily modify the data. I havent got a clue about sqlite, have a book but cant find the answer My problem is this. I can access data by putting characters to search for into the program but i want it to be a variable string that i can search for. Specificaly a couple of letters i input from keypad. At the moment this works to search for everything beginning with A sql = SELECT * FROM plants WHERE genus LIKE 'A%'; cursor.execute(sql); You should avoid the above style query if you ever get data from an untrusted source (user/internet) as bad things (obligatory xkcd: http://xkcd.com/327/ ) can happen. Instead, use parameterized queries which will handle escaping the input. sql = SELECT * FROM plants where genus LIKE ? cursor.execute(sql, (genus + '%')) # Add wildcard to parameter, not the base # query. Using this notation, genus can hold one character or any amount. slt =cursor.fetchone(); What i really need is to search for everything beginning with two letters from an input command. As in A is a variable that could be Bl or An or someother two letter combination ~Ramit This email is confidential and subject to important disclaimers and conditions including on offers for the purchase or sale of securities, accuracy and completeness of information, viruses, confidentiality, legal privilege, and legal entity disclaimers, available at http://www.jpmorgan.com/pages/disclosures/email. ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite database locked problem
Date: Tue, 20 Jul 2010 07:28:45 +0200 From: cwi...@compuscan.co.za To: pine...@hotmail.com CC: tutor@python.org Subject: Re: [Tutor] SQLite database locked problem On 20/07/2010 06:48, Che M wrote: I'm using an SQLite3 database (with Python 2.5) and every so often the application crashes or hangs because somewhere there is this error, or something like it: OperationalError: database is locked. This is probably because I am viewing and sometimes changing the database through SQLite Database Browser while working on my app, and occasionally the app tries to access the db when the Database Browser is writing to it or something like that. I'd like to a) know how to reproduce the error (haven't seen it in a while, but want to be sure know when it may happen for users and b) prevent it from being a problem in the running app. Any suggestions welcome. Thank you, Che Hotmail is redefining busy with tools for the New Busy. Get more from your inbox. See how. ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor ? SQLite is technically thread safe, but a write operation locks the entire database [1]: - Any resultset being step()'d through uses a shared read-only lock. - Any insert/update being executed requires an exclusive write lock. Thanks, that's fine to know, but what do I do in Python to address my concern? Che _ The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail. http://www.windowslive.com/campaign/thenewbusy?tile=multicalendarocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite database locked problem
On 20/07/2010 06:48, Che M wrote: I'm using an SQLite3 database (with Python 2.5) and every so often the application crashes or hangs because somewhere there is this error, or something like it: OperationalError: database is locked. This is probably because I am viewing and sometimes changing the database through SQLite Database Browser while working on my app, and occasionally the app tries to access the db when the Database Browser is writing to it or something like that. I'd like to a) know how to reproduce the error (haven't seen it in a while, but want to be sure know when it may happen for users and b) prevent it from being a problem in the running app. Any suggestions welcome. Thank you, Che Hotmail is redefining busy with tools for the New Busy. Get more from your inbox. See how. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2 ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor SQLite is technically thread safe, but a write operation locks the entire database [1]: - Any resultset being step()'d through uses a shared read-only lock. - Any insert/update being executed requires an exclusive write lock. [1] http://www.sqlite.org/lockingv3.html -- Kind Regards, Christian Witts ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite error messages
Benno Lang wrote:
On 10 March 2010 11:37, Alan Harris-Reid aharrisr...@googlemail.com wrote:
Hi there,
I am using the sqlite3 module with Python 3.1, and have some code which goes
something like as follows...
import sqlite3
con = sqlite3.connect('MyDatabase.db')
try:
execresult = con.execute('INSERT INTO MyTable (field_name) VALUES
(MyValue)')
con.commit()
except:
con.rollback()
If con.execute() fails, nothing is returned, and although the code
correctly executes the rollback next, I have no idea why, and therefore
cannot create a suitable error-handler with meaningful messages.
I notice from the SQLite website that there are error codes, but it looks
like the sqlite3 module is not reporting them.
Do you mean numerical error codes? Which page on the SQLite website
are you referring to? Certainly the exception contains usable data.
Try something like this:
try:
execresult = con.execute('INSERT INTO MyTable (field_name) VALUES
(MyValue)')
con.commit()
except Exception as error:
print(Didn't work:, error)
con.rollback()
(I didn't create a table, so I get Didn't work: no such table: MyTable)
HTH,
benno
Hi Benno, your example is great - just what I needed!
Regarding SQLite error codes, the list I was referring to is at
www.sqlite.org/c3ref/c_abort.html
http://www.sqlite.org/c3ref/c_abort.html, but it doesn't look complete
because I have already come-across some IntegrityError messages which
aren't on the list.
Regards,
Alan
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite error messages
Sander Sweers wrote:
- Original message -
I am using the sqlite3 module with Python 3.1, and have some code which
goes something like as follows...
import sqlite3
con = sqlite3.connect('MyDatabase.db')
try:
execresult = con.execute('INSERT INTO MyTable (field_name) VALUES
(MyValue)')
con.commit()
except:
Here you catch all exceptions. Normally you would catch a specific exception like ValueError.
con.rollback()
Do you know finally? It is run after all the exceptions have been handled and
this is where I would put the rollback.
Greets,
Sander
Hello Sander, thanks for the reply.
Normally you would catch a specific exception like ValueError.
Agreed, but as I don't know what type the exception is, I would have to
provide a suitable error message for all exception types (ValueError,
IntegrityError, etc.). At this stage catching Exception as errormessage
is sufficient for my purposes.
Do you know finally? It is run after all the exceptions have been
handled and this is where I would put the rollback.
In this case there is no 'finally' section, because if the 'try' section
doesn't work, then I want the rollback to occur for *all *exceptions.
Maybe I have misunderstood you, but I always thought that the 'finally'
section was run even if the 'try' section is successful, in which case I
would not want a rollback. (According to the Python documentation
(section 8.6) A /finally clause/ is always executed before leaving the
try http://docs.python.org/reference/compound_stmts.html#try
statement, whether an exception has occurred or not.).
Regards,
Alan
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite error messages
On 10 March 2010 21:02, Alan Harris-Reid aharrisr...@googlemail.com wrote:
Maybe I have misunderstood you, but I always thought that the 'finally'
section was run even if the 'try' section is successful, in which case I
would not want a rollback.
I was thinking something like this.
import sqlite3
con = sqlite3.connect('MyDatabase.db')
execresult = None
try:
execresult = con.execute('INSERT INTO MyTable (field_name) VALUES
(MyValue)')
con.commit()
finally:
if not execresult:
print 'Rollback'
con.rollback()
This way you can have a rollback and still see an exception.
Greets
Sander
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite error messages
On 10 March 2010 11:37, Alan Harris-Reid aharrisr...@googlemail.com wrote:
Hi there,
I am using the sqlite3 module with Python 3.1, and have some code which goes
something like as follows...
import sqlite3
con = sqlite3.connect('MyDatabase.db')
try:
execresult = con.execute('INSERT INTO MyTable (field_name) VALUES
(MyValue)')
con.commit()
except:
con.rollback()
If con.execute() fails, nothing is returned, and although the code
correctly executes the rollback next, I have no idea why, and therefore
cannot create a suitable error-handler with meaningful messages.
I notice from the SQLite website that there are error codes, but it looks
like the sqlite3 module is not reporting them.
Do you mean numerical error codes? Which page on the SQLite website
are you referring to? Certainly the exception contains usable data.
Try something like this:
try:
execresult = con.execute('INSERT INTO MyTable (field_name) VALUES
(MyValue)')
con.commit()
except Exception as error:
print(Didn't work:, error)
con.rollback()
(I didn't create a table, so I get Didn't work: no such table: MyTable)
HTH,
benno
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite error messages
- Original message -
I am using the sqlite3 module with Python 3.1, and have some code which
goes something like as follows...
import sqlite3
con = sqlite3.connect('MyDatabase.db')
try:
execresult = con.execute('INSERT INTO MyTable (field_name) VALUES
(MyValue)')
con.commit()
except:
Here you catch all exceptions. Normally you would catch a specific exception
like ValueError.
con.rollback()
Do you know finally? It is run after all the exceptions have been handled and
this is where I would put the rollback.
Greets,
Sander
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite query problem
2010/1/29 BOBÁK Szabolcs szabolcs.bo...@gmail.com: This also works, but this not: sql_command_stat = 'SELECT COUNT(lastmoddate) FROM '+sql_tablename_orig+'WHERE lastmoddate '+str(lastmod_date1) sql_cursor.execute(sql_command_stat) This was my original try, but tried various in various formula. sql_command_stat = 'SELECT COUNT(lastmoddate) FROM '+sql_tablename_orig+'WHERE lastmoddate %d' sql_cursor.execute(sql_command_stat, %lastmod_date1) sql_command_stat = 'SELECT COUNT(lastmoddate) FROM '+sql_tablename_orig+'WHERE lastmoddate (?)' sql_cursor.execute(sql_command_stat, (lastmod_date1)) But always the same error message: sqlite3.OperationalError: near : syntax error File C:\python\stat.py, line 42, in module sql_cursor.execute(sql_command_stat) From the three attempt I concluded that it's the same if I pass the variable value as a string or an integer (maybe I am wrong). Your error isn't where you expect it to be. Let's take a look at the string you're actually passing: sql_tablename_orig = 'pyfilestat_drive_e_2010_01_27_16_48_31' sql_command_stat = 'SELECT COUNT(lastmoddate) FROM '+sql_tablename_orig+'WHERE lastmoddate %d' sql_command_stat 'SELECT COUNT(lastmoddate) FROM pyfilestat_drive_e_2010_01_27_16_48_31WHERE lastmoddate %d' well, hello. we seem to be having a missing space, right in between the table name and 'WHERE.' Also, you should use the third form, using the parameters argument of execute(). it's the only secure one. Hugo ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite query problem
Dear Hugo, Thank you for your fast help and the sharp eyes. I was almost hopeless. It is working now, but only with the first formula, and I also voted to the third one, so I will try a little bit more. Thank you very much! 2010. január 29. 17:53 Hugo Arts írta, hugo.yo...@gmail.com: 2010/1/29 BOBÁK Szabolcs szabolcs.bo...@gmail.com: This also works, but this not: sql_command_stat = 'SELECT COUNT(lastmoddate) FROM '+sql_tablename_orig+'WHERE lastmoddate '+str(lastmod_date1) sql_cursor.execute(sql_command_stat) This was my original try, but tried various in various formula. sql_command_stat = 'SELECT COUNT(lastmoddate) FROM '+sql_tablename_orig+'WHERE lastmoddate %d' sql_cursor.execute(sql_command_stat, %lastmod_date1) sql_command_stat = 'SELECT COUNT(lastmoddate) FROM '+sql_tablename_orig+'WHERE lastmoddate (?)' sql_cursor.execute(sql_command_stat, (lastmod_date1)) But always the same error message: sqlite3.OperationalError: near : syntax error File C:\python\stat.py, line 42, in module sql_cursor.execute(sql_command_stat) From the three attempt I concluded that it's the same if I pass the variable value as a string or an integer (maybe I am wrong). Your error isn't where you expect it to be. Let's take a look at the string you're actually passing: sql_tablename_orig = 'pyfilestat_drive_e_2010_01_27_16_48_31' sql_command_stat = 'SELECT COUNT(lastmoddate) FROM '+sql_tablename_orig+'WHERE lastmoddate %d' sql_command_stat 'SELECT COUNT(lastmoddate) FROM pyfilestat_drive_e_2010_01_27_16_48_31WHERE lastmoddate %d' well, hello. we seem to be having a missing space, right in between the table name and 'WHERE.' Also, you should use the third form, using the parameters argument of execute(). it's the only secure one. Hugo -- Bobák Szabolcs ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite database not update correctly
It's working fine now, but actually I didn't write exactly what you suggested. The commit method belongs to the connection, not to the cursor. Therefore, in my script it should be conn.commit(). Whoops, you're quite right. Went a little too fast there. :D Che _ Windows 7: Unclutter your desktop. http://go.microsoft.com/?linkid=9690331ocid=PID24727::T:WLMTAGL:ON:WL:en-US:WWL_WIN_evergreen:112009___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite database not update correctly
I've got a functions that should update an sqlite database, among other things. However the database doesn't get updated. When used in isolation, the update statement works fine. What am I doing wrong? Below is the function. The whole script can be found at http://pastebin.com/m53978ffa I think it's because you don't commit the changes to the database. Use c.commit() after you make the changes. _ Find the right PC with Windows 7 and Windows Live. http://www.microsoft.com/Windows/pc-scout/laptop-set-criteria.aspx?cbid=wlfilt=200,2400,10,19,1,3,1,7,50,650,2,12,0,1000cat=1,2,3,4,5,6brands=5,6,7,8,9,10,11,12,13,14,15,16addf=4,5,9ocid=PID24727::T:WLMTAGL:ON:WL:en-US:WWL_WIN_evergreen2:112009___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite database not update correctly
Thanks a lot, Che! It's working fine now. On Sun, Nov 8, 2009 at 9:13 PM, Che M pine...@hotmail.com wrote: I've got a functions that should update an sqlite database, among other things. However the database doesn't get updated. When used in isolation, the update statement works fine. What am I doing wrong? Below is the function. The whole script can be found at http://pastebin.com/m53978ffa I think it's because you don't commit the changes to the database. Use c.commit() after you make the changes. -- Find the right PC with Windows 7 and Windows Live. Learn more.http://www.microsoft.com/Windows/pc-scout/laptop-set-criteria.aspx?cbid=wlfilt=200,2400,10,19,1,3,1,7,50,650,2,12,0,1000cat=1,2,3,4,5,6brands=5,6,7,8,9,10,11,12,13,14,15,16addf=4,5,9ocid=PID24727::T:WLMTAGL:ON:WL:en-US:WWL_WIN_evergreen2:112009 ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite database not update correctly
It's working fine now, but actually I didn't write exactly what you suggested. The commit method belongs to the connection, not to the cursor. Therefore, in my script it should be conn.commit(). On Sun, Nov 8, 2009 at 9:15 PM, Che M pine...@hotmail.com wrote: I've got a functions that should update an sqlite database, among other things. However the database doesn't get updated. When used in isolation, the update statement works fine. What am I doing wrong? Below is the function. The whole script can be found at http://pastebin.com/m53978ffa I think it's because you don't commit the changes to the database. Use c.commit() after you make the changes. http://www.microsoft.com/Windows/pc-scout/laptop-set-criteria.aspx?cbid=wlfilt=200,2400,10,19,1,3,1,7,50,650,2,12,0,1000cat=1,2,3,4,5,6brands=5,6,7,8,9,10,11,12,13,14,15,16addf=4,5,9ocid=PID24727::T:WLMTAGL:ON:WL:en-US:WWL_WIN_evergreen2:112009 -- Find the right PC with Windows 7 and Windows Live. Learn more.http://www.microsoft.com/Windows/pc-scout/laptop-set-criteria.aspx?cbid=wlfilt=200,2400,10,19,1,3,1,7,50,650,2,12,0,1000cat=1,2,3,4,5,6brands=5,6,7,8,9,10,11,12,13,14,15,16addf=4,5,9ocid=PID24727::T:WLMTAGL:ON:WL:en-US:WWL_WIN_evergreen2:112009 ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite: don't understand a code snippet
Rich, thanks a lot -- that was the problem. David Rich Lovely wrote: 2009/7/25 David ld...@gmx.net: Dear tutors, I am trying to teach myself the way Python's works with databases. I decided to start with SQLite, and am looking at Summerfield's 'Programming in Python 3'. I got a code snippet that I don't fully understand (the comments are mine): def get_and_set_director(db, director): # try to fetch existing director ID from db director_id = get_director_id(db, director) # if director ID was found in db, return ID if director_id is not None: return director_id cursor = db.cursor() # insert new director record cursor.execute(INSERT INTO directors (name) VALUES (?), (director,)) db.commit() # retrieve and return new director ID from db return get_director_id(db, director) Here is what I think is going on: The function get_and_set_director() gets the director ID from the db by calling the function get_director-id() and returns its value. If the director ID is not in the db then, from outside the get_and_set_director() function, the ID gets inserted to the db via the commit() method. Finally, the director ID is returned (from the db) by once again calling the get_director_id() function. Question: where does a new the director ID come from? Thanks for your directions! David ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor It sounds as if the directors table has an AUTO_INCREMENT column, which will automatically assign itself the next value sequentially when you insert an entry into the table. i.e. The first inserted entry gets an ID of 1, the next 2, and so on. This is stored in a column in the table. The get_director_id(...) function will do something like the following query: cursor.execute(SELECT id FROM directors WHERE name == \%s\, (name,)) I don't know how well I've explained it, but this is the normal technique for generating unique ids for rows in a database. ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite: don't understand a code snippet
2009/7/25 David ld...@gmx.net: Dear tutors, I am trying to teach myself the way Python's works with databases. I decided to start with SQLite, and am looking at Summerfield's 'Programming in Python 3'. I got a code snippet that I don't fully understand (the comments are mine): def get_and_set_director(db, director): # try to fetch existing director ID from db director_id = get_director_id(db, director) # if director ID was found in db, return ID if director_id is not None: return director_id cursor = db.cursor() # insert new director record cursor.execute(INSERT INTO directors (name) VALUES (?), (director,)) db.commit() # retrieve and return new director ID from db return get_director_id(db, director) Here is what I think is going on: The function get_and_set_director() gets the director ID from the db by calling the function get_director-id() and returns its value. If the director ID is not in the db then, from outside the get_and_set_director() function, the ID gets inserted to the db via the commit() method. Finally, the director ID is returned (from the db) by once again calling the get_director_id() function. Question: where does a new the director ID come from? Thanks for your directions! David ___ Tutor maillist - tu...@python.org http://mail.python.org/mailman/listinfo/tutor It sounds as if the directors table has an AUTO_INCREMENT column, which will automatically assign itself the next value sequentially when you insert an entry into the table. i.e. The first inserted entry gets an ID of 1, the next 2, and so on. This is stored in a column in the table. The get_director_id(...) function will do something like the following query: cursor.execute(SELECT id FROM directors WHERE name == \%s\, (name,)) I don't know how well I've explained it, but this is the normal technique for generating unique ids for rows in a database. -- Rich Roadie Rich Lovely There are 10 types of people in the world: those who know binary, those who do not, and those who are off by one. ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite LIKE question
Guys, I got it to work. The problem was to use pysqlite to search (in memory) a large number (10,000) of string items containing the substring q (and to do it continuosly with different q's). The solution was to incase the substring q with % ie. '%q%'. The performance is excellent. The code is in my recent post (Subject: pysqlite and functions) with a new problem ie. the code works as-is but not within a def function. Dinesh .. Date: Fri, 11 Apr 2008 13:20:12 +0100 From: Tim Golden [EMAIL PROTECTED] Subject: Re: [Tutor] SQLite LIKE question Cc: tutor@python.org Message-ID: [EMAIL PROTECTED] Content-Type: text/plain; charset=ISO-8859-1; format=flowed Dinesh B Vadhia wrote: Okay, I've got this now: con = sqlite3.connect(:memory:) cur = con.cursor() cur.execute(CREATE TABLE db.table(col.a integer, col.b text)) con.executemany(INSERT INTO db.table(col.a, col.b) VALUES (?, ?), m) con.commit() for row in con.execute(SELECT col.a, col.b FROM db.table): print row # when run, all rows are printed correctly but as unicode strings q = dog for row in con.execute(SELECT col.b FROM db.table WHERE col.b LIKE ? LIMIT 25, q): print row .. And, I get the following error: Traceback (most recent call last): for row in con.execute(SELECT col.b FROM db.table WHERE col.b LIKE ? LIMIT 25, q): ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied. Whenever you see this in a dbapi context, you can bet your socks that you're passing a single item (such as a string, q) rather than a list or tuple of items. Try passing [q] as the second parameter to that .execute function and see what happens! TJG ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite LIKE question
Dinesh B Vadhia [EMAIL PROTECTED] wrote
I'm using the LIKE operator to match a pattern against a string
using this SELECT statement:
for row in con.execute(
SELECT column
FROM table
WHERE string LIKE '%q%'
limit 25):
With q=dog as a test example, I've tried '$q%', '%q%', '%q'
Ok, Thats the problem. The execute statement works
somewhat like Python string formatting. Yopu don't put variable
names in the string you put markers(which vary by databnase
unfortunately!) In SqlLite they are question marks. You then
follow the SQL statement with a list of values.
Here is an example from my tutorial topic on using databases:
book.execute('''DELETE FROM Address
WHERE First LIKE ?
AND Last LIKE ?''', (first,last) )
Notice the tuple at the end?
HTH,
--
Alan Gauld
Author of the Learn to Program web site
http://www.freenetpages.co.uk/hp/alan.gauld
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite LIKE question
Simone wrote: In Python the symbol '%' in a string is a special char: you use it, for instance, to place a variable inside a string. For completeness, it's worth mentioning in passing that % is only special when you're doing string formatting. It's not otherwise special in strings. However, as Alan said, the method with the question mark to construct the query is more safe than this. Way way way way way safer. In fact, forget that you can even use string formatting to put values into SQL queries. At all. Unless you know precisely what you're doing. And even then don't do it. Really. That way lies madness. And more, larger, and more disastrous SQL database problems than possibly any other error. If your library supports specifying a SQL query string using placeholders (and supplying those values in a tuple which the database module will paste in on its own), it will know to properly quote or escape special characters in those data values. Some modules use ? as the place holder, others use %s (even for numeric values, interestingly enough). Check with your documentation. --steve ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite LIKE question
i forgot to mention that you need to try your sql commands out of your script before trying them inside, ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite LIKE question
I m not sure this is your case but i believe you are missing the cur.fetchall() command which does fetch data from sql db i suggest you put a print statement for every data you use in your program that way you know whats empty whats not... here is example of MySQLdb process: con=MySQLdb.connect(host='x', user='x', passwd='x', db='' ) cur) cur=con.cursor() my_cmd=select %s from where fieldx= '%s' ; % (var1, var2) cur.execute(my_cmd) #now the cmd you are missing : data=cur.fetchall() # then comes the : for i in data: print i hope this helps ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite LIKE question
Dinesh B Vadhia ha scritto: The second problem is that I'm using the LIKE operator to match a pattern against a string but am getting garbage results. For example, looking for the characters q='dog' in each string the SELECT statement is as follows: for row in con.execute(SELECT column FROM table WHERE string LIKE '%q%' limit 25): print row This doesn't work and I've tried other combinations without luck! Any thoughts on the correct syntax for the LIKE? In Python the symbol '%' in a string is a special char: you use it, for instance, to place a variable inside a string. The command you need is something like this: query = SELECT column FROM table WHERE string LIKE '%s' % q for row in con.execute(query): print row where q is your variable and %s tells Python that the q variable has to be converted in a string during the string valorization. If you want that q contains the symbol '%', you have to escape it by inserting 2 '%'. In your example, q have to be 'dog%%' instead of 'dog%'. However, as Alan said, the method with the question mark to construct the query is more safe than this. Hope that helps, Simone Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite LIKE question
Okay, I've got this now: con = sqlite3.connect(:memory:) cur = con.cursor() cur.execute(CREATE TABLE db.table(col.a integer, col.b text)) con.executemany(INSERT INTO db.table(col.a, col.b) VALUES (?, ?), m) con.commit() for row in con.execute(SELECT col.a, col.b FROM db.table): print row # when run, all rows are printed correctly but as unicode strings q = dog for row in con.execute(SELECT col.b FROM db.table WHERE col.b LIKE ? LIMIT 25, q): print row .. And, I get the following error: Traceback (most recent call last): for row in con.execute(SELECT col.b FROM db.table WHERE col.b LIKE ? LIMIT 25, q): ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied. As Python/pysqlite stores the items in the db.table as unicode strings, I've also run the code with q=udog but get the same error. Same with putting the q as a tuple ie. (q) in the Select statement. Btw, there are 73 instances of the substring 'dog' in db.table. Cheers Dinesh ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite LIKE question
Dinesh B Vadhia wrote: Okay, I've got this now: con = sqlite3.connect(:memory:) cur = con.cursor() cur.execute(CREATE TABLE db.table(col.a integer, col.b text)) con.executemany(INSERT INTO db.table(col.a, col.b) VALUES (?, ?), m) con.commit() for row in con.execute(SELECT col.a, col.b FROM db.table): print row # when run, all rows are printed correctly but as unicode strings q = dog for row in con.execute(SELECT col.b FROM db.table WHERE col.b LIKE ? LIMIT 25, q): print row .. And, I get the following error: Traceback (most recent call last): for row in con.execute(SELECT col.b FROM db.table WHERE col.b LIKE ? LIMIT 25, q): ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied. Whenever you see this in a dbapi context, you can bet your socks that you're passing a single item (such as a string, q) rather than a list or tuple of items. Try passing [q] as the second parameter to that .execute function and see what happens! TJG ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite LIKE question
Dinesh B Vadhia [EMAIL PROTECTED] wrote As Python/pysqlite stores the items in the db.table as unicode strings, I've also run the code with q=udog but get the same error. Same with putting the q as a tuple ie. (q) in the Select (q) is not a tuple, it is a variable surrounded by quotes. They are different. To pass a single element as a tuple add a comma: (q,) like so. Try that. Alan G. ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite LIKE question
Dinesh B Vadhia [EMAIL PROTECTED] wrote I'm reading a text file into an in-memory pysqlite table. When I do a SELECT on the table, I get a 'u' in front of each returned row eg. (u'QB VII',) The u is not part of the data its Python telling you that the string is Unicode. The second problem is that I'm using the LIKE operator to match a pattern against a string but am getting garbage results. Can you be more specidic? Can you post the statement and the 'garbage'? for row in con.execute( SELECT column FROM table WHERE string LIKE '%q%' limit 25): Is thios the actual string or have you put the placemarkers (column etc) in just for the post? You do realise that the things inside are intended to be replaced with the actual values from your database. Thus a realistic string would look like SELECT Name FROM Person WHERE Name LIKE '%q% Which would return all Names with q in them. HTH, -- Alan Gauld Author of the Learn to Program web site http://www.freenetpages.co.uk/hp/alan.gauld ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite database creation bafflement
Che M [EMAIL PROTECTED] wrote
don't. For example, this will create a brand new database on the
desktop:
conn = sqlite3.connect('C:\Documents and
Settings\user\Desktop\mydatabase.db')
But running *this*--only thing different is the database's
name--gives the
error, as shown:
conn = sqlite3.connect('C:\Documents and
Settings\user\Desktop\adatabase.db')
Could be that you are hitting the DOS naming issue.
Try making your path names raw strings or use forward slashesinstead
of backslashes. Python/SQLite may not like the \a character...
But I'm guessing.
HTH,
Alan G.
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite database creation bafflement
Che M schreef:
Hi, I am trying to simply create an SQLite database with Python. I find
that when I try to create a new database file, *sometimes* it lets me do it,
and sometimes it doesn't, and the only thing I am changing is the name of
the database. I am baffled as to why some names appear to work and some
don't. For example, this will create a brand new database on the desktop:
import sqlite3
conn = sqlite3.connect('C:\Documents and
Settings\user\Desktop\mydatabase.db')
But running *this*--only thing different is the database's name--gives the
error, as shown:
import sqlite3
conn = sqlite3.connect('C:\Documents and
Settings\user\Desktop\adatabase.db')
Traceback (most recent call last):
File C:/Documents and Settings/user/Desktop/sqlitetester, line 5, in
module
conn = sqlite3.connect('C:\Documents and
Settings\user\Desktop\adatabase.db')
OperationalError: unable to open database file
Backslashes in Python string literals function as escape characters,
meaning that some combinations of backslash + another character are
interpreted specially; for example, \a is an ASCII Bell. See the table
at http://docs.python.org/ref/strings.html for a complete list.
There are different ways to work around the issue:
- Use slashes instead of backslashes, as you would do on Unix. Windows
accepts slashes almost everywhere (a notable exception being the command
line).
- Use double backslashes: \\ in a string literal is actually a single \
- Use raw strings: in raw strings, backslashes are only used to escape
quotes and always remain in the string. You can make a raw string by
prefixing the string with r or R, for example:
conn = sqlite3.connect(r'C:\Documents and Settings\user\Desktop\mydb.db').
A gotcha is that you can't use a backslash as the last character of the
string.
- Use os.path.join(): that function inserts the correct slashes for the
platform you're using, but it's not very readable for literals:
conn = sqlite3.connect(os.path.join('C:', 'Documents and Settings',
'user', 'Desktop', 'mydb.db'))
The only thing that is different is one is called mydatabase.db (works)
and the other is called adatabase.db (doesn't work).
I've tested lots of different names, and it seems random to me what will
work and what won't. E.g., banana.db and apple.db don't work, but
peach.db and pear.db do It is also consistent with each name (that is,
if I am successful and then remove the .db file from the desktop, that name
will always work again to create a new .db file).
It will become clear if you look at the table mentioned above: \b and \a
have a special meaning, while \p doesn't, so \p is interpreted literally.
--
If I have been able to see further, it was only because I stood
on the shoulders of giants. -- Isaac Newton
Roel Schroeven
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] SQLite database creation bafflement
Thank you Alan and Roel for the insight, and Roel thank you for all the suggested ways to get around it. It's always nice when something goes from making no sense to making complete sense in a snap. Che _ Booking a flight? Know when to buy with airfare predictions on MSN Travel. http://travel.msn.com/Articles/aboutfarecast.aspxocid=T001MSN25A07001 ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite: does ? work in PRAGMA commands?
On Wed, 1 Aug 2007, Terry Carroll wrote: Does the ? approach not work with PRAGMA commands or something; or am I doing this wrong? Just a quick follow-up on this, in case anyone else cares. My conclusion is that it's not supported. Googling around I found this pysqlite bug report: http://www.initd.org/tracker/pysqlite/ticket/160 It's not quite on target, since it's trying to use substitution for non-SQL variables in an SQL statement, but I think the principle is the same. The PRAGMA statement is not really an SQL statement, so the variables in it are not SQL variables. So this is not-working as designed, I think. ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite: does ? work in PRAGMA commands?
I'm not sure about PRAGMA, but you can do introspection in sqlite by examining the table 'sqlite_master'. -- John. ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite: does ? work in PRAGMA commands?
On Thu, 2 Aug 2007, John Fouhy wrote: I'm not sure about PRAGMA, but you can do introspection in sqlite by examining the table 'sqlite_master'. Thanks. That's how I get the table names, actually. But it doesn't give the column names. It does give the SQL used to create the table, so I could theoretically parse that out. I suppose I don't actually have a risk in this particular case by using the python-based % substitution, rather than the DB API ? substitution. The table names come directly out of the schema, with no opportunity for a user-driven SQL injection. I'd just like to use good habits from the start. ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite: does ? work in PRAGMA commands?
Terry Carroll wrote: GET_TABLE_INFO_COMMAND = PRAGMA TABLE_INFO(?) pragma_cmd = GET_TABLE_INFO_COMMAND field_data = self.dbconn.execute(pragma_cmd, (tablename)) I get the error: sqlite3.OperationalError: near ?: syntax error Some of the variations included using tablename or (tablename,) for the second parameter; it made no difference. FWIW (tablename,) or [tablename] is the correct spelling, the parameter argument must be a sequence. Kent ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite: does ? work in PRAGMA commands?
On 03/08/07, Terry Carroll [EMAIL PROTECTED] wrote: On Thu, 2 Aug 2007, John Fouhy wrote: I'm not sure about PRAGMA, but you can do introspection in sqlite by examining the table 'sqlite_master'. Thanks. That's how I get the table names, actually. But it doesn't give the column names. It gives you the CREATE statement; you could just parse that :-) -- John. ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
