sqlite newbie questions

[kf9150]

Hello,

I have a sqlite database table which has a table named "Transmittal".
Before inserting a new record into the database, i'd like to perform
some checking. How do i select all records with certain value (say
"Smith") for a column (say "Last_Name")? Knowing almost nothing about
SQL, i just selected everything and process the returned list.

cur.execute('select * from Transmittal')
records = cur.fetchall()

Also, how do i delete records with certain value for centain column. I
was thining of something like
cur.execute('delete * from Transmittal where Last_Name=?', "Smith")
but the syntax it's not correct.

Thanks for your help.

kelie

 

[Carsten Haese]

Hello,

I have a sqlite database table which has a table named "Transmittal".
Before inserting a new record into the database, i'd like to perform
some checking. How do i select all records with certain value (say
"Smith") for a column (say "Last_Name")? Knowing almost nothing about
SQL, i just selected everything and process the returned list.

cur.execute('select * from Transmittal')
records = cur.fetchall()

Use a where clause not unlike the one you're using in the delete
statement below.

Also, how do i delete records with certain value for centain column. I
was thining of something like
cur.execute('delete * from Transmittal where Last_Name=?', "Smith")
but the syntax it's not correct.

You're close. Drop the '*' and put your parameter into a singleton tuple
or one-element list:

cur.execute('delete from Transmittal where Last_Name=?', ("Smith",) )

HTH,

 

[kf9150]

Thank you Carsten. Problem solved.

 

[=?iso-8859-1?q?Luis_M=2E_Gonz=E1lez?=]

Hello,

I have a sqlite database table which has a table named "Transmittal".
Before inserting a new record into the database, i'd like to perform
some checking. How do i select all records with certain value (say
"Smith") for a column (say "Last_Name")? Knowing almost nothing about
SQL, i just selected everything and process the returned list.

cur.execute('select * from Transmittal')
records = cur.fetchall()

Also, how do i delete records with certain value for centain column. I
was thining of something like
cur.execute('delete * from Transmittal where Last_Name=?', "Smith")
but the syntax it's not correct.

Thanks for your help.

kelie

You need to learn sql if you want to deal with databases.
Don't worry, it's very easy, and here is a very good resource to get
you up and running in a few minutes:
http://www.sqlcourse.com

Good luck!
Luis

 

[kf9150]

You need to learn sql if you want to deal with databases.
Don't worry, it's very easy, and here is a very good resource to get
you up and running in a few minutes:http://www.sqlcourse.com

Good luck!
Luis

Thanks Luis, will check out the site you recommended.

 

[Yves Pouplard]

Hello,

I have a sqlite database table which has a table named "Transmittal".
Before inserting a new record into the database, i'd like to perform
some checking. How do i select all records with certain value (say
"Smith") for a column (say "Last_Name")? Knowing almost nothing about
SQL, i just selected everything and process the returned list.

cur.execute('select * from Transmittal')
records = cur.fetchall()

Also, how do i delete records with certain value for centain column. I
was thining of something like
cur.execute('delete * from Transmittal where Last_Name=?', "Smith")
but the syntax it's not correct.

Can you try this:

cur.execute("delete from Transmittal where Last_Name='?' ", "Smith")

which is a better SQL syntax

PS: I don't know sqlite, but the syntax above is the right one for
Oracle, MySQL, PostgreSQL, ...

 

[Dennis Lee Bieber]

cur.execute("delete from Transmittal where Last_Name='?' ", "Smith")

which is a better SQL syntax

PS: I don't know sqlite, but the syntax above is the right one for
Oracle, MySQL, PostgreSQL, ...

To my limited experience, that syntax is wrong for both MySQL and
SQLite.

Close, though, for SQLite.

cur.execute("delete from Transmittal where Last_Name=? ", "Smith")

is the SQLite version. MySQL uses %s rather than ?

In both cases, the whole idea behind letting the adapter do
parameter substitution is that the adapter will add the appropriate
delimiters (quote marks, for the most part) needed for the data type.
Quote marks around string parameters, for instance. I think I've seen
some (M$ products, most likely) that put # around date/time objects.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Carsten Haese]

In both cases, the whole idea behind letting the adapter do
parameter substitution is that the adapter will add the appropriate
delimiters (quote marks, for the most part) needed for the data type.

That's only the case if the adapter is dumb enough to stuff the parameter into
the query as a literal value. Most adapters aren't that dumb.