Zugg Software :: View topic - Python Sqlite COM Component for ZMUD/CMUD

Apprentice Joined: 11 Oct 2007 Posts: 154

Awesome! well I just finished rewriting a ton of stuff for the way i searched for stuff through my script to search the db....

Im really having a ton of fun with this.

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

Basically in SQL an index is like the index of a book it keeps things in a sorted order to make it easy to find.
In a book when you're looking for something under a particular section you can quickly scan the index find what you're looking for and then turn to that page,
rather than having to thumb through the whole book until you find what you want.
Sqlite basically keeps another table with your column data from each row of the column(s) in your index in sorted order with a reference to the rowid that the data is found in.
When you search using an indexed column sqlite can search the indexed table quickly using a binary search and quickly determine what rows match the criteria placed on the index column(s)
It can then do another binary search on the original table using the rowid (which is a unique column sqlite automatically creates for each row in your table)
now it's doing two searches but they're binary searches and it doesn't have to scan the entire unsorted table to get the rows that match your criteria
it doesn't have to be unique,the more distinct the items in the indexed column(s) are the more effective the index is when you search.
Now indexes do cause some slow down for inserts and updates that affect an indexed column since the data has to be updated.
So it's not a good idea to just index every column, but if you search often using a particular column and has enough distinctiveness the speed gains are well worth it.

EDIT:I didn't notice you edited your previous post, so now I'm answering a question you didn't even ask :p

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

Added a standard deviation extension function to Sqlite3 so you can do something like SELECT STDDEV(price) FROM Sales .......
Also if you updated after todays earlier update please do so again.
I made an error wherein if your Execute statement does not return data like in any non SELECT statement or a SELECT that returns nothing.
the LastError message was updated to give a message like 'NoneType is not iterable' everything still worked but still that might confuse some people, my bad.

Posted: Wed Apr 30, 2008 6:16 am

I am getting an "error is 'NoneType' object is not iterable" from LastError(). In fact, there are no errors, I only display this text when the string returned is non-empty.

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

Yeah just get the latest.
As I said in the post above, I had noted and corrected this last sunday.
When I was working out the unicode stuff, I had moved an if check and neglected to put it back in place in the code I released earlier on sunday.

Apprentice Joined: 11 Oct 2007 Posts: 154

I need a little help with what im working with now.

I have a very awesome script for searching through my db using all sorts of parameters.
I have been able to add items to the DB as well.

I am creating a new table that stores the gear I personally own, and what I would like to do is be able to modify record values in this table, or even delete records completely. So I figure every record having a unique identifier will accomplish that nicely.

How would I go about this, is Autonumber an available feature?

Also could somebody please post an example of modifying a record?

Thanks so much :)

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

Apprentice Joined: 11 Oct 2007 Posts: 154

Dharkael, since this table will hold my personal gear i dont see the number of records in the table ever exceeding 400-500.

I did not realize there was already the ROWID that was created, with that I really dont see the need for a second row that does pretty much the same thing....
Could I not just make all of my Select statements for this table in this fashion:

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

Whichever way you go is fine, btw its not a second column, its aliased.
Two names same column, one name created implicitly one explicitly, but always the same column.
But yeah you don't need to create it explicitly if you don't want.
One case where you would want to create and thus name it explicitly is where you're using the relational aspect of databases.
Where a RowID in one table might be linked to another table, it wouldn't do to go around having the unique ID for each table named rowid.
If it's called something like GearID or something then you know you're refering to the unique ID in in the Gear Table or whatever.
But if you plan to use the table without ever relating it to another table then the implicit rowid will do the trick.

EDIT: This Blog Post gives a compelling argument as why its always a good idea to explicit create
the INTEGER PRIMARY KEY AUTOINCREMENT column

Apprentice Joined: 11 Oct 2007 Posts: 154

OH! I understand either way I go I will have the RowID, I should have read your statement closer. I will definately explicitly create it then. I thought the ROWID exists REGARDLESS of weather or not you create INTEGER PRIMARY KEY AUTOINCREMENT (as in the explicitly declared column being an addition to the ROWID), but if you do then RowID becomes an alias to the explicitly declared column

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

Now you have it "five by five"

Apprentice Joined: 11 Oct 2007 Posts: 154

Dharkael Ive emailed you an updated copy of my alleq.db3 file.

I have another issues and figured I would provide you with my db again because then you can see my problem from your end.

When I run the alias insert stuff it complains that there are 30 columns in the table but only 29 values provided but this is intentional because the one i did not proivde is the auto incrementing Primary key integer.

Error after INSERT : table myequip has 30 columns but 29 values were supplied

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

pass NULL as the value to your autoincrement column.

P.S.
You really should avoid using concat directly to pass values, it doesn't automatically escapes things that need to be escaped.
the qmark style will and you can avoid putting in all those single quotes

Apprentice Joined: 11 Oct 2007 Posts: 154

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

Okay I'm gonna have to ask when you last updated the component, because there have already been two posts about the NoneType since Sunday last. If you haven't updated since late late Sunday then please do so.

Apprentice Joined: 11 Oct 2007 Posts: 154

ahhh ok i think missing the last update or so let me give that a go first.

WOOO! you are the man as usual Dharkael, thanks again for the help, and very quick response.

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

P.P.S Even if using concat when passing a number to a field you don't need to quote it

Apprentice Joined: 11 Oct 2007 Posts: 154

Sometimes the value I pass is actually a Null value and by just encapsulating them all in quotes it works...

Apprentice Joined: 11 Oct 2007 Posts: 154

Im having trouble with a couple odd special characters in certain situations... going to try and make a list of all the ways it does it then report back.

Apprentice Joined: 11 Oct 2007 Posts: 154

I know you said that not to use %concat to directly pass values, how about using %format and a bunch of those &s thingys instead of the bunch of ????? is that ok?

Apprentice Joined: 11 Oct 2007 Posts: 154

Ok this one works for me but its giving me a little weirdness when certain characters are present in fields.

Apprentice Joined: 11 Oct 2007 Posts: 154

OK I figured out the problem, these two items when i try to add them:

(K)(G)(H) _-/Troika's Revenge\-_ (200)
(K)(G)(H) Agrizar's Fanon of Strength (200)

it dont work because of the single quote in both of their names.

OK im not sure if this is the right way to go about this, but it seems to be working:

Apprentice Joined: 11 Oct 2007 Posts: 154

W00ts figured out how to make my table with the constraints i wanted.

Apprentice Joined: 11 Oct 2007 Posts: 154

Only Unresolved Problem (I kinda did a lot today, i know, i can ramble some times, hehe)

OK when I query my alleq DB and these items are part of the query results There is a carriage return inserted after the item name.... making the rest of the line that should have continued bumped down a line. I should note that I am using Cmud, so i have cmud output set to 1.

<*><|Sash of Jord|><*>
<*><|Fenris Fangs|><*>
<*><|Modi's Courage|><*>
<*><|Asgardian Greaves|><*>
<*><|Brisings' Necklace|><*>
<*><|Strength of Magni|><*>
\|/ Ogham Bark \|/
\|/ The Yellow Book of Lechan \|/
=|EarthSword>
||A Book of Troll Poetry||
[|Pencil|>
|white toga|
|=|=|Footsteps of Evil|=|=|
/-|Strength Of Perseverance|-\

"blade named ""Stormbringer"""
"black hilted sword called, ""Dwarf Slayer"""
"Holy Avenger ""Faith Restorer"""
"Holy Avenger ""Faith Restorer"""

Heres a picture of what im looking at:

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada