Re: [Tutor] mysqlite
I finished the tutorial and must say i am very happy. Ill probably go over it again while writing some test dbs. The SQL command line still troubles me I'm not running it from Python I'm running it from the OS command line. You need to open a CMD shell window and cd to your SQL database folder (E:\PROJECTS\SQL in the example above) and then run sqlite3. sqlite3 employee.db my main questions are it possible to use the command line without cursor() and connect() in python 3 No only from the sqlite command line. I can only find an sqlite3 folder in my Python installation folder. Python\Lib\sqlite3 the command line command doesnt work from inside this folder, but i am guessing this isnt where im supposed to do it from. Is there a sqlite program i am supposed to install that is separate from the one that came with Python? Its the only reason i can come up with for my cmd not recognizing sqlite3 as a command, operable program or batch file, but i don't know which SQL package to download/install. ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] mysqlite
No only from the sqlite command line. I can only find an sqlite3 folder in my Python installation folder. Python\Lib\sqlite3 That's because you are using the default SQLite that comes with Python which is only the library for connecting from Python. I'll need to clarify that in the tutor. You need to download and install the full Sqlite3 package from the SQLIte web site to get the command line tools. This is well worth doing though because it makes quick 'n dirty queries and data loads much easier than having to write a full Python program to do it. You can create a file of pure SQL and run that directly. Alan G. ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] mysqlite
On 07/03/13 00:35, Lolo Lolo wrote: Because i find following your tutorials very easy i have looked at the other topics you have on your site. i notice they are not available in Python 3 Not yet, I'm still working on them. :-) I have been very busy at work the last 2-3 years so made little progress but last month I took voluntary redundancy and plan on taking a 6 month sabbatical so hopefully I will have time to complete the v3 tutor. but doesnt this matter? or would i need to wait for a python 3 version? You should be able to translate the v2 to v3 fairly easily. Also which could i read next network programming or writing web applications? The web apps bit is very thin, even in v2, it needs a lot of work so I'd say networking or OS are a better bet for now. The networking topic really works best if you read the 3 topics in order: OS, IPC, Networking because they build on each other. If you are familiar with computing concepts like processes, pipes and so on you could reads them separately but otherwise I'd follow through in order. -- 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
[Tutor] mysqlite
im working through alan's sql tutorial. i have a few questions from there but
1st i wanted to try out an example in Core python's sql example.
import sqlite3
cxn = sqlite3.connect('sqlite_test/test')
cur = cxn.cursor()
after this i created a table called users and inserted some data. then i did:
cur.execute('SELECT * from users')
for user in cur.fetchall():
print(user)
i got the results of all data i gave the table. then did
cur.close()
cxn.commit()
cxn.close()
i then closed this interactive IDLE session. i reopened another session and
simply did
import sqlite3
cxn = sqlite3.connect('sqlite_test/test')
cur = cxn.cursor()
for user in cur.fetchall():
print(user)
but this time no data was printed back to me, even though the database file
'test' already existed from the previous session. Why did this happen, cant i
just connect and my data is still intact? or do i have to always re enter data
from the previously saved session?
My questions about http://www.alan-g.me.uk/tutor/tutdbms.htm is to open a
database it uses the command line directly and doesnt creat cursor() or
connect() objects e.g.
E:\PROJECTS\SQL sqlite3 employee.db
sqlite create table Employee
... (EmpID,Name,HireDate,Grade,ManagerID);
sqlite insert into Employee (EmpID, Name, HireDate, Grade, ManagerID)
... values ('1020304','John Brown','20030623','Foreman','1020311');
i tried this in my command line but got :
python.exe: can't open file 'sqlite': [Errno 2] No
such file or directory
i tried creating the a folder called test and doing: test employee.db
but no test.db was created.
my main questions are it possible to use the command line without cursor() and
connect() in python 3 or is it only for python 2. is it better from the command
line or in a .py file. And also my previous question i had about having to re
enter previously stored data
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] mysqlite
On Wed, Mar 6, 2013 at 1:50 PM, Lolo Lolo losermelo...@yahoo.com wrote:
im working through alan's sql tutorial. i have a few questions from there
but 1st i wanted to try out an example in Core python's sql example.
import sqlite3
cxn = sqlite3.connect('sqlite_test/test')
cur = cxn.cursor()
after this i created a table called users and inserted some data. then i
did:
cur.execute('SELECT * from users')
for user in cur.fetchall():
print(user)
i got the results of all data i gave the table. then did
cur.close()
cxn.commit()
cxn.close()
i then closed this interactive IDLE session. i reopened another session
and simply did
import sqlite3
cxn = sqlite3.connect('sqlite_test/test')
cur = cxn.cursor()
for user in cur.fetchall():
print(user)
but this time no data was printed back to me, even though the database
file 'test' already existed from the previous session. Why did this happen,
cant i just connect and my data is still intact? or do i have to always re
enter data from the previously saved session?
Are you sure you are in same directory each time?
My questions about http://www.alan-g.me.uk/tutor/tutdbms.htm is to open a
database it uses the command line directly and doesnt creat cursor() or
connect() objects e.g.
E:\PROJECTS\SQL sqlite3 employee.db
sqlite create table Employee
... (EmpID,Name,HireDate,Grade,ManagerID);
sqlite insert into Employee (EmpID, Name, HireDate, Grade, ManagerID)
... values ('1020304','John Brown','20030623','Foreman','1020311');
i tried this in my command line but got :
python.exe: can't open file 'sqlite': [Errno 2] No
such file or directory
i tried creating the a folder called test and doing: test employee.db
but no test.db was created.
my main questions are it possible to use the command line without cursor()
and connect() in python 3 or is it only for python 2. is it better from the
command line or in a .py file. And also my previous question i had about
having to re enter previously stored data
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
--
Joel Goldstick
http://joelgoldstick.com
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] mysqlite :p:
On 03/07/2013 02:50 AM, Lolo Lolo wrote:
im working through alan's sql tutorial. i have a few questions from there but
1st i wanted to try out an example in Core python's sql example.
import sqlite3
cxn = sqlite3.connect('sqlite_test/test')
cur = cxn.cursor()
after this i created a table called users and inserted some data. then i did:
cur.execute('SELECT * from users')
for user in cur.fetchall():
print(user)
i got the results of all data i gave the table. then did
cur.close()
cxn.commit()
cxn.close()
i then closed this interactive IDLE session. i reopened another session and
simply did
import sqlite3
cxn = sqlite3.connect('sqlite_test/test')
cur = cxn.cursor()
for user in cur.fetchall():
print(user)
I normally use sqlalchemy for manipulating sqlite databases but I think
you need to do a select command or something before trying to do a
fetchall method. It looks like you are doing a fetchall on a cursor
object rather than a query object.
but this time no data was printed back to me, even though the database file
'test' already existed from the previous session. Why did this happen, cant i
just connect and my data is still intact? or do i have to always re enter data
from the previously saved session?
My questions about http://www.alan-g.me.uk/tutor/tutdbms.htm is to open a
database it uses the command line directly and doesnt creat cursor() or
connect() objects e.g.
E:\PROJECTS\SQL sqlite3 employee.db
sqlite create table Employee
... (EmpID,Name,HireDate,Grade,ManagerID);
sqlite insert into Employee (EmpID, Name, HireDate, Grade, ManagerID)
... values ('1020304','John Brown','20030623','Foreman','1020311');
i tried this in my command line but got :
python.exe: can't open file 'sqlite': [Errno 2] No
such file or directory
Are you trying to do this from the command line or from within python?
It looks like python is complaining but if you are using the regular
commandline interface to sqlite3, python shouldn't be invoked at all and
thus never complain. Incidentally (and I haven't gone through Allan's
tutorial on sql) I love the -column and -headers commands when invoking
sqlite from the command line. Put them like this:
sqlite3 -column -header employee.db
Makes the data much easier to read when you do a select command (at
least for me).
i tried creating the a folder called test and doing: test employee.db
but no test.db was created.
my main questions are it possible to use the command line without cursor() and
connect() in python 3 or is it only for python 2. is it better from the command
line or in a .py file. And also my previous question i had about having to re
enter previously stored data
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
.
thomas
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] mysqlite
On 03/06/2013 03:47 PM, Joel Goldstick wrote:
On Wed, Mar 6, 2013 at 1:50 PM, Lolo Lolo losermelo...@yahoo.com wrote:
im working through alan's sql tutorial. i have a few questions from there
but 1st i wanted to try out an example in Core python's sql example.
import sqlite3
cxn = sqlite3.connect('sqlite_test/test')
cur = cxn.cursor()
after this i created a table called users and inserted some data. then i
did:
cur.execute('SELECT * from users')
for user in cur.fetchall():
print(user)
i got the results of all data i gave the table. then did
cur.close()
cxn.commit()
cxn.close()
I've only used sqlite and Python together a couple of times but I don't
remember seeing that sequence of commands used. Don't use cur.close()
and see if that helps.
Regards, Jim
i then closed this interactive IDLE session. i reopened another session
and simply did
import sqlite3
cxn = sqlite3.connect('sqlite_test/test')
cur = cxn.cursor()
for user in cur.fetchall():
print(user)
but this time no data was printed back to me, even though the database
file 'test' already existed from the previous session. Why did this happen,
cant i just connect and my data is still intact? or do i have to always re
enter data from the previously saved session?
Are you sure you are in same directory each time?
My questions about http://www.alan-g.me.uk/tutor/tutdbms.htm is to open a
database it uses the command line directly and doesnt creat cursor() or
connect() objects e.g.
E:\PROJECTS\SQL sqlite3 employee.db
sqlite create table Employee
... (EmpID,Name,HireDate,Grade,ManagerID);
sqlite insert into Employee (EmpID, Name, HireDate, Grade, ManagerID)
... values ('1020304','John Brown','20030623','Foreman','1020311');
i tried this in my command line but got :
python.exe: can't open file 'sqlite': [Errno 2] No
such file or directory
i tried creating the a folder called test and doing: test employee.db
but no test.db was created.
my main questions are it possible to use the command line without cursor()
and connect() in python 3 or is it only for python 2. is it better from the
command line or in a .py file. And also my previous question i had about
having to re enter previously stored data
___
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
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] mysqlite
On 3/6/2013 1:50 PM, Lolo Lolo wrote:
im working through alan's sql tutorial. i have a few questions from there but
1st i wanted to try out an example in Core python's sql example.
import sqlite3
cxn = sqlite3.connect('sqlite_test/test')
cur = cxn.cursor()
after this i created a table called users and inserted some data. then i did:
cur.execute('SELECT * from users')
for user in cur.fetchall():
print(user)
i got the results of all data i gave the table. then did
cur.close()
cxn.commit()
cxn.close()
i then closed this interactive IDLE session. i reopened another session and
simply did
import sqlite3
cxn = sqlite3.connect('sqlite_test/test')
cur = cxn.cursor()
You are missing cur.execute('SELECT * from users')
for user in cur.fetchall():
print(user)
--
Bob Gailer
919-636-4239
Chapel Hill NC
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] mysqlite
You are missing cur.execute('SELECT * from users')
thanks:) this has fixed the 1st issue.
guys i still cant use the command line. but i guess i dont really mind as i
can just make a .py file...
i am having a lot of fun reading through alan's tutorial, everything is
becoming very clear but can someone please explain this to me.
update Employee
set ManagerID = (Select EmpID
from Employee
where Name = 'John Brown')
where Name = 'Fred Smith' OR
Name = 'Anne Jones';
this is saying set the managerID of 'Fred Smith' or 'Jane Brown' to the ID
returned in the nested select statement?
and this:
drop table author;
create table author (
ID Integer PRIMARY KEY,
Name String NOT NULL
);
drop table book;
create table book (
ID Integer PRIMARY KEY,
Title String NOT NULL
);
drop table book_author;
create table book_author (
bookID Integer NOT NULL,
authorID Integer NOT NULL
);
sqlite SELECT title from book, book_author ... where book_author.bookID =
book.ID ... and book_author.authorID = (select ID from author ...
where name = Jane Austin);
maybe its simple but because its more complex im finding it hard to read. we
want a title from book,
which is the field that will be returned, but why is book_author also in the
select statement when it isnt
being returned? or are we actually returning every single field from book
author?
so if the author.bookID is equal to book.ID, also check that this ID from
author matches the authorID from
book_author.authorID ? after comprehending this im lost in my thoughts. can
someone tell me how these are
linked. so the whole output is really dependent on the answer from the 2nd
condition after the and?:
... and book_author.authorID = (select ID from author
where name = Jane Austin);
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] mysqlite
On 06/03/13 18:50, Lolo Lolo wrote:
i then closed this interactive IDLE session. i reopened another session and
simply did
import sqlite3
cxn = sqlite3.connect('sqlite_test/test')
This gets you a connection to the data base
cur = cxn.cursor()
and this sets up a storage area for any results
for user in cur.fetchall():
print(user)
But at this point the cursor is empty, you haven't read anything from
the database. You need to execute some kind of SELECT statement to
populate the cursor from the database.
My questions about http://www.alan-g.me.uk/tutor/tutdbms.htm is to
open a database it uses the command line directly
and doesnt creat cursor() or connect() objects e.g.
Thats becsause the connection and cursor mechanism is how Python
interacts with SQLite. When you use the SQLite prompt you don't need
them SQLite connects itself automatically.
AS you read further in the tutorial page you'll see the section where we
jump from direct SQL command line into using Python and there you will
see connect/cursor.
E:\PROJECTS\SQL sqlite3 employee.db
sqlite create table Employee
... (EmpID,Name,HireDate,Grade,ManagerID);
sqlite insert into Employee (EmpID, Name, HireDate, Grade, ManagerID)
... values ('1020304','John Brown','20030623','Foreman','1020311');
i tried this in my command line but got :
python.exe: can't open file 'sqlite': [Errno 2] No
such file or directory
I'm not running it from Python I'm running it from the OS command line.
You need to open a CMD shell window and cd to your SQL database folder
(E:\PROJECTS\SQL in the example above) and then run sqlite3.
i tried creating the a folder called test and doing: test employee.db
but no test.db was created.
You need to change into the test directory. Then run
sqlite3 employee.db
and when you are done a new employee.db file should exist.
my main questions are it possible to use the command line
without cursor() and connect() in python 3
No only from the sqlite command line.
The great thing about command lines is there are so many
to choose from:
The OS
SQLite
Python
and then from within Python you can use:
the debugger
the help system
and, if you are really perverse, you can even use Tcl from within Tkinter!!
such choice! ...such confusion...
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] mysqlite
On 06/03/13 23:25, Lolo Lolo wrote: can someone please explain this to me. update Employee set ManagerID = (Select EmpID from Employee where Name = 'John Brown') where Name = 'Fred Smith' OR Name = 'Anne Jones'; this is saying set the managerID of 'Fred Smith' or 'Jane Brown' to the ID returned in the nested select statement? Almost. Its saying set the Manager of both Fred and Anne to John. The OR selects both entries and then applies the update to them. We can logically split it like this (so you can test it at the command prompt :-): SELECT * FROM Employee where Name = 'Fred Smith' OR Name = 'Anne Jones'; That will get you two rows. Select EmpID from Employee where Name = 'John Brown' That will get you one ID, lets call it JohnsID and putting them together: update Employee set ManagerID = JohnsID where Name = 'Fred Smith' OR Name = 'Anne Jones'; Hopefully that clarifies it a bit. and this: drop table author; create table author ( drop table book; create table book ( drop table book_author; create table book_author ( sqlite SELECT title from book, book_author ... where book_author.bookID = book.ID ... and book_author.authorID = (select ID from author ... where name = Jane Austin); maybe its simple but because its more complex im finding it hard to read. Yes, its where SQL starts to get more difficult when you start joining tables together. We are basically extracting data from the author table (Jane Austins ID) to use as a filter in the book_author table to get the IDs of all the books in our database written by Jane Austin. We then use this list of IDs to filter the Books table to get the names of those books. The whole thing could be simplified ifg we didn't use IDs but just used the names directly. But that leads to other problems (discussed in the tutorial) when names change etc (not so likely with books but very common with real people!). So using IDs, even though it adds complexity, is usually a worthwhile habit to get into. ... why is book_author also in the select statement when it isn't being returned? Because we are using it in the query (the where clause) we need to include it in the list of tables. You can think of the SELECT statement as a mini program and the tables list as a list of all the objects used in the program. Its not just for the values being returned. or are we actually returning every single field from book author? No, only the set of IDs corresponding to Jane Austin's ID. The thing to remember about book_author is that it only contains IDs it has no real data of its own. It's purely there to facilitate a multi way join between books and authors. The good news is that once you wrap your brain around this construct most SQL queries you will come across will be comprehensible to you. HTH, if not, just keep firing in the questions. -- 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] mysqlite
I'm not running it from Python I'm running it from the OS command line. You need to open a CMD shell window and cd to your SQL database folder (E:\PROJECTS\SQL in the example above) and then run sqlite3. thanks ill give this a try. We can logically split it like this and putting them together: update Employee set ManagerID = JohnsID where Name = 'Fred Smith' OR Name = 'Anne Jones'; Hopefully that clarifies it a bit. ok this is very clear now. i totally understand it im starting to see the last one and how the filtering is working. i will go over it a few more times to 100% get it. I'll ask more questions if i get stuck along the way:) Because i find following your tutorials very easy i have looked at the other topics you have on your site. i notice they are not available in Python 3 but doesnt this matter? or would i need to wait for a python 3 version? Also which could i read next network programming or writing web applications? or out of eevrything under Applications(excluding databases), which would be the easiest subject for a new programmer to understand ? ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
